Solved

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

Posted on 2010-09-13
5
592 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 27
Whats wrong in this query - Select * from tableA,tableA 11 32
Return 0 on SQL count 24 30
Help in Bulk Insert 9 35
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

823 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