| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2011-07-29 : 10:01:44
|
| I have a web application. When executed, the sql code writes records to a table. The INSERT statement is within a loop, but not an infinite one, thank goodness. Unless I can stop it, it will continue to fill up the table for hours on end. How can I kill this process? I tried shutting down sql server agent. But it automatically restarts on its own after a few seconds. When I check my table it's still growing, so I know it's still running. I also tried shutting down IIS.Your help is greatly appreciated. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-29 : 10:10:54
|
If it is a one-time thing (and I hope it is!), you can run sp_who2 in SSMS, identify the row to get the spid and issue the command kill spid (where spid is a number).You may also want to install sp_whoisactive (http://www.sqlmag.com/article/sql-server/sp_whoisactive) which is one of the highly recommended free tools that helps you find all kinds of things about what is running on a server. |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-07-29 : 10:18:41
|
| Okay, I'm new to this.So running 'exec sp_who2' will return a list from which I must choose the correct spid?I then execute 'kill <spid>'. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-29 : 10:26:26
|
| Yes. When you run sp_who2, it gives you a few pieces of information which (hopefully) will help you identify the spid of the offending process. Once you identify the correct spid, then you can kill it. Unless you can identify the spid correctly, don't kill something based on suspicion. |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-07-29 : 10:50:31
|
| I'm fairly confident of which one (or two) is the culprit. In looking at the output..1) the 'Hostname' field is the IIS server name.2) the 'Login' is the login specified in my connection string within the source code.3) the 'ProgramName' field is Internet Information Services.There's also another one with these same fields except the ProgramName is .Net SqlClient Data Provider.I'm torn between the two.Any thoughts? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-29 : 11:30:44
|
| DBCC INPUTBUFFER(@spid) will show you the statement being executed.Also, be aware if you kill it, that the rollback could take an awful long time too. |
|
|
|
3magic
Starting Member
14 Posts |
Posted - 2011-08-03 : 08:12:16
|
| Use while loop for this. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-03 : 08:19:55
|
| you might be better off converting this to set based insert------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
muratos
Starting Member
22 Posts |
Posted - 2011-08-04 : 04:43:16
|
| You can also use Activity Monitor and trace what is going on your SQL Server real time (at the refresh intervals you set). It supports getting all query details and killing it by menu. It is a GUI and suits for beginners. |
|
|
|
|
How to kill INSERT statement?