Scheduling Integrity Job

We have a production transactional database which is heavily used about 22-23 hours a day. Our Integrity job, when run, takes about 2.5 hours to run and disrupts production. This can't be a unique situation, so I'm wondering how other folks approach thi
s scheduling conflict. Would it make sense to run a series of CHECKTABLE and NEWALLOC commands on several days instead of the single CHECKDB, which is run by the Integrity job?
Although we're trying to get production to change their schedule, that won't be an easy task. Ideally we wanted to run the Integrity job every night, but that doesn't seem feasible. How often should the DBCC be run on the entire database / individual ta
bles?
Thanks for your help.
There is no standard frequency in which to run integrity checks.
Theoretically you never have to run them as they server no function for the
db itself. What they do is to allow you to be informed of any corruption
that may occur as soon as possible. How soon depends on how often you run
it after a problem occurs. So ideally once a night would be great but some
people can only afford to run them once a week, month or longer. One thing
you can do is to restore the DB to another machine and run the DBCC's there.
If it was corrupt on the original server it will be on the one restored as
well. This way you can run it without disturbing the normal operation and it
can be done on a much less powerful machine since you don't care how long it
takes.
Andrew J. Kelly SQL MVP
"slambert007" <anonymous@.discussions.microsoft.com> wrote in message
news:8CF141A0-9C67-4AFA-AA0E-6EF4864FD295@.microsoft.com...
> We have a production transactional database which is heavily used about
22-23 hours a day. Our Integrity job, when run, takes about 2.5 hours to
run and disrupts production. This can't be a unique situation, so I'm
wondering how other folks approach this scheduling conflict. Would it make
sense to run a series of CHECKTABLE and NEWALLOC commands on several days
instead of the single CHECKDB, which is run by the Integrity job?
> Although we're trying to get production to change their schedule, that
won't be an easy task. Ideally we wanted to run the Integrity job every
night, but that doesn't seem feasible. How often should the DBCC be run on
the entire database / individual tables?
>
> Thanks for your help.
|||Use the WITH PHYSICAL_ONLY option (assuming you're running on SQL Server
2000). This will do an allocation check and physical check of every
allocated page in the database without doing the more time consuming logical
checks. It'll be a magnitude faster at least and will still catch most
hardware-caused errors.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#FvMQQcIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> There is no standard frequency in which to run integrity checks.
> Theoretically you never have to run them as they server no function for
the
> db itself. What they do is to allow you to be informed of any corruption
> that may occur as soon as possible. How soon depends on how often you run
> it after a problem occurs. So ideally once a night would be great but
some
> people can only afford to run them once a week, month or longer. One
thing
> you can do is to restore the DB to another machine and run the DBCC's
there.
> If it was corrupt on the original server it will be on the one restored as
> well. This way you can run it without disturbing the normal operation and
it
> can be done on a much less powerful machine since you don't care how long
it
> takes.
> --
> Andrew J. Kelly SQL MVP
>
> "slambert007" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CF141A0-9C67-4AFA-AA0E-6EF4864FD295@.microsoft.com...
> 22-23 hours a day. Our Integrity job, when run, takes about 2.5 hours to
> run and disrupts production. This can't be a unique situation, so I'm
> wondering how other folks approach this scheduling conflict. Would it
make
> sense to run a series of CHECKTABLE and NEWALLOC commands on several days
> instead of the single CHECKDB, which is run by the Integrity job?
> won't be an easy task. Ideally we wanted to run the Integrity job every
> night, but that doesn't seem feasible. How often should the DBCC be run
on
> the entire database / individual tables?
>
|||have a question for paul. if integrity check job is scheduled thru
maintenance plan. how to specify with physical_only option (i assume default
will be doing the logic check as well).
thank you.
steve
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:#ua4lzhIEHA.3060@.TK2MSFTNGP11.phx.gbl...
> Use the WITH PHYSICAL_ONLY option (assuming you're running on SQL Server
> 2000). This will do an allocation check and physical check of every
> allocated page in the database without doing the more time consuming
logical
> checks. It'll be a magnitude faster at least and will still catch most
> hardware-caused errors.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#FvMQQcIEHA.4052@.TK2MSFTNGP12.phx.gbl...
> the
corruption[vbcol=seagreen]
run[vbcol=seagreen]
> some
> thing
> there.
as[vbcol=seagreen]
and[vbcol=seagreen]
> it
long[vbcol=seagreen]
> it
about[vbcol=seagreen]
to[vbcol=seagreen]
> make
days
> on
>
|||Are there any drawbacks from doing a checktable on a set of tables one night and then another checktable on the remaining tables another night vs. the complete checkdb all in one night (other than the entire set of tables not getting checked at once)?
If I backup, restore on another machine and run the checkdb on that second machine will that be an accurate picture of the state of the production database? Andrew mentioned that any corruption in the production database would show up when the database i
s checked on the second server, how do you know that it'll show up on the second system?
Thanks
sean
|||You don't have that flexibility. That's one of the drawbacks to using the
maintenance plans. If you want to do anything other than what the wizard
allows you must schedule your own jobs.
Andrew J. Kelly SQL MVP
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:O9%23JDXAJEHA.2604@.tk2msftngp13.phx.gbl...
> have a question for paul. if integrity check job is scheduled thru
> maintenance plan. how to specify with physical_only option (i assume
default[vbcol=seagreen]
> will be doing the logic check as well).
> thank you.
> steve
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:#ua4lzhIEHA.3060@.TK2MSFTNGP11.phx.gbl...
> logical
> rights.
for[vbcol=seagreen]
> corruption
> run
restored[vbcol=seagreen]
> as
> and
> long
> about
> to
> days
that[vbcol=seagreen]
every[vbcol=seagreen]
run
>
|||The only drawback to doing this separately is that you don't know if you
have a single backup that is fully checked.
As for the corruption in the restore the backup process does not really do
anything to the bits on the page. It just transfers the page bit for bit
and knows little or nothing of what they represent. So essentially any
corruption that DBCC's wall pick up on the original will show up in a
restore as well.
Andrew J. Kelly SQL MVP
"slambert007" <anonymous@.discussions.microsoft.com> wrote in message
news:03896DD9-4F9B-4BD1-8A09-1D2B6E2607F7@.microsoft.com...
> Are there any drawbacks from doing a checktable on a set of tables one
night and then another checktable on the remaining tables another night vs.
the complete checkdb all in one night (other than the entire set of tables
not getting checked at once)?
> If I backup, restore on another machine and run the checkdb on that second
machine will that be an accurate picture of the state of the production
database? Andrew mentioned that any corruption in the production database
would show up when the database is checked on the second server, how do you
know that it'll show up on the second system?
> Thanks
> sean