Set oAPI = MOMCreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()
If CheckBlockedSPIDS(oBag, sConnectionString, iTime,sInstanceName, sComputerName) = 0 Then
Call oAPI.Return(oBag)
Else
Quit()
End If
End Sub
'End of Main
'-----------------------------------------------------------------------------------------------
Function Quit()
WScript.Quit()
End Function
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Err.number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Function MomCreateObject(ByVal sProgramId)
Dim oError
Set oError = New Error
On Error Resume Next
Set MomCreateObject = CreateObject(sProgramId)
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError, ERROR_CREATE_OBJECT_ID
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr,ByVal iErrId )
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent "GetSQL2008BlockingSPIDs.vbs", iErrId, ERROR_EVENT, sMessage & ". " & oErr.Description
Quit()
End Function
Function CheckBlockedSPIDS(ByRef oBag, ByVal sConnectionString, ByVal iWaitInMinutes, ByRef sInstanceName, ByRef sComputerName)
dim cnADOConnection
dim rsBlockedSPIDS
dim strProv
dim sName
dim bFail
dim SCRIPT_SQL
dim SPIDList
dim SPIDCount
SCRIPT_SQL = "SELECT " & _
"dbo.sysprocesses.spid, " & _
"dbo.sysprocesses.blocked, " & _
"dbo.sysprocesses.waittime, " & _
"dbo.sysprocesses.waitresource, " & _
"dbo.sysprocesses.program_name, " & _
"dbo.sysprocesses.loginame, " & _
"dbo.sysdatabases.name " & _
"FROM dbo.sysprocesses WITH (nolock) " & _
"INNER JOIN dbo.sysdatabases " & _
"ON dbo.sysprocesses.dbid = dbo.sysdatabases.dbid " & _
"WHERE (dbo.sysprocesses.blocked <> 0) " & _
"AND (dbo.sysprocesses.waittime > " & CStr(iWaitInMinutes*60) & " * 1000) " & _
"ORDER BY dbo.sysprocesses.waittime DESC"
On Error resume next
Set cnADOConnection = MomCreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 30
strProv = "Server=" & sConnectionString & ";Database=master;Trusted_Connection=yes"
Err.Clear
cnADOConnection.Open strProv
if 0 <> Err.number then
On Error GoTo 0
ThrowScriptError "Cannot login to database [" & sComputerName & "][" & sInstanceName & ":master] ", Err, ERROR_LOGIN_FAILURE_ID
Exit Function
end if
Err.Clear
Set rsBlockedSPIDS = cnADOConnection.Execute(SCRIPT_SQL)
if 0 <> Err.number then
Exit Function
end if
SPIDList = ""
SPIDCount = 0
do
bFail = True
Err.Clear
if rsBlockedSPIDS.EOF then
if 0 <> Err.number then Exit Do
bFail = False
Exit Do
end if
if 0 <> Err.number then Exit Do
SPIDCount = SPIDCount + 1
if SPIDList = "" Then
SPIDList = CStr(rsBlockedSPIDS("Spid").Value)
else
SPIDList = SPIDList & "," & CStr(rsBlockedSPIDS("Spid").Value)
end if
' sAlertDescription = "The program """ & Trim(rsBlockedSPIDS("Program_name").Value) & _
' """ has been blocked for " & CStr((CLng(rsBlockedSPIDS("waittime").Value) / 1000) / 60) & _
' " minutes on database " & rsBlockedSPIDS("name").Value & _
' " in the SQL instance " & sInstance & _
' ". The defined acceptable blocking threshold is " & CStr(iWaitInMinutes) & _
' " minute(s). """ & Trim(rsBlockedSPIDS("Program_name").Value) & _
' """ is running on SPID " & CStr(rsBlockedSPIDS("Spid").Value) & _
' " as login " & Trim(rsBlockedSPIDS("Loginame").Value) & _
' " and is blocked by SPID " & CStr(rsBlockedSPIDS("Blocked").Value) & _
' ". The resource id is " & rsBlockedSPIDS("Waitresource").Value & "."
Err.Clear
if 0 <> Err.number then Exit Do
Err.Clear
rsBlockedSPIDS.MoveNext
if 0 <> Err.number then Exit Do
Loop
Call oBag.AddValue("BlockedSPIDList",CStr(SPIDList))
Call oBag.AddValue("BlockedSPIDCount",CInt(SPIDCount))
if bFail then
Exit Function
end if
Set cnADOConnection = Nothing
Set rsBlockedSPIDS = Nothing