Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Deadlock
Add to briefcase

Deadlock Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
nivedita.kathal
Posted Saturday, January 17, 2009 10:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 31, 2009 2:05 AM
Points: 22, Visits: 51
Hi,

I am facing deadlock problem at the page level in the production database server.
Can any one suggest me the method to resolve such kind of problems.

thanks
Post #638861
Steve Jones - SSC Editor
Posted Sunday, January 18, 2009 9:20 AM


One Orange Chip

One Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange ChipOne Orange Chip

Group: Administrators
Last Login: Today @ 4:06 PM
Points: 29,496, Visits: 11,673
Moved to administration. Please post in the appropriate forum.

You can trace this with Profiler and capture information. Once you track down the statements that are causing issues, you can look to rewrite them or perhaps reindex to speed up operations.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #638941
GilaMonster
Posted Sunday, January 18, 2009 9:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044
Enable traceflag 1222. You can use use a DBCC statement (DBCC TRACEON(1222,-1)), or add -t1222 to the startup parameters of the SQL instance (needs a restart to take effect)

Once that traceflag is enabled, SQL will write the deadock graph into the error log. That contains detailed information on what processes and resources were involved in the deadlock. It should help you find and fix the cause of the deadlock

Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #638944
ravikanth.
Posted Sunday, January 18, 2009 10:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,503, Visits: 1,615
DBCC TRACEON(1204,1222)

1204 - This trace flag reports deadlock information formatted by each node involved in the deadlock

1222 - This trace flag formats deadlock information, first by processes and then by resources.
Post #638950
GilaMonster
Posted Sunday, January 18, 2009 10:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044
ravikanth (1/18/2009)
DBCC TRACEON(1204,1222)

1204 - This trace flag reports deadlock information formatted by each node involved in the deadlock

1222 - This trace flag formats deadlock information, first by processes and then by resources.


There's no need for both. The information returned by 1222 is a superset of the info returned by 1204, as such it's redundant having both. Use 1222 on SQL 2005 and higher (as it was introduced in 2005) and 1204 in SQL 2000 and below.

Also, you need to enable the traceflags globally. The command there will only enable them for the current session, which is useless as it's the system processes that catch deadlocks.

Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #638970
MichaelJasson
Posted Sunday, January 18, 2009 11:57 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, Visits: 191
GilaMonster, One small information I want from you. Do we need to restart the sql service everytime we enable any trace or this is applicable to only some of the traces?

-M

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Post #638997
GilaMonster
Posted Sunday, January 18, 2009 12:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044
MichaelJasson (1/18/2009)
Do we need to restart the sql service everytime we enable any trace or this is applicable to only some of the traces?


If you enable the traceflag using DBCC TRACEON, the traceflag becomes active immediately and remains active until it's switched off (using DBCC TRACEOFF) or until the service is restarted

If you add the traceflag to SQL's startup parameters -t<trace flag> then the service needs to be restarted for it to take effect, as the startup parameters are only evaluated at startup.

That's applicable to all traceflags.

Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #639004
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.