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.
| Author |
Topic |
|
VikasKolluri
Starting Member
2 Posts |
 Posted - 2009-11-24 : 15:49:01
|
| Hi,I am new to SQL Server Administration. We moved a 90 gig DB from old (windows 2000 server with sql 2000) to the new server (windows 20003 server with sql 2000 sp4). Everything works fine on the old server but after I moved the DB to the new server, few queries are failing. The issue is with a complex query having almost 60 + selects and 60 + union alls to get the data from a single table with 60 different where clauses and group by clauses is failing. It throws the following error:Transaction (Process ID: 51) was deadlocked on thread. Communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Can some one please help me with this. Thanks in advance for your time. - Vikas |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-24 : 16:03:09
|
| You'll need to determine who the deadlock owner is in order to troubleshoot this. Please check SQL Server Books Online for how to troubleshoot deadlocks. You should consider upgrading to SQL Server 2005 or 2008 as you can use the READ_COMMITTED_SNAPSHOT isolation level to avoid SELECT statements from being involved in the deadlock chain.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
VikasKolluri
Starting Member
2 Posts |
Posted - 2009-11-24 : 16:13:52
|
| I have tried to do some troubleshooting by following the steps mentioned at the following URL:http://www.sqlservercentral.com/articles/Performance+Tuning/tracingdeadlocks/1324/But when I go to C:\Program Files\Microsoft SQL Server\MSSQL\ there is no log file in there. I went to see in Enterprise Manager, there are 4 logs, but when ever I try to open them, Enterprise Manager dies at me and the log file never opens. I had to end the task and open the Enterprise Manager once again. Pls advise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-24 : 16:30:38
|
| That's because you haven't restarted SQL Server in a while, so the error logs are huge. If you aren't going to restart periodically, then you should cycle the logs say weekly. We restart once a month due to the montly Microsoft security patches.By the way, the log files will open eventually, you just aren't waiting long enough. I doubt you'll find anything in them though since you'd need to turn on the deadlock traceflag in order for meaningful information to be put into them about deadlocks.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
Deadlocks issue on SQL 2000