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.
Best Answer
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 ;)