Menu
I'm trying to use a KILL statement with a declared variable but it's giving me a syntax error. Is there anyway to not use a constant and programatically change the SPID?
For example:
DECLARE @SPID smallint
SET @SPID = 100
Kill @SPID
BTW this is just an example. I need to run the kill in a loop with a cursor to get rid of old persistant user connections. (Don't ask)
789
Terminate running database backup using a KILL SPID command While the backup is in progress, execute the sp_who2 command to see the backup database process and note down the SPID for it. Let's assume we want to cancel the backup. Execute the KILL SPID command, and it immediately kills the backup process.
After you have connected, right click on the instance name and select 'Activity Monitor' from the menu. Once Activity Monitor has loaded, expand the 'Processes' section. Scroll down to the SPID of the process you would like to kill. Right click on that line and select 'Kill Process'.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
Variables declared before the GO statement are not accessible after the GO statement. Basically SSMS sends the first batch (i.e. Batch 1) of statements to the SQL Engine first, once its execution is over it sends the second batch of statements (i.e. Batch 2) after the GO statement to the SQL Engine for execution.
I think you're going to need dynamic SQL for this. Read this essential page before doing anything with dynamic SQL, please.
DECLARE @SPID smallint
DECLARE @SQL nvarchar(1000)
SET @SPID = 100
SET @SQL = 'KILL ' + CAST(@SPID as varchar(4))
EXEC (@SQL)
You could always do this via SMO as well, using powershell or C# :
http://msdn.microsoft.com/en-gb/library/microsoft.sqlserver.management.smo.server.killprocess(v=sql.110).aspx
This kind of management of sql server and the code that goes with it is just what powershell and SMO are good at, and T-SQL can sometimes be fiddly with.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
