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 |
|
Kristen
Test
22859 Posts |
 Posted - 2007-05-07 : 04:34:52
|
| Are there any issues turning this ON?I have inherited a database which started life under SQL 7 (where Torn page Detection was OFF by default), and I'd like to turn it on. Will this reshuffle all the pages to make room for the extra check-sum, or is that stored in a single block somewhere else such that it can easily be added?Is the change going to block access for long? (DB = between 2~5GB)ThanksKristen |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-07 : 05:24:20
|
| I think we have to wait for SQLUSA?I haven't turned this on for a large database yet. I remember doing this when installing SQL 2000, and the database was about 500 mb. Took about 20 seconds for that.Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-07 : 05:32:56
|
"I think we have to wait for SQLUSA?"No thanks  "500 mg"Your data doesn't weigh much!Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-07 : 05:58:05
|
Yeah. I saw that too and altered it accordingly  500 mg was the amount of dust in the fans.Peter LarssonHelsingborg, Sweden |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-05-07 : 18:12:14
|
| What is SQLUSA?Are you on 2000 or 2005? If 2000, turn on torn page detection for sure. If on 2005, turn on page checksums.The 20 secs referenced in a previous reply was not to torn-page protect all the data - just to make the change in the database metadata.Note that in either case, pages are not protected in any way after the option has been enabled UNTIL they're read in, changed, and written back out. Only at that point will they have torn-page protection or a page checksum on them.Hope this helps.Paul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 01:50:53
|
| "What is SQLUSA?"He sells training videos on SQL Server. Not regarded as much good in these parts ...Thanks Paul, that's reassuring. I'll get it turn on in case of any future hardware failures etc."The 20 secs referenced in a previous reply"I've had a look and not found that reference. I'm presuming that turning this on (SQL 2000) will be a quick operation for a 2~5GB database, and won't block users for too long.I look forward to the "clairvoyant torn page detection" in the next version of SQL Server than you don't need to turn on until after an accident!Kristen |
|
|
|
|
|
|
|
|
Turning Torn page Detection ON