March 4, 2011 at 12:19 am
Plz. guide for dbcc checkdb on larger data size.
Is it a good practice to use it?
How to use it in various envrionment.
Plz. Guide to perform the activity
March 4, 2011 at 1:12 am
It's absolutely essential to run it. If you don't and you get corruption there's no guarantee that you'll find it while still repairable.
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
March 4, 2011 at 1:30 am
Thanks for the reply:-)
March 6, 2011 at 5:06 am
One alternative is to use a third party product to enable you to move the VLDB database to a different location for consistency checks. There are a number of ways it can be done from low-level snapshot schemes in the disk storage arena to software like Red Gate's Virtual Restore which can run a DBCC against a backup.
Full disclosure, I work for Red Gate.
"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
March 6, 2011 at 9:19 pm
Hi,
Thanks for your reply. Can you plz. give the step by step guide.
March 7, 2011 at 1:10 am
vinod.saraswat (3/6/2011)
Can you plz. give the step by step guide.
For what? Between Paul's blog and Grant's reply we've given you several options, none very complex.
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
March 7, 2011 at 6:15 am
vinod.saraswat (3/6/2011)
Hi,Thanks for your reply. Can you plz. give the step by step guide.
You absolutely must run DBCC on databases if you care at all about the data. How it's done is very well documented in Books Online. If you have a very large database (we're talking at least several hundred gigabytes or more) then you need to follow Paul's advice as Gail linked or possible try some of the stuff that I suggested. But the basic DBCC check on a large database is the same as on a small database, DBCC CHECKDB('MyDatabase'). Again, look at the Books Online, SQL Server's documentation, for all the details.
"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
March 7, 2011 at 9:11 pm
Hi,
Thanks alot for your guide now got the idea.:-)
March 9, 2011 at 3:01 pm
My employer has 8 application databases on 2 servers.
Between 1:00 a.m. and 4:00 a.m., MDT, our servers tend to (but not always) hit their lowest usage point. During this period, a DBCC CHECKDB job is executed against the 6 smallest application databases and all of the system databases. This command typically takes less than 3 minutes to execute and does no harm to the servers.
The other two of our databases are extremely large. Since we're a 24x7 shop and much of our clientelle is international, our servers never really have a truly slack period.
I've attempted to execute a DBCC CHECKDB against our 2 largest databases with disastrous results. Both servers, each supporting one of the uber large databases, began to fail to meet contractual performance obligations. I simply had to stop the commands from executing.
This always concerned me because we could never be sure of the integrity of our largest production databases, at least on a daily basis.
I came up with a way to at least check for page checksum errors. A job executes every minute on both of our servers and checks for checksum errors. If we were to find one, all administrators would immediately receive an alert and we would take the problem database(s) offline to investigate and resolve the problem.
The script I use is this simple one for the "Suspect Pages" job:
USE msdb
DECLARE @count INT;
SET @count = (SELECT COUNT(*) FROM suspect_pages);
IF @count <> 0 RAISERROR( 'Suspect page(s) identified in a database', 19, 0 ) WITH LOG
I've done some experimenting with DBCC CHECKDB. I know of no action a DBA can take that will generate more I/O than this command. We recently purchased and then built a new database server that we're soon to deploy. I took a SAN snapshot of our largest database, which I have never finished executing a DBCC CHECKDB command against, and exposed it to a LUN assigned to our new server. Then, using SSMS, I executed a DBCC CHECKDB command on that database. The results were interesting.
The command execution completed with no errors found. Our new server, a 64 bit wonder, with 8 CPU cores, 16 if you count hyperthreading, and 96 GB of RAM, responded to the command by taking the all of the CPU cores to 95-100% utilization and holding them there, and creating a sustained rate of 60,000+ I/O's per second for over an hour on our SAN. It used all of the RAM I would allow, all but 2 GB of free RAM that I held in reserve.
DBCC CHECKDB is a very resource intensive command that runs without any governor. Therefore, it has the potential to consume all of the resources on your server (CPU, RAM, and Disk I/O bandwidth). If you must execute it during production hours, do so with care. You may have to place your databases on another, unused or mostly unused server, to execute the command.
LC
March 9, 2011 at 5:02 pm
One thing you should always consider with DBCC CHECKDB is running it on another server with a restored database.
March 9, 2011 at 10:56 pm
crainlee2 (3/9/2011)
I've attempted to execute a DBCC CHECKDB against our 2 largest databases with disastrous results. Both servers, each supporting one of the uber large databases, began to fail to meet contractual performance obligations. I simply had to stop the commands from executing.This always concerned me because we could never be sure of the integrity of our largest production databases, at least on a daily basis.
Reposted from above:
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 9, 2011 at 8:26 am
Steve Jones - SSC Editor (3/9/2011)
One thing you should always consider with DBCC CHECKDB is running it on another server with a restored database.
Agree, This will guarantee a base-line for futures pages-reconstructions if necessary.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply