checktable and alloc  
Author Message
vikasingh





PostPosted: Thu Aug 23 18:08:15 CDT 2007 Top

SQL Server Developer >> checktable and alloc

Didn't get a response yesterday, so I'll try again:

I'm looking for strategies to handle a 1.5 terabyte database and I think I've
got a way to do it. What's killing us is that when we do a CHECKDB on this
database, it hammers the server for 9 hours straight. (SS 2000 SP4)

Well, I read the following statement in BOL: "DBCC CHECKDB performs the
same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE
statement were executed for each table in the database."

Now here's my "brilliant" idea: run CHECKTABLE and CHECKALLOC on all the
tables in the database instead. That way we could run some tables on one
night, some more on the next night and just have a job that runs an hour or
two each night instead of 9 hours!

Now if we do this are we really covered? Or is there a better way yet?

I would really appreciate any advice as this is a large database and I want
to do things the right way.

SQL Server105  
 
 
Andrew





PostPosted: Thu Aug 23 18:08:15 CDT 2007 Top

SQL Server Developer >> checktable and alloc In a nut shell that is fine with a few comments. Checkalloc is not a table
by table command, it checks for the entire db. But run by itself it should
take much less time than a CheckDB. The CheckTable can be run on a table by
table basis with some done each night. But there is one other you should
work in as well and that is CheckCatalog. Again this is a DB level check but
should not be overlooked. This is included in SQL 2005 when you run a
CheckDB but was never included in 2000 and often overlooked. Another option
is to run DBCC CHECKDB or DBCC CHECKTABLE with the PHYSICAL_ONLY option.
This does not do a full check but will finish faster. So you can use this
normally and once in a while you might want to run the full deal. If you
have multiple Filegroups (which for a TB database I would hope so) you can
run DBCC CHECKFILEGROUP against the individual FG's, each on a different
night. And there is also the option to run it on a restored copy of the db
as well.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors




> Didn't get a response yesterday, so I'll try again:
>
> I'm looking for strategies to handle a 1.5 terabyte database and I think
> I've
> got a way to do it. What's killing us is that when we do a CHECKDB on
> this
> database, it hammers the server for 9 hours straight. (SS 2000 SP4)
>
> Well, I read the following statement in BOL: "DBCC CHECKDB performs the
> same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE
> statement were executed for each table in the database."
>
> Now here's my "brilliant" idea: run CHECKTABLE and CHECKALLOC on all the
> tables in the database instead. That way we could run some tables on one
> night, some more on the next night and just have a job that runs an hour
> or
> two each night instead of 9 hours!
>
> Now if we do this are we really covered? Or is there a better way yet?
>
> I would really appreciate any advice as this is a large database and I
> want
> to do things the right way.
>

 
 
CLM





PostPosted: Fri Aug 24 11:32:04 CDT 2007 Top

SQL Server Developer >> checktable and alloc Thank you for the response! That is exactly the kind of information I was
looking for. Much obliged...



> In a nut shell that is fine with a few comments. Checkalloc is not a table
> by table command, it checks for the entire db. But run by itself it should
> take much less time than a CheckDB. The CheckTable can be run on a table by
> table basis with some done each night. But there is one other you should
> work in as well and that is CheckCatalog. Again this is a DB level check but
> should not be overlooked. This is included in SQL 2005 when you run a
> CheckDB but was never included in 2000 and often overlooked. Another option
> is to run DBCC CHECKDB or DBCC CHECKTABLE with the PHYSICAL_ONLY option.
> This does not do a full check but will finish faster. So you can use this
> normally and once in a while you might want to run the full deal. If you
> have multiple Filegroups (which for a TB database I would hope so) you can
> run DBCC CHECKFILEGROUP against the individual FG's, each on a different
> night. And there is also the option to run it on a restored copy of the db
> as well.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>


> > Didn't get a response yesterday, so I'll try again:
> >
> > I'm looking for strategies to handle a 1.5 terabyte database and I think
> > I've
> > got a way to do it. What's killing us is that when we do a CHECKDB on
> > this
> > database, it hammers the server for 9 hours straight. (SS 2000 SP4)
> >
> > Well, I read the following statement in BOL: "DBCC CHECKDB performs the
> > same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE
> > statement were executed for each table in the database."
> >
> > Now here's my "brilliant" idea: run CHECKTABLE and CHECKALLOC on all the
> > tables in the database instead. That way we could run some tables on one
> > night, some more on the next night and just have a job that runs an hour
> > or
> > two each night instead of 9 hours!
> >
> > Now if we do this are we really covered? Or is there a better way yet?
> >
> > I would really appreciate any advice as this is a large database and I
> > want
> > to do things the right way.
> >
>
>