Backup of Transcations Doesn't Reduce the Size of the Transcation Log File (LDF)

gtrapp
gtrapp used Ask the Experts™
on
I am running a SQL Server Maintenance Plan to backup a database. I am backing up the transcation log and a TRN file is produced. However, the size of the LDF file is not reduced, and still remains the same. What can I to use the SQL Server maintenance plans to create a TRN file and have the LDF reduced?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think the size of the LDF depends on the Recovery Model selected. You can choose between Full, Bulk Logged, or Simple.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> However, the size of the LDF file is not reduced, and still remains the same.

Taking a Transactional Log backup will not reduce the size of LDF file.

If you want to reduce the file size of Transactional Log file, then you have to use the command below:

DBCC SHRINKFILE ('ur_db_name', 1000);

Where 1000 represents the size of Transactional Log file.

And its not recommended to keep this Shrink operation in your Scheduled maintenance plans as it will increase the fragmentation.

You can pre-grow the Log file to some huge value say 5 GB and then let it be like that.
Note 5 GB depends upon your business requirement and your daily growth of Log file.

Hope this clarifies.

Author

Commented:
The recovery model selected is FULL.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
So, you are saying that I can set a fix size of 5 GB on a LDF, and it will only hold 5 GB of transcations? How do I do that?

I have used the Shrink File command before, and I rather not use it frequently in the maintenance plan.

If you need full recovery then you'll need some manual maintenance like rrjegan17 suggested.

If you're not sure what benefit Full recovery provides in your environment, then consider learning about the other recovery models, they might suit your environment more/less (I don't know). It depends on your environment's needs/priorities.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> So, you are saying that I can set a fix size of 5 GB on a LDF, and it will only hold 5 GB of transcations? How do I do that?

Normally, you can pre-grow the file size of your LDF to 5 GB like that ( a huge value above which Log file size wont grow -- This is to achieve better performance without fragmentation).

This can be done through SSMS as mentioned below:

1. Right Click database in SSMS.
2. Choose Properties --> Files.
3. For Log File, Specify your Initial file size as 5GB and Autogrowth to 500 MB or 20 % like that

>> and I rather not use it frequently in the maintenance plan.

That's the correct decision.
Kindly note that the values specifies 5GB, 500 MB, 20% everything varies based upon your business needs and require slight homework on it.

Hope this helps.
taking a backup of transaction log, doesn't mean that you can reclaim disk space, I would suggest you to take backup of transaction log and shrink LDF file. shrinking log file is not a good idea always but sometime we need to do it for reclaiming the disk space.

Commented:
Take a log backup and immediately execute the below mentioned query
backup log <DBName> with truncate_only
then try to shrink the log file... surely u can gain some amt of free space
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> backup log <DBName> with truncate_only

Of course.. It will reclaim some free space but it will break up the Backup chain and hence it is not recommended.
Hence backup log with truncate_only and dbcc shrinkfile should be manual operations done with care and as required.

Author

Commented:
I am going to try what rrjegan17 says above.

We are a small company with limited resources. I am a part-time DBA, and spend most of my time doing other IT related tasks. So, I don't have much time for needed maintenance. I need to have some automated process to limit or hold the size of LDF files. One database had a LDF size of 160 GB, and nearly crashed the server.

Author

Commented:
Will the size of LDF files be reduced after I follow these steps? I still have big LDF files.

-->This can be done through SSMS as mentioned below:

-->1. Right Click database in SSMS.
-->2. Choose Properties --> Files.
-->3. For Log File, Specify your Initial file size as 5GB and Autogrowth to 500 MB or 20 % like that

Author

Commented:
I answered my question: It does not. You can set it bigger than the current size.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I need to have some automated process to limit or hold the size of LDF files.

Hope you have a Full Backup and Transactional Backup Tasks in place.
Otherwise you need to set those tasks to keep LDF file size in control

Author

Commented:
I have full backups. Does the FULL backup include Transcanal Logs? Or, do I need to create the TRN files, as well as the BAK?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Does the FULL backup include Transcanal Logs?

No..
You have to create a Transactional Log Backup plan.
Creating a Transactional Backup plan is similar to your Full Backup and choose Transactional Log instead of Full in Backup Type and Filetype as TRN instead of BAK.

Author

Commented:
Sorry, for posting to a closed question, but I have one more follow up question.

I have file size growth restrictions on the LDF. If I have file size restrictions on the Transacation Log File (LDF), and I do a backup creating the TRN, do I get all of the transactions? I am thinking, if the LDF does not hold everything because of the size restrictions, do I get everything during the backup?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> If I have file size restrictions on the Transacation Log File (LDF), and I do a backup creating the TRN, do I get all of the transactions?

If transactions grow over File Size specified, then it would overwrite the existing Log Files resulting in loss of some transactions in your Transactional Log Backup.

You need to pre-grow or set the Log File Size at the maximum level to meet up this scenario and say if you have transactional backup every hour, then your LDF File size should be able to hold the worst case higher value of transactions.

Hope this clarifies.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial