dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 20, 2004

Monitor Blocking in SQL Server 2000 - Part 1 - Page 2

By Muthusamy Anantha Kumar aka The MAK

Step 4

Execute the job "Job_BlockMonitor" as shown in Fig 1.5

Step 5

Since we gave "d:\blocking.log" as the File Path in the job step, it creates the file on D:\ with blocking details as shown in Fig 1.6

8 No Waittypes: 
  2004-09-24 22:36:54.030 30 SQL
8 No Waittypes: 
  2004-09-24 22:37:42.857 16 SQL
8 No Waittypes: 2004-09-24 22:40:00.140 0 SQL
8 No Waittypes: 2004-09-24 22:43:32.763 0 SQL
8 No Waittypes: 2004-09-24 22:44:24.140 0 SQL
8 No Waittypes: 2004-09-24 22:44:49.640 0 SQL
8 No Waittypes: 2004-09-24 22:45:00.547 0 SQL


SYSPROCESSES SQL  134218488
ESP 1063

SYSPROC FIRST PASS
No blocking via locks at 
  2004-09-24 22:45:50.810

DBCC SQLPERF(WAITSTATS)
MISCELLANEOUS,0.00000,0.00000,0.00000
LCK_M_SCH_S,0.00000,0.00000,0.00000
LCK_M_SCH_M,1.00000,188.00000,0.00000
LCK_M_S,25.00000,6468.00000,79.00000
LCK_M_U,0.00000,0.00000,0.00000
LCK_M_X,0.00000,0.00000,0.00000
LCK_M_IS,0.00000,0.00000,0.00000
LCK_M_IU,0.00000,0.00000,0.00000
LCK_M_IX,0.00000,0.00000,0.00000
LCK_M_SIU,0.00000,0.00000,0.00000
LCK_M_SIX,0.00000,0.00000,0.00000
LCK_M_UIX,0.00000,0.00000,0.00000
LCK_M_BU,0.00000,0.00000,0.00000
LCK_M_RS_S,0.00000,0.00000,0.00000
LCK_M_RS_U,0.00000,0.00000,0.00000
LCK_M_RIn_NL,0.00000,0.00000,0.00000
LCK_M_RIn_S,0.00000,0.00000,0.00000
LCK_M_RIn_U,0.00000,0.00000,0.00000
LCK_M_RIn_X,0.00000,0.00000,0.00000
LCK_M_RX_S,0.00000,0.00000,0.00000
LCK_M_RX_U,0.00000,0.00000,0.00000
LCK_M_RX_X,0.00000,0.00000,0.00000
SLEEP,102219.00000,102267936.00000,102267872.00000
IO_COMPLETION,366.00000,57577.00000,79.00000
ASYNC_IO_COMPLETION,26.00000,10828.00000,0.00000
RESOURCE_SEMAPHORE,0.00000,0.00000,0.00000
DTC,0.00000,0.00000,0.00000
OLEDB,35111.00000,1943038976.00000,92694416.00000
FAILPOINT,0.00000,0.00000,0.00000
RESOURCE_QUEUE,25068.00000,306792736.00000,102271544.00000
ASYNC_DISKPOOL_LOCK,540.00000,0.00000,0.00000
UMS_THREAD,0.00000,0.00000,0.00000
PIPELINE_INDEX_STAT,0.00000,0.00000,0.00000
PIPELINE_LOG,0.00000,0.00000,0.00000
PIPELINE_VLM,0.00000,0.00000,0.00000
WRITELOG,2083.00000,29616.00000,251.00000
PSS_CHILD,0.00000,0.00000,0.00000
EXCHANGE,0.00000,0.00000,0.00000
XCB,0.00000,0.00000,0.00000
DBTABLE,0.00000,0.00000,0.00000
EC,0.00000,0.00000,0.00000
TEMPOBJ,0.00000,0.00000,0.00000
XACTLOCKINFO,0.00000,0.00000,0.00000
LOGMGR,0.00000,0.00000,0.00000
CMEMTHREAD,0.00000,0.00000,0.00000
CXPACKET,0.00000,0.00000,0.00000
PAGESUPP,0.00000,0.00000,0.00000
SHUTDOWN,0.00000,0.00000,0.00000
WAITFOR,0.00000,0.00000,0.00000
CURSOR,0.00000,0.00000,0.00000
EXECSYNC,0.00000,0.00000,0.00000
LATCH_NL,0.00000,0.00000,0.00000
LATCH_KP,0.00000,0.00000,0.00000
LATCH_SH,0.00000,0.00000,0.00000
LATCH_UP,0.00000,0.00000,0.00000
LATCH_EX,0.00000,0.00000,0.00000
LATCH_DT,0.00000,0.00000,0.00000
PAGELATCH_NL,0.00000,0.00000,0.00000
PAGELATCH_KP,0.00000,0.00000,0.00000
PAGELATCH_SH,1347.00000,516.00000,516.00000
PAGELATCH_UP,926.00000,552.00000,536.00000
PAGELATCH_EX,173.00000,30.00000,30.00000
PAGELATCH_DT,0.00000,0.00000,0.00000
PAGEIOLATCH_NL,0.00000,0.00000,0.00000
PAGEIOLATCH_KP,0.00000,0.00000,0.00000
PAGEIOLATCH_SH,1047.00000,47477.00000,281.00000
PAGEIOLATCH_UP,363.00000,8713.00000,126.00000
PAGEIOLATCH_EX,215.00000,6816.00000,0.00000
PAGEIOLATCH_DT,0.00000,0.00000,0.00000
TRAN_MARK_NL,0.00000,0.00000,0.00000
TRAN_MARK_KP,0.00000,0.00000,0.00000
TRAN_MARK_SH,0.00000,0.00000,0.00000
TRAN_MARK_UP,0.00000,0.00000,0.00000
TRAN_MARK_EX,0.00000,0.00000,0.00000
TRAN_MARK_DT,0.00000,0.00000,0.00000
NETWORKIO,1787.00000,13312.00000,0.00000
Total,171297.00000,2352281856.00000,297235744.00000

*********************************************************************
Print out DBCC Input buffer for all blocked or blocking spids.
*********************************************************************

DBCC INPUTBUFFER FOR SPID 60
Language Event
0
DBCC CheckDB([ship])


[Fig 1.6]

Conclusion

This article has covered how to take advantage of the Microsoft Knowledge Base article 271509 to monitor blocking and capture the SQL statements that are involved in blocking to a file. This was accomplished by updating the procedure from KB Article 271509 in such a way that it writes the output directly to a file.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM