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

Latest Articles