June 12, 2012 at 1:18 am
Does DBCC CHECKDB Needs a Downtime...?
I read through ::::
But it didnt explained that it will require a Downtime..
Though it Slows down the Performance and thats Acceptable but Do it really requires a DOWNTIME for the Database.
And Can Database be online While Running CHECKDB.
Please suggest as i need to run it on my Production DB Tomorrow .
June 12, 2012 at 1:35 am
DBCC CHECKDB doesnt require downtime, what it will do is put the databases into a consistant state by rolling forward or back any transactions so that it can check for concurrency.
June 12, 2012 at 1:38 am
Hi Anthony..thx for responding...I just want to know Does it Lock the Data\Database if I Actually run any Query while Running CHECKDB
June 12, 2012 at 1:45 am
Jai-SQL DBA (6/12/2012)
Hi Anthony..thx for responding...I just want to know Does it Lock the Data\Database if I Actually run any Query while Running CHECKDB
Depends!!!!
DBCC CHECKDB creates an internal snapshot of the database, leaving the actual database free to the users, but if DBCC CHECKDB cannot create the snapshot, it will place the nessesary locks on the database to perform the consistancy checks
Internal Database Snapshot
DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed. For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL). If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks.
DBCC CHECKDB fails when run against master if an internal database snapshot cannot be created.
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
June 12, 2012 at 2:16 am
Jai-SQL DBA (6/12/2012)
Does DBCC CHECKDB Needs a Downtime...?I read through ::::
But it didnt explained that it will require a Downtime..
Though it Slows down the Performance and thats Acceptable but Do it really requires a DOWNTIME for the Database.
And Can Database be online While Running CHECKDB.
Please suggest as i need to run it on my Production DB Tomorrow .
Running DBCC CHECKDB for only checking the db doesn't need downtime.
Repairing the database with DBCC CHECKDB needs downtime (db should be in single user mode)
If you are not familiar with this, don't try directly on production server.
First run this on a non-production server.
June 12, 2012 at 6:22 am
Because CHECKDB will place additional load on the system and can affect transactions, while you do not need down time, I would schedule it during off-peak periods. Even better, if you backup your database, restore it to somewhere else, you can do the full DBCC on that backup and only have to run a PHYSICAL_ONLY check on the main database. I'd still do that during off-hours, but it's less intrusive than the full logical and physical checks.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply