SQL Server Performance Forum – Threads Archive

Creating self blocking in sql server

I have created a procedure that logs the blocking information on the server to a table.I have tested it by creating a blocking on QA using scripts .I need to check it for self blocking ie same process blocking the same .How I can create a self blocking on sql server using sql server. Thanks DBA
SHAMS

One spid can’t block itself, why do you need this?

I need to track the blocks that are happening on the server.I have seen few spids with same number in current activity .It says spid 61 blocking spid 61. How to track this type of blocking . Thanks DBA
SHAMS

quote:Originally posted by thomas One spid can’t block itself, why do you need this?

In rare circumustances on multi proc and parallalism enabled servers may cuase self blocking…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes I have seen this in 2000 SP3. Maybe try running a very big query which is executed in parallel and see what happens? But jimmy, this is not true blocking. It’s just the way SQL Server tracks parallel query execution. So if you’re concerned about blocking, you can ignore this kind of blocking.

You could take help of DMVs and referring the wait types within SQL 2005.
The list of wait types is available from the sys.dm_os_wait_stats dynamic management view (DMV). So when we talk about Wait types these can occur only at times when they do not affect user activity, such as during initial server startup and shutdown, and are not visible to users.
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.

One more thing…SQL SERVER 2000 in SP4, MS introduced new way of troubleshooting… The blocked column in the sysprocesses table is populated for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/default.aspx/kb/906344
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I think we need to be clear whether SHAMS need for 2000 or 2005 [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Currently iam testing for sql 2000 DBA
SHAMS

]]>