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.