August 9, 2005 at 3:39 pm
I'm not a dba, we don't have one, so we have to figure things out for ourselves. My question is should we have the trunc. log on chkpt. set on or not. Our database is set to full recovery mode. We do full backups once a week and transaction log backups daily. I was under the impression that the log would truncate when backed up, however this does not seem to be the case(trunc. log on chkpt. is not set). We are having disk space issues and this database is around 90 gig, so I need to use a little disk space as possible on the backups. Any recommendations on this???
August 10, 2005 at 1:58 am
Ok, where to start?
Recovery models
A database has 3 recovery models: Simple, Bulk-logged or full. These were introduced in SQL 2000 to replace/complement "truncate log on checkpint" and "select into/bulk-copy" which go way back.
"truncate log on checkpint" set = simple recovery. Full stop, regardless of "select into/bulk-copy"
"select into/bulk-copy" set = Bulk logged
Neither set = Full
Log size
When a log backup is done, the physical log file on disk does not shrink in size.
See "trunc. log on chkpt. option" in BOL index, "Truncating the Transaction Log"
To shrink the file, you have to DBCC SHRINKFILE, but it will just grow again (with default settings) and fragment on disk.
Ideas
Do you rebuild indexes? This may compact your data.
Do you have all backups still?
Can you put more disks in? If you have that much data, don't penny pinch on disk space and compromise your recovery path. More disks for the data only or just for the backups? NAS?
Can you archive data? Into text files and compress them?
Cheers
Shawn
August 10, 2005 at 8:15 am
If you run log backups more frequently they will not have to grow as big. Try once an hour.
When you do a log backup the log is logically truncated but the log file doesn't automatically shrink, just like the MDB files don't shrink automatically when you delete data. After a backup the inactive portions of the log file can be reused rather than growing the file.
You should reindex tables periodically, but be aware that this generates A LOT of log activity. The reindex optimization maintenance plan may compact the tables in a database, but the log file may grow larger than the data file and the next log backup may be as big or bigger than a full backup. If you've got disk space problems, be very careful about reindexing. It may be better to find out which tables have the most unused space, then use bcp to export & reimport them instead of reindexing.
If you're running short of disk space, can't add disks, and can't find a large maintenance window to work on this, you have my sympathy.
August 10, 2005 at 8:50 am
Another point worth making here is that setting the 'Truncate Log on Checkpoint' optoin effectively puts your database in Simple Recovery mode. This means that in the event of data loss or a disaster, you can only recover to the point where the last full database backup was taken. Therefore you should use this option only if you can afford to lose data up to the point of your last full backup.
My hovercraft is full of eels.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply