September 7, 2004 at 12:38 pm
I started adding a transaction log backup to some database maintenance plans in hope that with each transaction backup, hoping that it gets truncated. However, some database logs are always the same size and in fact keep growing depsite the transaction log backukp. Other logs are always 1K in size. How can i successfully shrink those logs?
Guy
September 7, 2004 at 3:22 pm
There is a difference between shrinking and truncating a log file. Truncating (done with a transaction log backup) removes inactive transactions from the file. Shrinking removes unused space from the file. Truncating the log file will not change the size of the file, but should help keep it from growing. If its still growing, you need to backup the log more often.
Once you have the growth under control, if you have a lot of unused space, then you can shrink the log file using DBCC SHRINKFILE.
Steve
September 8, 2004 at 3:37 am
You may need to think more about the impact of shrinking log files that will grow again over the next few days.
Any shrink operation will release disk space to the operating system. Any increase in space will grab 1 or (more often) more fragments of disk space to meet your growth request.
After a few weeks or months, your database file will be very badly fragmented at the operating system level. Bad file fragmentation WILL reduce the performance of your application. It can only be cured by a disk defrag.
The standard we use is that database files are never truncated, unless we expect the space released to not be needed for at least 3 months. We also perform regular transaction log backups to prevent log files growing larger than necessary.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 8, 2004 at 7:20 am
I have tried regular transaction log backups (daily), but the .trn files just keep piling up in the backup directory and and are not being removed even though i specified to have them removed (files older than a day) in the maintenance plan. Any suggestions?
September 9, 2004 at 7:20 am
View the job created for the backup by the maintenance plan. I had a problem with the maintenance plan sometimes not setting up the 'Delete old backups' parameter correctly. In the backup job step commend you should find a parameter that looks like:
-DelBkUps 1DAYS
If it looks different, just change it.
Peter
September 9, 2004 at 7:48 am
Do you create SEPARATE .trn files or are you APPENDING to the .trn file?
If you are appending, that's your problem. The RETAINDAYS and EXPIREDATE work by the date/time of the file (that's the OS date/time). So when you append a backup to the file, it changes the date and time of the file and will never meet the RETAINDAYS or EXPIREDATE requirement.
-SQLBill
September 9, 2004 at 9:40 am
I checked the job steps for that maintenance plan and it did have an entry such as -DelBkUps 1DAYS.
Where can i set the SEPARATE or APPENDING options when creating the .trn files? I just specify to create a new directory in the maintenance plan and it seems to create separate .trn files in the backup directory.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply