Up until this morning I had no problems accessing our database.

This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the instance quite happily, but cannot access the database itself. This goes for both my C# application and SQL Server Management Studio. In SSMS I get "The database EMS_Main is not accessible. (Object Explorer)" if I try and expand the icon for EMS_Main.

Seems to make no difference whether I'm logging in via LAN, VPN, or even remote desktop on the server itself.

The logs don't show much out of the ordinary other than last night around 22:05 I got the following:

Four "IO is frozen on database" messages for master, EMS_Main, msdb, and model.

Four "IO was resumed on database" messages for the above.

Then (with the same timestamp):

"A timeout occurred while waiting for buffer to latch -- type 2"

"Error: 3041, Severity: 16, State: 1"

"BACKUP failed to complete the command BACKUP DATABASE EMS_Main. Check the backup application log for detailed messages."

Then nothing until this morning when I got:

"Error: 18456, Severity: 14, State: 38"

"Login failed for user 'sa'. Reason: Failed to open the explicitly specified database."

Any idea what is going on/how I can rectify this?

It smells like a permissions issue to me; I've tried creating a new login in SSMS so I can do a "GRANT VIEW ANY DATABASE TO sa" (a solution suggested on another website), but creating a new user hangs in SSMS (clicking the 'User Mapping' page also hangs it as well, not sure if that's related) - the 'Progress' pane just shows 'Executing' with the progress indicator going round indefinitely.

If I double click on the sa login, I can see (after a bit of a wait) that it has permission to connect to the database engine, the login is enabled, the box for 'Login is locked out' is not checked (but is greyed out), it only has 'public' and 'sysadmin' checked for Server Roles, default database is set to 'master'.

The server is running MS Windows Server 2003 for Small Business Server SP2. We're on SQL Server 2008 R2.

I am new to being a dba (got thrown into it by my employer) - my background is more in coding business apps, so go gentle on me! I don't necessarily know all the dba jargon too well ;)

Thanks in advance.

  • No, is not a permission issue. Your database is offline. Investigate why and very likely is time to implement your previously tested restore strategy. Feb 18, 2013 at 12:56
  • 'Bring Online' is greyed out...I have no idea where to start 'investigating why' (like I said, I'm new to all this)...some help in that department would be appreciated :)
    – Jez Clark
    Feb 18, 2013 at 13:04
  • 2
    Contact your administrator Feb 18, 2013 at 13:12
  • 1
    I'd love to, but I AM the administrator (much to my chagrin). To say this place is a farce when it comes to IT is an understatement...but unfortunately it is the hand I am dealt. Certain people in this building do not seem to understand that C# Coder != DBA...
    – Jez Clark
    Feb 18, 2013 at 13:15
  • Do you have backups? How old are they? What amount of data loss is acceptable for your business? Have you ever restored one of these backups? Feb 18, 2013 at 13:23

1 Answer 1


Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it happened.

And sort myself a disaster recovery plan ;)

  • 1
    Make sure you test your DR plan. Leave clear step-b-step procedure what to do in case of disaster, so that any reasonable competent person can do it. Feb 18, 2013 at 18:38

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.