| Downloads |
|---|
| 16387.zip |
Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your contributions (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.
Killing SQL Server Processes
Inactive processes can quickly eat up connections to your SQL Server databases, dramatically limiting the number of connections available for other work. You can dynamically terminate a particular process by executing a kill statement, such as
kill n
where n is the server process ID (SPID) number of the process you want to end. However, because kill is a system function and not a stored procedure, it doesn't accept a parameter. So, you can't create a procedure that identifies an inactive process based on certain criteria (such as connection time or whether the process is blocking another process) and passes the SPID number of that process as a kill statement parameter, such as
kill @spid
The code example in Listing 1, however, shows how you can dynamically build a kill statement that terminates an inactive process based on the process's SPID. The code first declares two variables: @command, which holds the T-SQL string that you use to create the kill statement, and @spid, which holds the SPID for the process you want to terminate. The first SELECT statement initializes @spid, and the second SELECT statement builds the T-SQL string that contains the kill statement—complete with the SPID of the process you want to end. The code then executes the kill statement in the string.
Find and Insert Missing Records—Revisited
Will Haney's script to update a production table with records in an update table ("Find and Insert Missing Records," July 2000) was very interesting. Our company also needs to find and insert records that are in our update tables but not in our production tables. However, we use views instead of temporary tables to find the missing records and append those records to the production tables. Listing 2 shows the code we use. This script creates the production and update tables, then populates them with the data that we want to compare. Next, the script creates the production_append view, which compares the two tables' data and finds records that are missing from the production table. The script then populates the production table with rows that were in the update table but not in the production table.
