Using a Script to Kill Processes in SQL
Backing up and restoring databases is part of every administrator's job. While performing backups, a number of methods can be used to save time. If a database needs to be moved from one server to another, a backup on one server and a restore on another server is a great method. Some projects require a production database to be moved from one server to a another server under a different name. In each of these processes, a restore cannot be completed unless all the users are out of the target database.
To automate a restore process it is necessary to be able to programmatically kill processes on a a SQL server in order to perform the restore. The script presented in this article does this. By being provided a server name and a database name, the script will kill the processes using the database.
Using the Script
The script should be able to be used as it is listed. The only changes required are to change lines 26 and 27 to the names of the server and database for a given environment. The script also assumes security is trusted, meaning the user needs to be a local administrator on the computer where the script will be exectuted.
How the Script Works
Lines 1-14 of the script enumerate the variables that may be used in the script. To develop the script some of the examples from SQL Code Samples were used. Variable names may be familiar if one has used those samples. Lines 18-22 instantiate the objects that are needed. Line 24 writes an event to the Windows Application Event log.
Lines 26 and 27 set the variables for the environment, as mentioned in the section above. These variables control what processes will be killed. Line 31 and 32 are the steps that allow one to connect to the SQL server. On line 34 the processes on the server are enumerated and written to a collection variable oQueryResults. Lines 36-38 take the collection variable oQueryResults and setup a recordset (rsQryResults). The recordset allows for easier manipulation of the data. Lines 41 to 50 populate the recordset from the collection data.
Line 54 instantiates another recordset, rs, for clarity and nothing more. It is possible to use the rsQryResults recordset, but it did not seem as clear. Lines 57 through 66 do the bulk of the work in the script. Each Process has a record in the recordset. Each of these records is tested against the desired database (line 61) and killed if it matches the target database.
The process is actually killed on line 63, and line 62 writes an event to the Windows 2000 Application event log. Line 67 writes another event to the Windows 2000 Application log letting one know the script is complete, and the last two lines set the recordsets to nothing.
The ability to kill processes in a database becomes necessary when scripting a number of database procedures. It is extremely useful when one wants to programmatically restore a database. This script could also be modified to return who is using a database and the current state of the process.