I'm trying to set up a backup maintenance program (after finding out that we didn't have one) on SQL Server, and I've started some transaction log backups running - but I'm trying to work out how to reduce the size of the existing log file, which is almost too big for the drive its on. What's the best way to do this?
Hi Matthew,
When you perform a backup, the transaction log is automatically truncated, so you should be seeing that your transaction log backups are relatively small going forward. It will depend on what your DR plan is, on how often you want to move these transactions log files off the server onto archived storage.
We for example, have a full backup every night (which truncates the log file), and then 15 minute transaction log backups (which again truncate the log file). These sit on our SAN for 3 days so they are available relatively quickly if we have a DR event. After 3 days they are backed up onto tape and taken to an off site location. So in our case, if we had an unusually large transaction log backup (say because a few had failed), it would only eat into the server storage for 3 days.
Another option is to use ShrinkFile, which may make the log file smaller, but I've found it isn't that effective in a transactional system, because as people query the data, the database has to explode out the data again which increases the size of the transaction log...
I've set up those backups as well, and they seem to be working fine. The only thing is that, in terms of hard disk space, the.ldf files don't seem to be getting any smaller. Or am I misunderstanding how those files work?
Hi Matt
You need to explicitly shrink those files, using the
Database>>tasks>>Shrink>>files task (or an equivalent script)
and as Simon said, that's a bit odd in the way it works - you probably need to do it several times with different settings to get the available free space down to a minimum. The actual space you can recover depends on where SQL Server is at the time in its sequence of use and re-use of blocks of space within the logfile itself, IIRC.
There is a fairly radical alternative (which I've never done on a Live system - there are limits to Bravery), but it should be possible to shut the SQL instance down, (backing the db up first, of course); delete the ldf file altogether or rename it; and start the instance up up again - if SQL Server doesn't find its logfile when it starts up, it should just create a fresh blank one, using the size specs in the db properties as a starting point. I think i'd try that a few times in a test system first, before getting up the courage to do it in Live, though....
Ken
Never got back to you guys about this one, but I got the Shrinkfile to work once the backups were all set up and there's now some wriggle room on the hard drive. And after a bit of reading, I'm now more conversant with the difference between truncating logs and shrinking them.
Thank you all for pointing me in the right direction. Appreciate it.