January 17, 2009 at 10:32 pm
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
January 18, 2009 at 9:20 am
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: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 18, 2009 at 9:33 am
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2009 at 10:07 am
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.
January 18, 2009 at 10:39 am
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2009 at 11:57 am
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.
January 18, 2009 at 12:15 pm
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply