September 25, 2012 at 6:23 am
I tried running: DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS;
And got the errors:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 9001, Level 21, State 1, Line 1
The log for database 'DatabaseName' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 9001, Level 21, State 1, Line 1
The log for database 'DatabaseName' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
The good news is that this database is not in active use anymore. But does this indicate I may have an issue in my storage system? I'd like to nail this down before a database I care about goes bad. (Yes, we have full backup and transaction log backups)
What's the next steps?
Thanks,
Rob
September 25, 2012 at 6:30 am
Are there any related messages in the event log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2012 at 6:36 am
GilaMonster (9/25/2012)
Are there any related messages in the event log?
Looking in Event Viewer on the server under Administrative Events, I see "The log for database 'DatabaseName' is not available. Check the event log for related error messages. Resolve any errors and restart the database" listed a number of times.
I don't see any other errors (like in the Windows Logs\System) that apply.
September 25, 2012 at 7:13 am
Any messages about drives being unavailable?
Anything useful in the SQL error log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2012 at 7:31 am
The SQL logs just have [font="Courier New"]Error. 9001 Severity: 21. State: 1.[/font] - which I think is related to the bad log. And [font="Courier New"]The log for database 'DatabaseName' is not available ... [/font]repeated over and over.
There is a log that says [font="Courier New"]SQL Server has encountered 111 occurrences(s) of I/O requests taking longer than 15 seconds to complete on d:\Data\tempdb\mdf.[/font]
But that's it -- nothing else.
DBCC CHECKDB shows fine for other databases on that server.
September 25, 2012 at 10:21 am
Nothing drive-related in the Windows event logs (application or system)
Does that DB's log share a drive with any other?
Are you running enterprise edition?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2012 at 11:50 am
GilaMonster (9/25/2012)
Nothing drive-related in the Windows event logs (application or system)Does that DB's log share a drive with any other?
Are you running enterprise edition?
I just double checked the application and system Windows logs and there's nothing drive related there.
All of the .mdf and .ldf files for that server are on the same D:\ drive. There are aprox 20 other databases on that server.
No, SQL Server 2008 R2 Standard edition on that server.
September 25, 2012 at 1:02 pm
Odd. I've seen this before and couldn't figure it out then either.
Does this problem persist across restarts of SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2012 at 1:33 pm
Any chance that you're running it on VMWare? I've seen DBCC CHECKDB errors (can't remember the exact message) and "I/O requests taking longer that 15 seconds" on one of our development servers.
September 25, 2012 at 1:54 pm
Nils,
No, it is not on a virtual server.
Gail,
This is a production server; the scheduled reboot is this upcoming Sunday. Since this db isn't actively used, I think the plan is to see what happens after restarting SQL Server.
Thanks,
Rob
October 1, 2012 at 6:39 am
After last night's reboot of the server, the problem has went away and the DBCC comes back fine.
Rob
October 1, 2012 at 9:27 am
robert.gerald.taylor (10/1/2012)
After last night's reboot of the server, the problem has went away and the DBCC comes back fine.Rob
Not saying this is the answer, but I have seen similar issues when access to a drive was interrupted. Until SQL Server is recycled - even if that drive appears to be available SQL Server doesn't 'see' it anymore.
After cycling SQL Server - it checks again, sees the drive available and everything is back to normal.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2012 at 9:31 am
It can also happen if the database snapshot that checkDB creates isn't dropped properly (though don't know how that happens). It's still there until SQL restarts, hence further checkDB fails.
I think there's something on Paul's blog on this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply