0

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?

  • "I was mid-dialog" -> what dialog? Your application's, SQL Server's UI, something else? – Aaron Bertrand Nov 20 '15 at 15:58
  • I was trying to run a transaction log backup via SSMS. I added a destination path incorrectly. Event Viewer says: (Unnamed tape was dismounted from drive 'D:\invalid\path'. This is an informational message. No user action is required.) It says tape but I was added a hard disk path. It then hung before I could re-add it correctly. I never managed to start the backup. – PunkyGuy Nov 20 '15 at 16:09
  • So the UI hung, or SQL Server itself hung? – Aaron Bertrand Nov 20 '15 at 16:39
  • Both. SQL Server timed out new connections from other clients and couldn't reboot it via Windows services which was the main issue. I don't know if SSMS hung the server or the server hung SSMS. – PunkyGuy Nov 20 '15 at 16:50
  • 1
    Only administrators may connect at this time. <- sounds like the UI issues ALTER DATABASE ... SET RESTRICTED_USER. Maybe stop using the UI for log backups - it's not very good at things like this. BACKUP LOG ... TO DISK is much more reliable, transparent, and error-free... – Aaron Bertrand Nov 20 '15 at 16:59
3

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.