• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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

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!!
0
tim90g
Asked:
tim90g
5 Solutions
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
David ToddSenior DBACommented:
Hi,

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.

Regards
  David
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
Marten RuneCommented:
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.

//Marten
0
 
Anthony PerkinsCommented:
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.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now