June 25, 2012 at 10:37 am
Will a job with the following fail if corruption exists? Or will the job succeed?
DBCC CHECKDB(N'master') WITH NO_INFOMSGS
I'm suddenly feeling a bit exposed since I am not sure if I would get notified if corruption exists.
June 25, 2012 at 10:40 am
Chrissy321 (6/25/2012)
Will a job with the following fail if corruption exists? Or will the job succeed?DBCC CHECKDB(N'master') WITH NO_INFOMSGS
The job would fail and report at least some of the errors it encountered. To see all errors you need to use "WITH ALL_ERRORMSGS". That can be combined with NO_INFOMSGS.
June 25, 2012 at 10:40 am
The job will fail and @@ERROR will be set to the last severity 16 (or higher) message that is output.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 25, 2012 at 10:43 am
Be careful though because SQL Agent will only capture the first X bytes of the output (unless that changed in recent versions). You may want to go the route of using the undocumented WITH TABLERESULTS and storing the output in a table for later perusal. See here[/url] for an example.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 25, 2012 at 10:50 am
Thanks Paul I will definitely explore that option. I have a monitoring system for failed jobs so it sounds like I would at least be notied that something is amiss. Seems like a good example of maintenance plans being not quite as robust as built or borrowed maintenance scripting.
June 25, 2012 at 10:59 am
Paul Randal (6/25/2012)
Be careful though because SQL Agent will only capture the first X bytes of the output (unless that changed in recent versions). You may want to go the route of using the undocumented WITH TABLERESULTS and storing the output in a table for later perusal. See here[/url] for an example.
We write the output to a file and that has the entire output. Use whichever works better for you.
June 25, 2012 at 1:56 pm
Fantastic article, thank you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 27, 2012 at 6:10 am
Thanks for this article , all this while in my DBA career was wondering why REPAIR_REBUILD did not work most times on cluster index chain broken.
But observed in one of the client places REPAIR_ALLOW_DATA_LOSS did work for corrupt system tables without data loss but yes backups are best bet's as you said.
Cheers
Satish
Cheer Satish 🙂
June 27, 2012 at 6:14 am
It entirely depends on which system tables are corrupt, and what the corruption is. Basically, it's a crap-shoot whether it'll work or make things worse because the system tables are quite twitchy when direct physical changes are made to them.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply