SQL Server Blocking Issue
- by Robin Weston
We currently have an issue that occurs roughly once a day on SQL 2005 database server, although the time it happens is not consistent.
Basically, the database grinds to a halt, and starts refusing connections with the following error message. This includes logging into SSMS:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Our CPU usage for SQL is usually around 15%, but when the DB is in it's broken state it's around 70%, so it's clearly doing something, even if no-one can connect. Even if I disable the web app that uses the database the CPU still doesn't go down.
I am unable to restart the SQLSERVER process as it is unresponsive, so I have to end up killing the process manually, which then puts the DB into Suspect/Recovery mode (which I can fix but it's a pain).
Below are some PerfMon stats I gathered when the DB was in it's broken state which might help. I have a bunch more if people want to request them:
Active Transactions: 2 (Never
Changes) Logical Connections: 34 (NC)
Process Blocked: 16 (NC) User
Connections: 30 (NC) Batch Request: 0
(NC) Active Jobs: 2 (NC) Log
Truncations: 596 (NC) Log Shrinks: 24
(NC) Longest Running Transaction
Time: 99 (NC)
I guess they key is finding out what the DB is using it's CPU on, but as I can't even log into SSMS this isn't possible with the standard methods.
Disturbingly, I can't even use the dedicated admin connection to get into SSMS. I get the same timout as with all other requests.
Any advice, reccomendations, or even sympathy, is much appreciated!