May 13, 2015 at 5:50 am
hI.
DBCC CHECKDB Taking more time to complete.is there any way to check .
Thanks,
Arjun
May 13, 2015 at 6:02 am
Not really, you can run the other DBCC commands that make up DBCC CHECKDB independently though, overall it wouldn't save you any time:
https://msdn.microsoft.com/en-us/library/ms176064.aspx
MCITP SQL 2005, MCSA SQL 2012
May 13, 2015 at 6:36 am
You can offload the work to a second server, restore a backup of the production DB and check that. You can break CheckDB down and run its pieces, that's CheckAlloc, CheckCatalog and CheckTable on all tables, do them bit by bit over a couple days.
How big is the DB in question?
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
May 13, 2015 at 7:03 am
If you offload it to another server, you'll still want to run a PHYSICAL_ONLY check on the first server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:07 am
Grant Fritchey (5/13/2015)
If you offload it to another server, you'll still want to run a PHYSICAL_ONLY check on the first server.
Why?
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
May 13, 2015 at 7:10 am
Because you can have physical corruption that doesn't get backed up or restored.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:25 am
Grant Fritchey (5/13/2015)
Because you can have physical corruption that doesn't get backed up or restored.
How?
I keep hearing this advice, but the problem is it doesn't bear out. Backups are a page-by-page extent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.
Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.
and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826
Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?
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
May 13, 2015 at 7:32 am
GilaMonster (5/13/2015)
Grant Fritchey (5/13/2015)
Because you can have physical corruption that doesn't get backed up or restored.How?
I keep hearing this advice, but the problem is it doesn't bear out. Backups are a
page-by-pageextent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.
and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826
Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?
My source for that inclusion is Paul. When I talked about offloading DBCC checks to the compressed backups that one of the RG tools had, he advised me to be sure to also do the physical checks on the original.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:36 am
Grant Fritchey (5/13/2015)
GilaMonster (5/13/2015)
Grant Fritchey (5/13/2015)
Because you can have physical corruption that doesn't get backed up or restored.How?
I keep hearing this advice, but the problem is it doesn't bear out. Backups are a
page-by-pageextent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.
and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826
Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?
My source for that inclusion is Paul. When I talked about offloading DBCC checks to the compressed backups that one of the RG tools had, he advised me to be sure to also do the physical checks on the original.
I dropped him a mail asking for clarification
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
May 13, 2015 at 3:44 pm
It's not possible for there to be corruption in a database that doesn't get included in a backup - so I wouldn't have said that.
If you're offloading DBCC CHECKDB to another server, you don't need to do any DBCC checks on the primary server.
But there may have been some other reason I said to run physical_only on the primary server. I can't think of one off the top of my head though.
Grant - do you have the email thread still?
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
May 14, 2015 at 1:00 am
Paul Randal (5/13/2015)
It's not possible for there to be corruption in a database that doesn't get included in a backup - so I wouldn't have said that.If you're offloading DBCC CHECKDB to another server, you don't need to do any DBCC checks on the primary server.
But there may have been some other reason I said to run physical_only on the primary server. I can't think of one off the top of my head though.
Grant - do you have the email thread still?
Nope. Sorry Paul. It was a long conversation we had at MVP Summit about... 3 years ago (might have been 4, I'm getting old). It was when RG had released the tool that let you mount a backup as if it were a database. I don't recall why you told me to include the physical_only checks, just that you did. Sorry I can't be more helpful on that.
However, great information. I'm very happy to know this and I'll absolutely adjust my advice accordingly. Thanks!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2015 at 2:28 am
Thanks Paul.
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
May 14, 2015 at 8:57 am
I have been "testing" CHECKFILEGROUP Across our database that spans 5 different Filegroups and spreading the loads over 2 days.
BOL mentions that it does not do CHECKCATALOG so I have been running that independently too
Feasible idea guys?
May 14, 2015 at 9:23 am
Absolutely.
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
May 14, 2015 at 12:14 pm
Thank you for confirming
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply