Kill Processes in a Database

,

This script kills all the processes using a given database.  It is useful for removing users from a database so it can be restored.

To use this script change the server variable and the database variable at the top of the script.  Notifications are written to the windows application eventlog.

Use this script at your own risk. It is always best to make sure you have an understanding of a script before implementing it.

' VBScript source code
    Dim srv1 'As New SQLDMO.SQLServer
    Dim usr1 'As SQLDMO.User
    Dim qres 'As SQLDMO.QueryResults
    Dim rs 'As New ADODB.Recordset
	Dim sQueryResult 'As String
	Dim rsQryResults 'As New ADODB.Recordset
	Dim sRows() 'As String
	Dim sCols() 'As String
	Dim idxRow 'As Long
	Dim idxCol 'As Long
	Dim wshShell ' As wscript Object
	Dim strSQLServer 'As String
	Dim strDBKill 'As String


	
	set wshShell = createObject("wscript.shell")
	set srv1 = CreateObject("SQLDMO.SQLServer")
	set usr1 = CreateObject("SQLDMO.User")
	set rs = CreateObject("ADODB.RecordSet")
	Set rsQryResults = CreateObject("ADODB.RecordSet")

	WshShell.LogEvent 0, "Kill Process Script Started" 

	strSQLServer = "ENTER SERVER NAME HERE"
	strDBKill = "ENTER DATABASE NAME HERE"

    'Instantiate SQLServer object and
    'point it at the server.
    srv1.LoginSecure = True 
    srv1.Connect strSQLServer
        
    Set oQueryResults = srv1.EnumProcesses

	For idxCol = 1 To oQueryResults.Columns
		rsQryResults.Fields.Append oQueryResults.ColumnName(idxCol), 200, oQueryResults.ColumnMaxLength(idxCol) + 2
	Next 'idxCol next column
	    
	''Create the recordset rows
	rsQryResults.Open
	For idxRow = 1 To oQueryResults.Rows
		'Add a new record
		rsQryResults.AddNew
		'Add values to each field in the row
		For idxCol = 1 To oQueryResults.Columns
			rsQryResults.Fields(idxCol - 1) = oQueryResults.GetColumnString(idxRow, idxCol)
		Next
		rsQryResults.Update
	Next 'idxRow
	' return to caller


	Set rs = rsQryResults
    
    rs.MoveFirst
   Do While Not rs.EOF
        For Each fd In rs.Fields
            strfieldlist = strfieldlist & fd.Name & " " & fd.Value & vbCrLf
        Next
        If UCase(rs("dbname").Value) = Ucase(strDBKill) Then
           	WshShell.LogEvent 0, "The following process was killed programatically " & rs("Spid").Value & " " & rs("dbname").Value & " " & rs("status").Value
            srv1.KillProcess (CInt(rs("Spid").Value))
        End If
        rs.MoveNext
    Loop
            	WshShell.LogEvent 0, "Kill Process Script Complete" 
   
    Set oQueryResults = nothing
    Set rs = nothing
    
    if err.number <> 0 then
	WshShell.LogEvent 1,"Error " & err.number & " " & err.description
	err.clear
    end if

Rate

Share

Share

Rate