Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 31, 2002

Using a Script to Kill Processes in SQL

By DatabaseJournal.com Staff

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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM