Error Log Message for Blocking

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error Log Message for Blocking

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2008-10-23 : 11:57:51

Hi All,
I have turned on flags 1222,1205 globally, so that when i get a deadlock sql server writes to error log, though i was seeing deadlocks occasionally, i am acting on those. But i see log of messages like below in the error log

2008-10-23 02:54:25.39 spid4s Starting deadlock search 698
2008-10-23 02:54:25.39 spid4s Target Resource Owner:
2008-10-23 02:54:25.39 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000000806A7300 Mode: IX SPID:69 BatchID:0 ECID:0 TaskProxy:(0x000000013375A598) Value:0xb2a88880
2008-10-23 02:54:25.39 spid4s 0:Insert new node: Node:1 ResType:LockOwner Stype:'OR'Xdes:0x00000000806A7300 Mode: IX SPID:69 BatchID:0 ECID:0 TaskProxy:(0x000000013375A598) Value:0xb2a88880
2008-10-23 02:54:25.39 spid4s 1:SearchOR Considering new blocker - task: 0000000000C3FD68, Worker 00000000E8AA41C0
2008-10-23 02:54:25.39 spid4s 2:Insert new node: Node:2 ResType:LockOwner Stype:'OR'Xdes:0x000000008005C8E0 Mode: IX SPID:66 BatchID:0 ECID:0 TaskProxy:(0x00000001332B2598) Value:0x801a7bc0
2008-10-23 02:54:25.39 spid4s 3:SearchOR Considering new blocker - task: 0000000000FF65C8, Worker 00000000F79281C0
2008-10-23 02:54:25.39 spid4s 3:SearchOR Considering new blocker - task: 00000000054589B8, Worker 00000000F64021C0
2008-10-23 02:54:25.39 spid4s 3:SearchOR search result: No deadlock
2008-10-23 02:54:25.39 spid4s 1:SearchOR search result: owner considered does not lead to deadlock, will consider next owner/blocking task
2008-10-23 02:54:25.39 spid4s 1:SearchOR search result: No deadlock
2008-10-23 02:54:25.39 spid4s End deadlock search 698 ... a deadlock was not found.

i see numerious messages like these,i am affraid that i have too much blocking going on, is this true, how do i go about resolving these.by this message i am not able to figure out anything, is there a way to get the queries.

Thanks you all for support

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 13:08:42

Buzzi,

There is no occurence of deadlock in your error log. since you also enabled -T 1205, it gives you detailed message about resources and owners about deadlock.

buzzi
Starting Member

48 Posts

Posted - 2008-10-23 : 13:36:57

Thank You very much,
Yes you are right, but the spids SPID:69 & SPID:66, blocked each other for sometime which didn't end up in a dead lock, Since i am seeing these kind of messages a lot I am working worried that there might be lot of blocking happening.Is there are a way to find out on what resources the spids are waiting, like tables so that i can create more usefull indexes, to minimize the blocking

Thanks again

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 14:16:48

http://www.sql-server-performance.com/tips/blocking_p1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources