Monitor Open transactions which are sleeping or suspended in SQL server

Dear all,

In MS SQL server when a Query is fired from application it is considered that i will get executed from SQl server within 30 secs by default. Some times due to heavy transactions it may take more time which we can configure from web.config by adding “Connect Timeout = ” parameter.

For example we have configured 180 seconds in web.config for connect timeout which measn asp.net will wait till 180 secs to collect result from SQL server .

What if transaction takes more than 180 seconds ? It indicates that some issue in Query or Database design ( Missing indexes ).

To find such qeries we can use below code

————–Created by prashant Deshpande——————————————————————-
– This stored procedure can be used to monitor the sleeping or sunpended process for more than 200 secs
– as default SQl server leaves any connection after 30 secs , but if in web.config connect timeout parameter is
– given then we need to change the wait time value accordingly
– here I have given 200000 which means 200 secs
———————————————————————————
create proc block_monitor as
SELECT db_name(sp.dbid)as database_name,sp.spid,sp.status,text as TSQL_Statement,waittime FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST
WHERE db_name(sp.dbid) not in (’Master’,’Msdb’,’Report’,’Tempdb’)
and sp.open_tran>0 and sp.waittime >=200000
and ( sp.status =’sleeping’ or sp.status =’suspended’)
order by sp.dbid

after creating the above SP ( block_monitor ) we will need to monitor the results of this SP.

I have created below VBS script which will monitor the results and will send an email alert if issue found.

‘ =============== Script Details =================
‘ Script will execute Storped Procedure
‘ Collects the output and writes in Text file
‘ ===================================
‘ ================= Declare Variables ===============
Dim Conn,cmdUpdate, cmdRead,rs
dim filesys, filetxt, getname, path
‘================= Assign Values to variable ==========
‘ Create Database object
‘ open database connection and create command type object and
‘ specify type of command to be executed like adCmdText or adCmdStroedproc
‘================================================

set Conn = CreateObject(”ADODB.Connection”)
Conn.Open “Provider=sqloledb;Data Source=(Local);Initial Catalog=master;Integrated Security=SSPI;persist security info=False;Trusted_Connection=Yes;”
‘=========== parameters ===========================
‘ if any parameter is supposed to be send to sp the
’ cmd.Parameters.Append cmd.CreateParameter(”@p1″, 200,&H0001, 80, parmnm)
‘ cmd.Parameters.Append cmd.CreateParameter(”@p2″, 200,&H0001, 80, var1)
‘================================================
‘================ record set and file writing ============
‘ create recordset object and collect the output of SP in it
‘ create file object and here checking if the fil eis already ther or not
‘ and accordingly it will take action if file is ther it wil just opne or it will create new one
‘================================================
set cmdRead = CreateObject(”ADODB.Command”)
cmdRead.ActiveConnection = Conn
‘cmdRead.CommandType = adCmdStoredProc
cmdRead.CommandText = “block_monitor”
set rs = CreateObject(”ADODB.RecordSet”)
set rs= cmdRead.execute()

dim filename,foldername
foldername=”c:\dblogs”
Set filesys = CreateObject(”Scripting.FileSystemObject”)
if filesys.FolderExists(foldername) = False then
filesys.CreateFolder Foldername
end if
dim ssplit
ssplit=split(date(),”/”)
dim final
final=ssplit(0) + “-Block”
filename=final +”.txt”
filename=”c:\dblogs\” +filename

If Not filesys.FileExists(filename) Then
else
filesys.DeleteFile(filename)
end if
Set filetxt = filesys.CreateTextFile(filename, True)

path = filesys.GetAbsolutePathName(filename)
getname = filesys.GetFileName(path)

do while not rs.eof
filetxt.WriteLine rs(”Database_Name”) & “ ” & rs(”SPID”) & “ ” & rs(”status”) & “ ” & rs(”TSQL_Statement”)

rs.movenext
loop
filetxt.Close

Set TargetFile = filesys.GetFile(Filename)
FileSize = TargetFile.Size
if filesize >0 then
dim strbdy
strdby=”Please find here with attached SQl server Internal porcesses ( T-SQL ) which are sleeping or suspended from more than 200 Seconds and SQL server not able to process more requests
strdby=strdby+”
Please check on priority basis

‘ create the output file here
‘wscript.echo strdby
Send_Email true,strdby
end if

Private Function Send_Email(blnAsAttachment,msg)
‘wscript.echo “attach” &blnAsAttachment
‘wscript.echo “strhtml” &strhtml
‘wscript.echo “msg” &msg
‘ wscript.echo “in side mailer”

Const SMTP_SERVER =”202.137.237.24″ ‘
Const SMTP_PORT = 587
Const SMTP_CONNECTION_TIMEOUT = 10
‘SET YOUR STMP EMAIL ACCOUNT SETTINGS BELOW
Const SMTP_USERNAME =”XXXX@XX.XXX”
Const SMTP_EMAIL_FROM =”XXXX@XX.XXX”
Const SMTP_PASSWORD=”XXXXXXX”

Const EMAIL_TO = “XXXX@XX.XXX;XXXX@XX.XXX“ ’<- COMMA SEPERATED LIST OF RECIPIENTS ;
EMAIL_SUBJECT = “Database Active Connections “+ strHTML
’SEND THE EMAIL TO RECIEPIENTS
’SOME UGLY CONSTANT DEFINITIONS
const cdoSendUsingPort = 2
const cdoBasic = 1
const cdoSendUsingMethod = “http://schemas.microsoft.com/cdo/configuration/sendusing”
const cdoSMTPAuthenticate = “http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”
const cdoSMTPServer = “http://schemas.microsoft.com/cdo/configuration/smtpserver”
const cdoSMTPServerPort = “http://schemas.microsoft.com/cdo/configuration/smtpserverport”
const cdoSMTPConnectionTimeout = “http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout”
const cdoSendUserName = “http://schemas.microsoft.com/cdo/configuration/sendusername”
const cdoSendPassword = “http://schemas.microsoft.com/cdo/configuration/sendpassword”
dim cdoConfig, cdoMessage
’CREATE CDO OBJECTS
set cdoConfig = CreateObject(”CDO.Configuration”)
set cdoMessage = CreateObject(”CDO.Message”)
’SET OBJECT PROPERTIES
cdoConfig.Fields.Item(cdoSendUsingMethod) = cdoSendUsingPort
cdoConfig.Fields.Item(cdoSMTPAuthenticate) = cdoBasic
cdoConfig.Fields.Item(cdoSMTPServer) = SMTP_SERVER
cdoConfig.Fields.Item(cdoSMTPServerPort) = SMTP_PORT
cdoConfig.Fields.Item(cdoSMTPConnectionTimeout) = SMTP_CONNECTION_TIMEOUT
cdoConfig.Fields.Item(cdoSendUserName) = SMTP_USERNAME
cdoConfig.Fields.Item(cdoSendPassword) = SMTP_PASSWORD
’FORCE PROPERTIES TO UPDATE
cdoConfig.Fields.Update
’SET THE CONFIGURATION TO THE ONE THAT WE JUST SETUP
Set cdoMessage.Configuration = cdoConfig
’SET GENERAL EMAIL OPTIONS
cdoMessage.From = SMTP_EMAIL_FROM
cdoMessage.To = EMAIL_TO
cdoMessage.CC =”XXXX@XX.XXX”
cdoMessage.BCC = “”
cdoMessage.Subject = EMAIL_SUBJECT
’TO ATTACH OR NOT TO ATTACH….THAT IS THE QUESTION

If blnAsAttachment Then

dim ssplit
ssplit=split(date(),”/”)
dim final
final=ssplit(0) + “-block”
file=final +”.txt”
file=”c:\dblogs\” +file
‘file=”c:\temp\splist.rar”

cdoMessage.AddAttachment file
cdoMessage.HTMLBody = msg
Else
cdoMessage.HTMLBody = strHTML
End If
’GENERIC NO HTML CLIENT MESSAGE
cdoMessage.TextBody = “Your Email Client Does Not Support HTML”
’SEND THE EMAIL OUT
cdoMessage.Send
’CLEANUP
Set cdoMessage = Nothing
Set cdoConfig = Nothing
‘ wscript.echo “send”
End Function

Just schedule a task for VBS as per requirement and get relaxed.

Thanks

Prashant Deshpande

Advertisements

5 Comments

Filed under Uncategorized

5 responses to “Monitor Open transactions which are sleeping or suspended in SQL server

  1. JM

    can you email the scripts. The formatting changes when I just and past. I was able to fix the SQL statement. But I am not sure how to correct the VB script. Thank you JM

  2. Dana York

    Dear Prashantd, Can you please email the scripts to me as well? This is exactly what I wam looking for. Thanks!

    • Hi, The script is already there in post.
      the only thing you will have to do s paste eh script code in notepad and replace ‘ with single quot as it gets replaced while posting

      Thanks

  3. Aayush

    Hi,
    You have posted a very informative blog but there are a lot of typos I could see which sometimes makes the text quite difficult to understand.Hope you would take my comment as a piece of advice.Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s