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 |
|
udubnate
Starting Member
1 Post |
 Posted - 2006-12-08 : 20:03:05
|
| Hey all-I am working to establish a backup plan for SQL Server 2000. We are currently doing log shipping between servers.A full backup once a weektransaction log backup every hourWe run a bulk deletion (delete * from table where date < ninetydays) which deletes 22,000 rows approximately. We are running in Full Recovery mode.I wonder if there was anyway to improve my backup plan, preferably minimizing logging when the deletion takes place but still backup the data with log shipping.Let me know if any of you have ideas. ThanksNate |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-09 : 02:04:50
|
| "preferably minimizing logging when the deletion takes place"IME 22,000 rows would be too few to worry about!Is that daily, or once a week? If once a week moving it to daily would reduce the logging amount (but might spoil the user experience of being able to retrieve data for 90 + 6.999 days!You could consider doing the deletion (and any other housekeeping like Index Rebuild) just before the Full backup, stopping log shipping and resuming it (with a copy of the Full backup) - but that depends a bit what the ratio of TLog to Full backup is. (Sounds a lot of hassle to me ...)"A full backup once a week"Wny not more often? That's a lot of files (and potentially a HUGE amount of time) to restore in the event of a disaster. FULLs, or DIFFs during the week, every day would be a better policy I reckon."transaction log backup every hour"Why are you happy to lose an hours work when you could do TLog backups every 10 minutes, and then only lose 10 minutes work?More frequent TLog backups might help with the 22,000 row delete (although I expect that takes well less than 10 minutes, but you could delete them in, say, 5,000 row batches with a 10 minute WAITFOR between each batch to ensure the next TLog backup had run)Kristen |
 |
|
|
|
|
|
|
|
SQL Server 2000 : log shipping, backup plan