May 20, 2010 at 1:26 pm
Recently we've migrated a database from SQL 2000 to SQL 2008, there were some teething problems with performance but that appeared to stabilise, automated stored procedures which included references to a linked server which produced data appeared to work fine for first week and all appeared to work well, then all of a sudden we started having issues with the stored procedures producing SOS_SCHEDULER_YIELD, the server itself is a decent spec server running Windows Server 2008 R2.
We killed the processes but they don't appear to be rolling back properly, using various DMV's and scripts that appear to have been Killed but are still showing the SOS_SCHEDULER_YIELD, obviously been reading up about this wait type a lot, and the processors do not appear to be excessively used so not sure why the Rollbacks don't appear to have started. We've discussed restarting the Node (it's on an active/passive cluster) but we don't want the risk of SQL simply replaying the SQL again (which has been suggested) or run the risk of the database starting in suspect mode or been analysed first.
We've restarted some of the servers which called the stored procedures as well to no avail.
Has anyone else come across this and if so were you able to get the rollbacks to work OK or simply get them pernamently killed off?
July 8, 2010 at 1:09 am
Just an FYI in case anyone else experiences this issue in the future, according to Microsoft these zombie Spids were caused by a bug which can occur in rare circumstances if your database connects to a SQL 2000 database via a linked server, the only way to resolve this is to restart SQL, we failed it over to the second node and all was fine after that...
October 26, 2010 at 10:53 am
In case anyone else sees something related to this, but is a non-yielding process it is addressed in SQL 2008 R2 with CU3 http://support.microsoft.com/kb/2261464/
and in SQL 2008 SP1 in CU4 http://support.microsoft.com/kb/973602/
Regards, Irish
November 5, 2010 at 1:31 pm
This has been occurring on our production server of late, Windows Server 2008 R2 64 bit with 64 bit SQL Server 2008 SP1 CU8. Where is it stated that CU4 should address this issue?
November 8, 2010 at 4:42 am
Hi,
You may want to look at this forum thread.
I was experiencing a SOS_SCHEDULER_YIELD issue in addition to performance problems. Following a weeklong running call with microsoft we resolved the issue in two steps. I enabled the ability to lock pages in memory for the service account against the original recommendations of Microsoft who state it is no longer a requirement. This helped the situation tremendously but was not the solution.
In the thread you will notice two links to windows 2008 R2 hotfixes. This fixed the issues for us.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply