2

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?

  • What is the maxdop setting on your server ? If it is default, then you have to change it. Refere here on the best way to calculate – Kin Shah Jan 7 '14 at 18:05
  • It's the default value of 0. – ShawnMTherrien Jan 7 '14 at 18:20
  • The default is the problem, as a rouge process would utilize all the available threads and will lead to thread starvation. Better is to adjust the maxdop setting as described in my above comment. Also, let the rollback complete as if you restart sql server when a rollback is in progress, it will redo everything when the service is restarted. – Kin Shah Jan 7 '14 at 21:16
  • Well that did it. Process is gone, no errors are being logged. SSMS is now responsive. Can you add this as an answer so I can mark it completed? And Thank you very much! – ShawnMTherrien Jan 7 '14 at 21:21
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.