September 23, 2008 at 9:46 pm
I recently updated my SQL server 2005 installation to SP2 and the latest SQL roll up of hotfixes, I am running on enterprise version of 2003 and have standard version of SQL2005 with 16GB of memory.
My problem started 3 days ago, 2 weeks after the update to SQL, I found that my nightly optimization job was being blocked by SPID -2, the optimization was in tern blocking user transactions on my database (The Command ALTERINDEX was being blocked by SPID -2).
To resolve I stopped the optimization which is configured to rebuild indexes and this immediately resolved the problem, from what I could see the optimization was blocked before it could start the indexrebuild.
There is very little information I can find on this though I have seen this on another system but I do not have information on how it was solved. From the little information I can find there is mention that SPID -2 is an orphaned MSDTC transaction, looking in component services I can see one active transaction from another server on the network which seems to remain open, when I check on that other server the transaction with the same ID exists and is active.
This has happened twice over the last 3 days and I am at a loss as to why this is happening and how to stop it for re occurring.
September 24, 2008 at 12:16 am
I don't know about the root cause of the locking issue, but one thing that you could do to prevent the index optimization job from hanging is to use SET LOCK_TIMEOUT in the script.
I'm using this method in my index optimization stored procedure.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
September 24, 2008 at 12:33 am
Ola Hallengren (9/24/2008)
I don't know about the root cause of the locking issue, but one thing that you could do to prevent the index optimization job from hanging is to use SET LOCK_TIMEOUT in the script.I'm using this method in my index optimization stored procedure.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
Thanks, as you say you do not know the cause, however I found your article very useful and will definatly be following some of your advice.
Thanks,
Chris.
September 28, 2008 at 9:22 pm
chris.barrow (9/24/2008)
Ola Hallengren (9/24/2008)
I don't know about the root cause of the locking issue, but one thing that you could do to prevent the index optimization job from hanging is to use SET LOCK_TIMEOUT in the script.I'm using this method in my index optimization stored procedure.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
Chris.
I was also given the following information on another forum I belong to which should be useful to others reading this post;
See these KBAs http://support.microsoft.com/kb/949075/en-US &
http://support.microsoft.com/kb/954669 for more information.
September 30, 2008 at 6:55 am
The MSDTC orphaned transactions hotfix was not included in CU8 which I had previously applied, but it did make it into CU9 for SQL 2005, so this should fix the problem - http://support.microsoft.com/kb/954669
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy