Re: Check database healthiness on a daily basis?

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 13:11:57 -0400

Bing,

Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check. 

-- 
Andrew J. Kelly  SQL MVP
"bing" <bing@discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@microsoft.com...
> Thanks so much for your instance response, Hari.  Not just for this one.
You have answered a lot of my questions I posted previously.  They are all
very helpful.  I really appreciate your knowledgement and your kindness of
willing to help others.
> I'll try what you suggested.
>
> Bing
>
> "Hari Prasad" wrote:
>
> > Hi,
> >
> > Yes, that will be a better option to check and confirm that your
database is
> > good. DBCC CHECKDB will run for a long time if your database is
> > big. In that case probably you can do this activity weekly once during
non
> > peak hours (weekends).
> >
> > Along with this you can also run UPDATE STATISTICS daily on those tables
> > which have high DML access (Insert/ Update/ Delete).
> >
> > Monthly once check the fragmentation of table using DBCC
> > SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> > THis will remove the fragmentation and increase the performance.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "bing" <bing@discussions.microsoft.com> wrote in message
> > news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@microsoft.com...
> > > How do people usually do to make sure all the databases are in good
> > status?
> > >
> > > We have about 50 databases.  I was intended to write 'dbcc checkdb'
for
> > each one in a T-SQL script and have it run every day.  Is there a better
way
> > to do that?
> > >
> > > Thanks in advance for any advices.
> > >
> > > Bing
> >
> >
> >

Relevant Pages

  • Re: Unable to handle database
    ... 5GB database size might not be a problem. ... DBCC SHOWCONTIG to identify thye tables fragmented and use DBCC DBREINDEX to ... remove Fragmentation. ... Check the usage of Transaction log using DBCC SQLPERF. ...
    (microsoft.public.sqlserver.server)
  • Re: Check database healthiness on a daily basis?
    ... that will be a better option to check and confirm that your database is ... DBCC CHECKDB will run for a long time if your database is ... Monthly once check the fragmentation of table using DBCC ... THis will remove the fragmentation and increase the performance. ...
    (microsoft.public.sqlserver.server)
  • Re: Defragmentation of database file
    ... Check the fragmentation for those tables using the below command ... DBCC DBREINDEX ... and importing data records from the table a good way to defragment the ... >>> Is the only way to export data from the database and then import back ...
    (microsoft.public.sqlserver.server)
  • Re: Check database healthiness on a daily basis?
    ... Thanks so much for your instance response, Hari. ... DBCC CHECKDB will run for a long time if your database is ... > Monthly once check the fragmentation of table using DBCC ... > THis will remove the fragmentation and increase the performance. ...
    (microsoft.public.sqlserver.server)
  • Re: High Extent Scan Fragmentation
    ... This looks like you've got multiple files. ... perfectly - that's why there's no logical scan fragmentation. ... > we check the ext. scan frag before and after the dbcc dbreindex. ... > insert FragTest values ...
    (microsoft.public.sqlserver.server)