Can I run a SQL 2000 database integrity check and database backup without locking up the database?

Posted on 2010-09-13
Medium Priority
Last Modified: 2012-05-10
I'm thinking the answer is no, but I need some confirmation.  :)  Is it possible to run a SQL 2000 database integrity check, followed by a database backup and still have the database accessible?  Right now I believe the database is getting locked up during both processes.  We are now running a third production shift during the time these are scheduled to run.  I know very little about SQL so I thought I would just verify what I suspect.  Thanks so much to any help provided!!
Question by:tim90g
LVL 52

Accepted Solution

Mark Wills earned 800 total points
ID: 33669443
Yep, backup and dbcc should not exclusively lock the database.

Well, it can depend on the dbcc commands, but in 2000 they moved to the "lighter" intent shared locks which means the database can still be accessed concurrently by normal users.

When talking about backup - you do mean the internal SQL Backup command  - because that should also be fine if running online with other users.

Now, it could be your routines. It is quite possible to set the database to exclusive or single user. So, maybe it is the procedures being used ?

Do you know how the backup routines are being run ? Is it via the maintenance wizard, or some script, or even sqlmaint command line utility ?
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 total points
ID: 33669574

The backup should be fine! But depending on how you have the disk structured if there may be a disk contention. Pays to wait till an off-peak time.

I'd guess that likewise with dbcc checkdb.

Both of these are fairly heavy - reading the entire database, which may mean that cached pages are dumped from the cache, causing a slower response to normal workloads.

LVL 52

Assisted Solution

by:Mark Wills
Mark Wills earned 800 total points
ID: 33669645
dbcc checkdb can apply locks, depending on the options... You can specify TABLOCK, and if it has to do something then the schema locks become an exclusive table lock... So, it really does depend on what is really / actually running.

And agree with David, it can sometimes add a such a workload whereby a timeout is invoked (or not invoked and waits, looking like a lock out). So, it might also depend on the various time outs and even client side isolation levels in connecting to the database.

For those reasons, always best to run in off-peak times, but with a third shift being added, it is sounding like off-peak is a vanishing commodity :)

But dont have to run everything everytime either. Really need to understand more about your scripts / commands being used and might be able to suggest alternate strategies. For example, what kind of recovery model is being used ? Do you do transaction log backups ?
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 400 total points
ID: 33670154
If you have the time. And resources, you could do the backup, do a restore on another system (sql), and there perform the integrity check. This would ease the load on the integrity check from the production database.

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 33675031
And we can only hope you did not check the box that reads "Attempt to repair any minor problems" or it would explain the problem you are having.

I sometimes think that label should have read: Attempt to repair any minor problems and instead cause some major ones. :)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question