January 16, 2009 at 6:32 am
When attempting to run DBCC CHECKDB on one of my databases, I get the following:
From the SQL Server Log at the beginning of the CHECKDB I get two "informational" messages
1. Message
4 transactions rolled forward in database 'DATABASE' (9). This is an informational message only. No user action is required.
2. Message
1 transactions rolled back in database 'DATABASE' (9). This is an informational message only. No user action is required.
Then I get the following in the detailed text file from the job:
Msg 926, Sev 21, State 6, Line 1 : Database 'DATABASE' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. [SQLSTATE HY000]
There is no additional information in the errorlog and the database is not in a SUSPECT status.
The database is 565 GB with a 22 GB log file.
Has anyone run into this situation?
January 16, 2009 at 8:04 am
The transaction rolled forward/back should not be any issue. That is simply the dbcc command getting the database to stable state to perform its actions (or so is my understanding).
You don't receive any other messages from the dbcc command? Usually you get a message with an error of why it was put as suspect. Any messages in the windows event viewer logs?
January 16, 2009 at 8:36 am
I get this message in both event viewer and the SQL Log:
Event Type:Error
Event Source:MSSQL$*****
Event Category:(2)
Event ID:17053
Date:1/16/2009
Time:1:30:28 AM
User:N/A
Computer:
Description:
P:\DG1\MP5\P____S_LABCORP_20080901_IN_D1.ndf:MSSQL_DBCC9: Operating system error 112(There is not enough space on the disk.) encountered.
If the problem was a space issue I would expect this "not enough space" message in the return from the job.
Instead I get the error about the database being SUSPECT which it is not.
January 16, 2009 at 8:43 am
DBCC CHECKDB uses the tempdb to store work tables whilst processing.
Is it possible that the drive hosting your tempDB could have run out of space when you ran DBCC CHECKDB?
You can use the ESTIMATEONLY argument in order to calculate how much space will be required.
Excerpt from Books Online:
ESTIMATEONLY
Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.
January 16, 2009 at 8:57 am
dmc (1/16/2009)
The transaction rolled forward/back should not be any issue. That is simply the dbcc command getting the database to stable state to perform its actions (or so is my understanding).
Yup. The rollback/rollforward is only in the context of the database snapshot that CheckDB creates to read from. It's not actually rolling anything in the database itself back..
Fred, do you get the same errors if you try running checkDB from a query window? If so, try running Checkdb with the tablock option.
I'm thinking that it's the hidden snapshot that's running out of space. If a database snapshot runs out of space, it's marked as suspect, so that could explain the suspect database message.
How big is that ndf that the event log message refers to, and how much space on the P drive? Is the database fairly active at the time you run checkdb?
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
January 16, 2009 at 9:11 am
Gail,
Much thanks for the info.
The MP5 drive is 134 GB of which 5.1 GB is free space. The ndf file referred to in the log is 22GB.
January 16, 2009 at 9:20 am
That's probably it. Are there other data files from the same db on that disk?
The 'internal' snapshot that checkDB uses is created on the same drives as the data files. The insuffecient space error clearly shows that it's the snapshot that's running out of space.
P:\DG1\MP5\P____S_LABCORP_20080901_IN_D1.ndf:MSSQL_DBCC9: Operating system error 112
Is the server relatively busy when you run checkdb?
I can see that there are active transactions (from the rollback/rollforward messages) If those are large data changes, then the old versions have to go into the snapshot before checkDB can start. If the space required is more than the space available, the snapshot will go suspect and checkDB will fail.
Any chance you can free up some space on that disk?
Otherwise you can run checkDB with the tablock option, but then it will take locks to ensure it sees consistent data. If there's other activity, it's going to get blocked.
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
January 16, 2009 at 9:46 am
Gail,
Thanks a lot for all the information. There is no way to make any additional room on this disk as things are now configured. We will have to have the storage team add another Mount Point and then move some files to it to make the necessary space.
Thanks for clearing up the mystery of the SUSPECT message.
Fred
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply