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