Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of preventing any inserts, deletes, updates, etc. Just thinking in terms of scheduling my maintenance plans, etc.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Jan 21 '12 at 23:27
This question came from our site for professional and enthusiast programmers.
Briefly looking at the script you have referenced shows that it is making use of DBCC CHECKDB in order to perform the database integrity checks.
This will not lock the database to modifications. CHECKDB works on a consistent view of your database by creating a Database Snapshot.
Locks are of course acquired throughout the integrity checking process but these will not be long term blocking unless you explicity request them to be, for example by using the TABLOCK option for CHECKDB (which can be requested via a parameter in Ola's script).
The best practice advice would be for you to perform the desired maintenance operation in a controlled test environment first. This will give you an idea also of at least how long you can expect CHECKDB to take in Production.
Additional Thoughts: Dependant on your requirements, say for example you only manage a few databases, deploying an automated solution such as Ola's may in fact be overkill when a simple SQL Server Agent Job that calls the CHECKDB command may suffice. If you just need to check a single database then see my blog post Simplify Your DBCC CHECKDB Output for guidance.