Using a Script to Kill Processes in SQL

Using a Script to Kill Processes in SQL

Introduction

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.

1	‘ VBScript source code
2    Dim srv1 ‘As New SQLDMO.SQLServer
3    Dim usr1 ‘As SQLDMO.User
4    Dim qres ‘As SQLDMO.QueryResults
5    Dim rs ‘As New ADODB.Recordset
6	Dim sQueryResult ‘As String
7	Dim rsQryResults ‘As New ADODB.Recordset
8	Dim sRows() ‘As String
9	Dim sCols() ‘As String
10	Dim idxRow ‘As Long
11	Dim idxCol ‘As Long
12	Dim wshShell ‘ As wscript Object
13	Dim strSQLServer ‘As String
14	Dim strDBKill ‘As String
15
16
17
18	set wshShell = createObject(“wscript.shell”)
19	set srv1 = CreateObject(“SQLDMO.SQLServer”)
20	set usr1 = CreateObject(“SQLDMO.User”)
21	set rs = CreateObject(“ADODB.RecordSet”)
22	Set rsQryResults = CreateObject(“ADODB.RecordSet”)
23
24	WshShell.LogEvent 0, “Kill Process Script Started”
25
26	strSQLServer = “ENTER SERVER NAME HERE”
27	strDBKill = “ENTER DATABASE NAME HERE”
28
29  ‘Instantiate SQLServer object and
30    ‘point it at the server.
31    srv1.LoginSecure = True
32    srv1.Connect strSQLServer
33
34    Set oQueryResults = srv1.EnumProcesses
35
36	For idxCol = 1 To oQueryResults.Columns
37		rsQryResults.Fields.Append oQueryResults.ColumnName(idxCol), 200,
                oQueryResults.ColumnMaxLength(idxCol) + 2
38	Next ‘idxCol next column
39
40	”Create the recordset rows
41	rsQryResults.Open
42	For idxRow = 1 To oQueryResults.Rows
43		‘Add a new record
44		rsQryResults.AddNew
45		‘Add values to each field in the row
46		For idxCol = 1 To oQueryResults.Columns
47			rsQryResults.Fields(idxCol – 1) = oQueryResults.GetColumnString(idxRow, idxCol)
48		Next
49		rsQryResults.Update
50	Next ‘idxRow
51	‘ return to caller
52
53
54	Set rs = rsQryResults
55
56    rs.MoveFirst
57  Do While Not rs.EOF
58  ‘      For Each fd In rs.Fields
59  ‘          strfieldlist = strfieldlist & fd.Name & ” ” & fd.Value & vbCrLf
60  ‘      Next
61        If UCase(rs(“dbname”).Value) = Ucase(strDBKill) Then
62           	WshShell.LogEvent 0, “The following process was killed programatically ” &
                rs(“Spid”).Value & ” ” & rs(“dbname”).Value & ” ” & rs(“status”).Value
63            srv1.KillProcess (CInt(rs(“Spid”).Value))
64        End If
65        rs.MoveNext
66    Loop
67            	WshShell.LogEvent 0, “Kill Process Script Complete”
68
69    Set oQueryResults = nothing
70    Set rs = nothing

Conclusions

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.

»


See All Articles by Columnist Bruce Szabo