February 3, 2009 at 9:44 pm
Hi,
Does The DBCC CHECKDB statement raise errors in SQL Server (error log) when the DBCC CHECKDB statement detect a consistency error in the database? Can i view these error in sql error log?
I have to schedule dbcc checkdb statement on weekly basisb to find out consistency error in database. I want to know when dbcc check stament detect consistency error in the database, does it generate log in sql error log?
Please help me.
February 3, 2009 at 10:01 pm
From BOL:
Understanding DBCC Error Messages
After the DBCC CHECKDB command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates success and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates that the command was terminated, a state value, and the amount of time the command ran. The following table lists and describes the state values that can be included in the message.
Error Reporting
A dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever DBCC CHECKDB detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality.
The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. Access is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.
So, yes you have an entry in the SQL Server Error Log that you can start your investigation from. Paul Randal has some great posts on DBCC CheckDB on his blog here.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 4, 2009 at 9:44 pm
Thanks for your reply.
So what I have to refer dump file which is created in the SQL server Log directory or sql error log from enterprise manager/management studio?
After the dbcc checkdb command finished, a message is written to SQl server error log. If the dbcc command successfully executes (consistency error is not found), the message indicates success and the amount of time that command ran.
Similarly when dbcc checkdb command finished, If the dbcc command successfully executes (consistency error is found), is message written to the SQL server error log?
Please help me.
February 5, 2009 at 6:11 am
hemant789 (2/4/2009)
Thanks for your reply.So what I have to refer dump file which is created in the SQL server Log directory or sql error log from enterprise manager/management studio?
After the dbcc checkdb command finished, a message is written to SQl server error log. If the dbcc command successfully executes (consistency error is not found), the message indicates success and the amount of time that command ran.
Similarly when dbcc checkdb command finished, If the dbcc command successfully executes (consistency error is found), is message written to the SQL server error log?
Please help me.
You would have to look for the dump file in the log directory as directed in BOL. Yes, a brief error is written to the SQL Server Error Log as well. You will need to get the detail of the errors from the file on disk.
There is other ways to get the information using TABLERESULTS option but I'm not sure if that is supported in 2005 even though it works. A link for that example is HERE.
You could also put something like the following in place to read through the error log and send the output as an email.
CREATE TABLE #Errors (LogDate datetime, ProcessInfo varchar(32), MsgText varchar(max))
INSERT #Errors EXEC xp_readerrorlog
SELECT * FROM #Errors WHERE MsgText LIKE '%DBCC CHECKDB%'
ORDER BY LogDate
DROP TABLE #Errors
Please note that if you do a log switch, which you periodically should, this could miss some of the output so whatever solution you put in place you need to make it robust and test to verify that first of all you are doing CHECKDB on all your databases and secondly that you have a process in place for reviewing the output.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 5:56 am
Thanks for your reply.
Your reply was very helpful for me to understand the concept of dbcc checkdb command.
Now i want to corrupt the table in database to test dbcc checkdb statement. Do you know how to corrupt table? Please suggest me.
If checkdb found consistency error in database then what is severity level of error in sql error log?
February 6, 2009 at 8:08 am
Check out Paul Randal's blog about corrupting a database....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply