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!