SQL Server Performance Forum – Threads Archive

blocking needs to be stored in table

In order to identify the blocking process iam writing procedure to log the info into a table ,I also need to log the command that was executing at the time of blocking is happening. I am planning to use master..sysprocess table for the same .How i can fing the queries that are running at that time (DBCC INPUTBUFFER (PID)Can serve the purpose .Do anyone have have any better way to do this .Does this queries are stored in any table ? Thanks in advance DBA
SHAMS

---

dbcc inputbuffer only gives you the first 256 characters. In sql 2005 you can use fn_get_sql and dynamic management views, Satya did a blog on this recently which will help you.. http://sqlserver-qa.net/blogs/perft…-a-process-spid-using-tools-tsql-queries.aspx

---

Now I need the information for sql 2000 server only .SO dbcc inputbuffer is the pnly resource ? DBA
SHAMS

---

You can also use sql_handle in SQL Server 2000, like this
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 115
SELECT * FROM ::fn_get_sql(@Handle)

---

I have logged in to the server as sa(Query analyser) and the command does not return any values .DBCC INPUTBUFFER IS WORKING FINE .any conig changes needs to be done on this to work DBA
SHAMS

---

It has to be an active process. If its status isn’t runnable or similar (killed/rollback, for example), it won’t return anything. dbcc inputbuffer will work even on sleeping processes. But if you’re interested in blocking, I expect active processes are the ones you’ll be after. you may find dbcc inputbuffer is enough.

---

you can also make use of the script from the following url.. How to monitor blocking in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/kb/271509/
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thanks ,this was too helpful to resolve the issue.
DBA
SHAMS

---

REcently I wrote a blog to catch up such tasks:
http://sqlserver-qa.net/blogs/perft…-a-process-spid-using-tools-tsql-queries.aspx
FYI
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>