July 1, 2008 at 10:26 am
I just noticed this as I don't normally look at the full DBCC file, I use a filter and just look at the error counts: if it's > 0, I open the full dbcc result file.
Anyway, here's how I do my DBCCs:
use master
go
exec sp_MSforeachdb @command1="print getdate() print '? checkdb' DBCC CHECKDB(?)"
go
exec sp_MSforeachdb @command1="print getdate() print '? checkalloc' DBCC CHECKALLOC(?)"
go
exec sp_MSforeachdb @command1="print getdate() print '? checkcatalog' DBCC CHECKCATALOG(?) print ''"
go
exec sp_MSforeachtable @command1="print getdate() print '? Update Statistics' UPDATE STATISTICS ? WITH FULLSCAN"
go
exec sp_MSforeachtable @command1="print getdate() print '? DBReindex' dbcc dbreindex('?', '', 90)"
go
On this one particular server, we have one production database plus a copy as a test DB, plus the normal mix of system databases. This is a canned system, so no new development work. As far as I know, or at least I haven't been notified otherwise, the database has not been upgraded recently.
As part of my DBCC code, I keep the full output for the last ten runs in a rolling list. My oldest runs are all 445K in size. My three newest are 819-822K in size. According to my backup logs, the number of pages backed up has increased an average of 0.3% per day for the last week or so, so it's not like the database has had a sudden dramatic increase in size.
If the database is not changing, and the DBCCs are finding no errors, why should the result file grow?
Any ideas?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 1, 2008 at 11:14 am
I'd think that something must have changed. If not, the DBCC shouldn't have doubled.
One thing to note, that checkdb includes a checkcatalog and checktable. Paul Randal has a series on this (http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/09/623789.aspx)
I would only think that new tables, or possible errors, would cause the size to double. Is there anything that could have happened here? Any SS2K patches that might have changed the logging level of checkdb to be more verbose?
Can you be sure that all stages were running before?
July 1, 2008 at 11:32 am
Heh, found the problem! The test database wasn't there previously, someone created it on 6/26, which is when the DBCC result file doubled. I had assumed that it had always been there as it's pretty normal in our environment to have such a database.
No patches or updates should have been installed without my knowledge or permissions, but this particular server is in another facility, and someone else also has admin privileges on that box, so I don't have exclusive control there.
I'll check into that checkdb/checkcatalog, I originally wrote my DBCC methodology in 6.5 days and must have missed that duplication. Thanks, Steve!
:w00t:
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 1, 2008 at 3:17 pm
Steve Jones - Editor (7/1/2008)
...One thing to note, that checkdb includes a checkcatalog and checktable. Paul Randal has a series on this http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/09/623789.aspx)">(http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/09/623789.aspx)
Thanks again for the checkdb note. Reading up on it, according to BOL,
DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed.
so I can keep the checkcatalog and just delete my checkalloc step.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply