Monthly Archives: June 2011

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

Customizing Error Message in IIS

Dear All,

 

As you all know whenever there is any exception on Web server for any site the ERROR messages are thrown from server which are generic message.

For example when any website is not available on server it shows below message

We can configure customized error messages in IIS which can guide the user. Below are the steps which I have used to provide a user friendly message for all those sites which we have removed from live server and configured on local server , when any user tries to those websites he will be shown a pre-configured customized page

Steps

1)Open Inet manager

 

 

2) Select the website on which we want to configure the message page. And double click on Error pages icon

3) After clicking on Error pages it will open below window which are the generic error codes and its associated error pages

4) Select the error code and click on EDIT button from right panel.

5) From the windows click on Respond with a 302 redirect Absolute URL and provide the URL from where it will pick up the customize message page

That’s all

Now whenever your will try for any URL which is not present on server he will get

 

Thanks

 

Prashant Deshpande

 

Leave a comment

Filed under Uncategorized