ihavebeenseen
Posts: 1
Joined: 8.Jun.2004
Status: offline
|
Here is some code and basic instructions on how to create a monitor task that watches to see if a SQL server has any blocked spids.
1. create a VBS files with the code provided below.
2. create the stored proc in your master database with the code provided below.
3. create a VBS monitor
function name: CheckSQLBlk
parameter 1: "your_servername"
Hope this helps
VBS CODE:
VBS CODE:
VBS CODE:
VBS CODE:
VBS CODE:
Const retvalUnknown = 1
Function CheckSQLBlk( sqlServerval )
On Error Resume next
Dim objConn
Dim objRst
Dim SQLStmt
Dim iRetVal
Set objConn = CreateObject("ADODB.Connection")
ObjConn.Open "driver={SQL Server};server=" & sqlServerval & ";database=master"
Set objRst = CreateObject("ADODB.Recordset")
' By the way, you should use Server.CreateObject() if
' you're using ASP, or WScript.CreateObject() if you're
' using WSH here.
SQLStmt = "DECLARE @retval int " & vbCrLf
SQLStmt = SQLStmt & "EXEC @retval = master..esi_check_block " & vbCrLf
SQLStmt = SQLStmt & "SELECT 'Return Value' = @retval"
Set objRST = ObjConn.Execute(SQLStmt)
If (Not objRst.EOF and Not objRst.BOF) Then
iRetVal = objRst("Return Value")
End If
if (iRetVal <> 0) then
EXPLANATION = "spids are currently blocked"
CheckSQLBlk = False
Else
EXPLANATION = "no blocked spids"
CheckSQLBlk = True
end if
objRst.Close
ObjConn.Close
Set objRst = Nothing
set ObjConn = Nothing
End Function
SQL CODE:
SQL CODE:
SQL CODE:
SQL CODE:
SQL CODE:
create proc esi_check_block
AS
create table #spid(
SPID varchar(100)
,Status varchar(100)
,Login varchar(100)
,HostName varchar(100)
,BlkBy varchar(100)
,DBName varchar(100)
,Command varchar(100)
,CPUTime varchar(100)
,DiskIO varchar(100)
,LastBatch varchar(100)
,ProgramName varchar(100)
,SPID2 varchar(100) )
insert into #spid exec sp_who2
declare @blkcnt int
set @blkcnt = (select count(1) as blkcnt From #spid where ltrim(rtrim(replace(blkby,'.',''))) = '')
drop table #spid
GO
|
Printable Version
SQL 2000 SPID Block Monitor - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread