Solved

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

Posted on 2010-09-13
5
583 Views
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!!
0
Comment
Question by:tim90g
5 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 200 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 ?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 33669574
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 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 ?
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 100 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.

//Marten
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 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.

<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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

864 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now