Solved

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

Posted on 2010-09-13
5
612 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 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