Our error log is filled with errors of this nature:
Error: 9646, Severity: 16, State: 2.
An error occurred in the timer event cache. Error 845, state 1.
Process 491:0:2 (0x184c) Worker 0x00000010754D2160 appears to be non-yielding on Scheduler 24. Thread creation time: 13020506683971. Approx Thread CPU Used: kernel 1248 ms, user 13594083 ms. Process Utilization 10%. System Idle 89%. Interval: 20353254 ms.
A time-out occurred while waiting for buffer latch -- type 2, bp 00000010ADB0AB00, page 1:240, stat 0x9, database id: 16, allocation unit Id: 281474981494784/4784128, task 0x0000000751019498 : 0, waittime 300 seconds, flags 0x3038, owning task 0x0000000000000000. Not continuing to wait.
As well as these:
Service Broker needs to access the master key in the database 'WebResources'. Error code:32. The master key has to exist and the service master key encryption is required.
Is it all resource related?
- Edited by SQL_Mike123 Saturday, August 10, 2013 6:19 AM
Can you check this link
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thank you very much for the link. We will try the suggestions and see if they help.
We do want to add more memory to the server as well - currently at 64GB and planning to upgrade it to 164GB.
Currently we had to disable all major jobs on the server and every time we experience an issue we end up restarting SQL Server.
Btw, we're running Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Whenever we experience a problem SQL Server continues to function fine but no longer works with our website. We end up restarting the server as killing connections becomes an almost impossible task.
The blog did not help us.
We've upgraded memory from 64GB to 160GB and disabled the broker on the heaviest database. That helped us with the frequency of outages (once a day), instead of several times a day. I'm convinced it's a SQL Server 2012 SP1 issue because we did not have this problem with SQL Server 2008 R2 SP3 from which we've migrated our databases less than a month ago.
I checked the error logs on the SQL 2008 server and in a over year and a half we have not had a single time-out or a stack dump issue, which we started having as soon as we switched to SQL Server 2012.
We tried installing SQL Server 2012 CPU4 which addresses 2 issues with the broker but the engine failed to update both times (SQL Server Services were shut down).
It's highly frustrating to say the least to go from a working system to something that's supposed to be newer and increasingly better and instead get mired in these issues.