October 23, 2007 at 8:49 am
Hello,
The database integrity checks in the SQL 2000 maintenance plans are resource intensive, but they also provide a quality check. Is it OK to run them once per week, or do they need to be run more frequently?
I'm asking because in order to alleviate what appears to be a high load on our database, I found that we had 6-7 database integrity checks running 4 times per week. (28 times total.) I changed the schedules so they each run once per week (7 times total).
Is this OK? Or is it not often enough? I thought once per week is OK, but what other factors should be weighed?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 23, 2007 at 1:44 pm
In theory as frequently as possible so you can detect the error early (if any). In reality, do you have window to run it every day? how big is the database? How long does it take?
It should run before the backup. Once any error found, should have an alert send out as a notification.
Do not wipe out the previous good backups.
October 23, 2007 at 1:52 pm
Vivien Xing (10/23/2007)
In theory as frequently as possible so you can detect the error early (if any). In reality, do you have window to run it every day? how big is the database? How long does it take?It should run before the backup. Once any error found, should have an alert send out as a notification.
Do not wipe out the previous good backups.
The databases vary, but one is over 15 GB.
I'd thought that if we did not have to run them 4 times per week, it would reduce the load on the server over the course of a week, but if it is important to run them more often, I will need to go back to running the database integrity checks 4 times per week. I just don't want to run anything any more than I need to. Although I did read a web page that made the interesting suggestion of running the database integrity checks on a copy of production restored onto a test server. Of course, copying the file from the production server also takes resources, but I wonder if that is better than running the check directly on the production server.
What action can be taken if an error is found by this integrity check? Do you have a reference that explains the options and what can be done in detail?
Thanks!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 23, 2007 at 2:23 pm
Restoring the backup to another server to do the integrity check is an option. This also helps to verify your backup. The check is against the restored database not the current production one.
You may refer to Books online (SQL2K/2K5) - Database Integrity Check section. It is part of database maintenance (DBA) tasks.
If error detected, the error will be recorded in errorlog.
Error varies. Google "integrity check error" will give you some information.
It is supposed to be set up depends on your own environment.
October 23, 2007 at 9:03 pm
Checkout a series of articles I wrote last year and this year when I was still in MS about running CHECKDB - http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
There's also a recording of a session I presented at TechEd in June this year on this subject - http://www.microsoft.com/emea/spotlight/Paul_Randal_Secrets_to_Fast_Detection_and_Recovery_from_Database_Corruptions.aspx
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 24, 2007 at 7:26 am
Paul Randal (10/23/2007)
Checkout a series of articles I wrote last year and this year when I was still in MS about running CHECKDB - http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspxThere's also a recording of a session I presented at TechEd in June this year on this subject - http://www.microsoft.com/emea/spotlight/Paul_Randal_Secrets_to_Fast_Detection_and_Recovery_from_Database_Corruptions.aspx
Thanks, Paul and others, for the pointers! I'm sure this will take a while to read through and digest, so I will get started on it right away.
Gratefully,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 24, 2007 at 7:58 am
Do consider about the system defined SP's on the maintenance plans....
October 24, 2007 at 1:43 pm
VAIYDEYANATHAN.V.S (10/24/2007)
Do consider about the system defined SP's on the maintenance plans....
Sorry, I don't quite follow you. Could you explain more about the considerations for the system-defined SP's and maintenance plans?
Many thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 24, 2007 at 1:47 pm
I almost replied to his comment this morning - seems there's been a rash of him posting redundant/irrelevant info on threads.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply