August 28, 2009 at 1:35 pm
We create daily snapshots of our DB2 core system database on a SQL 2005 server and groom the snapshots according to a retention schedule and have around 40 30GB databases (these are normal db's not MSSQL database snapshots).
When the databases are created DBCC CHECKDB is executed and then the db is placed in read-only mode.
Since these db's are never written to again, is it still necessary to run CHECKDB on them? They are fairly large and it would take some time to run CHECKDB on all of them. What is advisable in this situation?
August 28, 2009 at 2:25 pm
One of the causes of corruption is a hardware problem with the disk, and that can cause a problem with any database, even one in read-only mode.
As long as you have a good backup somewhere on tape or disk, you can recover from it, but you would have to know that you have a problem.
How often you need to do a check depends on how important the data is.
August 28, 2009 at 3:53 pm
Ed (8/28/2009)
Since these db's are never written to again, is it still necessary to run CHECKDB on them?
Yes it it. SQL doesn't corrupt it's own databases while writing to them. The major cause of corruption is something in the IO subsystem overwriting bits on the disk, outside of SQL's control. That can happen regardless of whether or not the databases are read-only or read-write.
If there's a good backup, taken when the DB was made read-only, then you don't necessarily have to run checkDB often, as you can always restore from a good backup, but you should still consider running it from time to time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2009 at 7:02 pm
Thank you both. I suspected this was the answer, but I just needed to hear it from someone a little more experienced than I am.
September 6, 2009 at 5:26 am
If CHECKSUM page validation is enabled for the database (and always has been*), the chances of a data corruption going undetected are rather small. So long as this is the case, and CHECKDB has been run at some point in the past, I personally wouldn't bother. As has been said, so long as you have backups (even if it is the DB2 source) and you can recover from it in a time acceptable to you, life's good.
If the server is 2008 Enterprise, and you have storage to burn, setting up database mirroring adds an interesting dimension. If a page corruption is detected** (from the page checksum) the mirrored server can transparently request a good copy of the suspect page from the mirror server. Clever stuff.
Paul
* - Checksums are only created or updated when a page is written. Any pages already in existence when the option was set remain without checksums until modified by any process.
** - all pages except the file header page (page 0), the boot page (9), and GAM, SGAM, and PFS pages.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 4:47 am
Paul White (9/6/2009)
If CHECKSUM page validation is enabled for the database (and always has been*), the chances of a data corruption going undetected are rather small. So long as this is the case, and CHECKDB has been run at some point in the past, I personally wouldn't bother. As has been said, so long as you have backups (even if it is the DB2 source) and you can recover from it in a time acceptable to you, life's good.If the server is 2008 Enterprise, and you have storage to burn, setting up database mirroring adds an interesting dimension. If a page corruption is detected** (from the page checksum) the mirrored server can transparently request a good copy of the suspect page from the mirror server. Clever stuff.
Paul
* - Checksums are only created or updated when a page is written. Any pages already in existence when the option was set remain without checksums until modified by any process.
** - all pages except the file header page (page 0), the boot page (9), and GAM, SGAM, and PFS pages.
Nice to know about the mirroring bit, thanks for that
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 5:30 am
Silverfox (9/9/2009)
Nice to know about the mirroring bit, thanks for that
It is a very cool feature. Not sure if we will ever use it (or notice it being used) but it's nice to know it's there 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply