Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-05-22 : 11:07:21
|
| I want to switch this setting off on all db's - that's easy.But I presume that means the problem could occur without me knowing for weeks (maybe due to a defective disk driver). So is there a way I can schedule a maintainance task that will check for this problem out of hours? (Maybe by switching the DB option on for a while and then back off? If you switch the option on, does it check the whole database? Or does it just do checks during the actual update?) Hopefully there is a better way than that to do it...Also, am I right in saying that CHECKSUM is new to 2005?And what do you guys use: Checksum, Torn Page Detection or None? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-22 : 11:14:33
|
| If you want to schedule a maintainance task, you can use DBCC CHECKDB instead.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-22 : 11:16:38
|
quote:
Originally posted by harsh_athalye
If you want to schedule a maintainance task, you can use DBCC CHECKDB instead.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Thanks. I do that already. Is that good enough then? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-22 : 11:28:24
|
| Yes. I believe DBCC CHECKDB WITH PHYSICAL_ONLY should be sufficient.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 11:55:43
|
| "And what do you guys use: Checksum, Torn Page Detection or None?"I can't imagine NOT using this. I can't really imagine not using the MAXIMUM protections either.What's causing you to want to disable it?Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-22 : 12:11:00
|
quote:
Originally posted by Kristen
"And what do you guys use: Checksum, Torn Page Detection or None?"I can't imagine NOT using this. I can't really imagine not using the MAXIMUM protections either.What's causing you to want to disable it?Kristen
You can't emainge not using what? "Checksum" or "Torn Page Detection"?What do you mean by "maximum protections"?I'm doing an audit on the database server for the first time and I have noticed that different db's fall in each of these categories: Checksum, Torn Page Detection & None.So trying to make my mind up as to what to set them all to. An old SQL2000 tip on this link says "Torn Page Detection" can cause a performance hindrence. We have our drivers up to date and we have a battery backup and "Write Back Cache in Disk Controller" turned off. So I'm thinking, it's worth turning it "Torn Page Detection" off. Question is, does Checksum leave a smaller footprint... I presume so. Is it small enough to justify using it? I'm thinking yes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 12:23:41
|
| I can't imagine turning this sort of checking off.As Microsoft add more sophisticated checking systems I am likely to implement the most rigorous, unless there is some very good reason not to.We have had applications fail catastrophically within seconds of a torn page error. To my mind this is much better than us not being aware that they have failed until the next DBCC CHECKDB action (daily for us) and then the delay after that before someone reacts to the Alert they received!We have had RAID5 disk failures that have trashed the database and Torn Page detection drew it to out attention in a very short time period.And we were able to recover the databases with zero data loss, from the Logs, which might not have been the case if there had been a longer delay in discovering the problem.But I have no idea how much it slows down database writes.Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-05-22 : 18:09:03
|
quote:
Originally posted by Kristen
I can't imagine turning this sort of checking off.As Microsoft add more sophisticated checking systems I am likely to implement the most rigorous, unless there is some very good reason not to.We have had applications fail catastrophically within seconds of a torn page error. To my mind this is much better than us not being aware that they have failed until the next DBCC CHECKDB action (daily for us) and then the delay after that before someone reacts to the Alert they received!We have had RAID5 disk failures that have trashed the database and Torn Page detection drew it to out attention in a very short time period.And we were able to recover the databases with zero data loss, from the Logs, which might not have been the case if there had been a longer delay in discovering the problem.But I have no idea how much it slows down database writes.Kristen
And Torn Page detection is better than Checksum?Does that mean you don't recommend running a database in simple mode? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-23 : 03:00:13
|
| "And Torn Page detection is better than Checksum?"I'm no great guru on SQL 2005, but i think its the other way round."Does that mean you don't recommend running a database in simple mode?"Depends. For a database with even a modest number of record modifications, particularly if they are difficult to repeat (transactions made by customers using a web site, for example) I think it is essential.For a database populated by scripts from another source, which could be recreated at will, it would be pointless.I expect there is some middle ground!Kristen |
|
|
|
|
|
|
|
|
torn page detection