Shrink SQL 2005 Transaction Logs

[Daryl_Katauskas]

Hello everyone,

Am experiencing an issue with the EV SQL server transaction logs; once we began Journal Archving, our SQl logs have grown alarmingly; the Journal EV log is over 22GB!

Anyhow, I've now setup a nightly transaction log backup and log trucate job with Backup Exec 10D.

All well and good. I've also setup a Maintenance Plan to shrink the transaction logs after they've been backed up and truncated. I ran the shrink file and have only freed up abourt 10% of the space previously occupied by the transaction logs.

Can anyone help with a metohdology to properly shrink the transaction logs under SQL 2005?

Thanks in advance,

Daryl Katauskas
Incentra Solutions

[Alan_M]

I would recommend frequent, several times a day, log backups to shrink the size on disk. If you backup log files every four hours it should allow you to manage the size on disk and provide a level of data protection adequate for most organizations. If you backup to disk you won't suffer a performance hit, you can choose to leave the backups on disk or then put them to tape independent of the disk backup.

[edgar_melecio]

Look into the Recovery Mode for the Database. It should be SIMPLE, not FULL.

Also make sure you have a good backup ( daily! ) of your SQL Server.


Take a look into http://support.veritas.com/docs/283402