StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server Blocking Issue
text
Body
copied!<p>We currently have an issue that occurs roughly once a day on SQL 2005 database server, although the time it happens is not consistent.</p> <p>Basically, the database grinds to a halt, and starts refusing connections with the following error message. This includes logging into SSMS:</p> <p><strong>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.)</strong> </p> <p>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.</p> <p>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).</p> <p>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:</p> <ul> <li>Active Transactions: 2 (Never</li> <li>Changes) Logical Connections: 34 (NC)</li> <li>Process Blocked: 16 (NC) User</li> <li>Connections: 30 (NC) Batch Request: 0</li> <li>(NC) Active Jobs: 2 (NC) Log</li> <li>Truncations: 596 (NC) Log Shrinks: 24</li> <li>(NC) Longest Running Transaction</li> <li>Time: 99 (NC)</li> </ul> <p>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.</p> <p>Disturbingly, I can't even use the dedicated admin connection to get into SSMS. I get the same timout as with all other requests.</p> <p>Any advice, reccomendations, or even sympathy, is much appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload