SQL Server 2005, non-yielding scheduler Spinloop on Snapshot

Question

I was running dbWarden's procedure for CheckFiles on my server.

The job never ended and my logs started to fill up with message at a rate of 1 per minute: 2014-01-07 08:40:04.07 Server Process 58:0:0 (0x1438) Worker 0x00000000D62B41C0 appears to be non-yielding on Scheduler 4. Thread creation time: 13033413586324. Approx Thread CPU Used: kernel 46 ms, user 2421 ms. Process Utilization 10%. System Idle 82%. Interval: 79691009 ms.

From what I understand is that it means the thread on the CPU is just locked up.

The SSMS GUI just doesn't work with the instance any longer. In TSQL most things work, unless they reference the snapshot, which nixes most dmvs and system procedures. SP_Who2 doesn't work, but SP_Who does.

SP_WHO shows me 58 alternating between ready and spinloop.

58 is running the code to check files (locations, growth information, etc) on a snapshot.

When I KILL 58, it says it's rolling back, but nothing happens and it's still in a spinloop.

When I try SP_Who2, it gets blocked by the spinloop. When I drop the snapshot, it's blocked by the spinloop. When I restart or Stop/Start the instances service, the errors return to the log and the spinloop resumes.

Any ideas or advice?

Answer 1

The default is the problem, as a rogue process would utilize all the available threads and will lead to thread starvation. Better is to adjust the maxdop setting as described in What is a good, repeatable way to calculate MAXDOP on SQL Server?

Also, let the rollback complete as if you restart sql server when a rollback is in progress, it will redo everything that it has already done when the service is restarted.

A similar case can be found Need to understand parallel query execution error. This has more details that I put in and will explain you better.