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?

Improve this question
6
  • "I was mid-dialog" -> what dialog? Your application's, SQL Server's UI, something else?
    – Aaron Bertrand
    Commented Nov 20, 2015 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.
    – Gavin Ward
    Commented Nov 20, 2015 at 16:09
  • So the UI hung, or SQL Server itself hung?
    – Aaron Bertrand
    Commented Nov 20, 2015 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.
    – Gavin Ward
    Commented Nov 20, 2015 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
    Commented Nov 20, 2015 at 16:59

1 Answer 1

Reset to default
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...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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