| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-03-13 : 14:16:56
|
| Ouch -- well, I had an interesting night. My site is still running Snitz 3.3.05, and probably always will be, due to heavy customization and integration work. Turns out that 3.3.05 has a nasty bug in the pop_delete.asp script; if the topic you're trying to delete has topic_id>32767, there'a a Cint that fails where there should be a CLng. So rather than building "DELETE FROM TOPICS WHERE TOPIC_ID=XXXXX", you get "DELETE FROM TOPICS". Ouch.Anyways, that's the background; around dinner time, and we've got no topics.So I turn to the trusty backup and figure I'll restore to a different database and script copying over the topics to the production database. No big deal.However, we've been running with transaction log backups at 5 minute intervals; this happened at about 5:00PM, and the last DB backup was at 2:00AM. So there are 15 hours * 12 txlogs/hour = 180 tx log backups to restore after the full DB restore.And guess what? Txlog restores are *slow*! Those 180 restores took almost 10 hours.Moral of the story: if you're going to use frequent txlog backups, use differential backups relatively frequently, too. And, of course, test a sample restore scenario you you know how long it might take and whether that's acceptable. I periodically test full backups for functionality, but I had neglected to test a realistic scenario for performance. Ugh.Just a friendly heads-up.-b |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-13 : 14:20:47
|
| I feel your pain. We do full backups once per night and transaction log backups every 15 minutes. I should probably test the performance of these and determine if we should be doing differentials as well.Tara |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-13 : 14:52:07
|
| bah ... i could hardly convince these people to do backups every 2 hours ... consider yourself lucky ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-14 : 13:45:17
|
| We could not afford to lose 2 hours of data in case we had to restore to a point in time, so that's why we do transaction logs more often.Why won't they let you do backups more often anyway? If it's because of performance, then why don't they cough up some faster servers.Tara |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-14 : 16:17:45
|
| storage space i think ... they know little about SQL Server and even less about SQL Server backup procedures ... this team manages all NT operating systems (permissions,setup,etc) ... I fought very hard to try to get them to change it at all since they didn't see my point as valid ... it was very difficult because they don't understand how SQL Server works ... sadly, I'll take what I can get ... its very hard getting people to do something when they don't understand it but are supposed to be managing it ... ah ... corporate america ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-14 : 16:49:13
|
| Maybe if you explained to them that transaction log backups are usually very small (some of ours are over 1GB even though it runs every 15 minutes, 1GB because the optimizations job ran during that time, which is the DBCC DBREINDEX jobs), then maybe they would allow you to do them more often. Personally, I would think that they would just buy more storage space since disks are relatively cheap these days rather than risk losing 2 hours of data which can be very costly (customer's satisfaction etc.).Tara |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-03-16 : 18:04:50
|
| There's also that product that compresses SQL server backups on the fly; can't remember its name, but it should be easy to find. We've been looking into that for both disk space and IO performance reasons.Or, hey, if they don't mind losing data, that's their business. Just be sure to save the emails :)Cheers-b |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-03-17 : 03:55:31
|
| The products are SQLZIP at www.sqlzip.com and SQLLITESPEED at www.sqllitespeed.comWe do full backup at night, differential every 2 hours and t-log every hours and we move all the backup files to another server...just in case.HTHFranco |
|
|
|
|
Lesson learned: backups