SQL Server – How to Diagnose Unresponsive or Crashed SQL Server

sql serversql-server-expresswindows-server

Recently an SQLExpress instance suddenly become unresponsive/crashed whilst I was mid-dialog (SSMS transaction log backup), which froze with an hourglass and the dialog was unusable. No other applications or users on the network were able to access instance. I tried to restart using Services, but that didn't respond. Eventually I rebooted the server entirely and it came back up. All the databases in use at the time appear to still be working post-reboot and not corrupt.

Where are the best places to look to find out the source of this crash?

I looked at Event Viewer > Applications, but the only messages around the time of the crash are (in order):

  1. The last dialog box I received before the crash
  2. (approx 1 min pause)
  3. Windows Error Reporting and Application Hang for SSMS (I was using locally on the server)
  4. Subsequent warnings from ASP.NET saying it couldn't connect
  5. Several "Access to server validation failed while revalidating the login on the connection." errors
  6. Several "The client was unable to reuse a session with SPID 68, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message."
  7. Login failed for user 'xxxxxxxx'. Only administrators may connect at this time. [CLIENT: 127.0.0.1]

Messages 5/6/7 appear to be resulting from the server becoming unresponsive, not the cause.

I have looked at the SQL Error Logs using SSMS > Management > SQL Server Logs but that just jumps from 1 to 5 above.

Any other things I can analyse?

Best Answer

Only administrators may connect at this time.

Sounds like the UI has issued ALTER DATABASE ... SET RESTRICTED_USER and then hung. This hasn't actually hung SQL Server, it has only prevented non-admin users from connecting. You could get around this by making your application use sa - I'm kidding.

Honestly, stop using the UI for log backups - it's not very good at things like this, and it's hard to pick all the right options for success. BACKUP LOG ... TO DISK is much more reliable, transparent, error-free, and even source controllable...

Related Question