gtrapp
asked on
Backup of Transcations Doesn't Reduce the Size of the Transcation Log File (LDF)
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I have used the Shrink File command before, and I rather not use it frequently in the maintenance plan.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
backup log <DBName> with truncate_only
then try to shrink the log file... surely u can gain some amt of free space
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
-->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
ASKER
I answered my question: It does not. You can set it bigger than the current size.
>> 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
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
ASKER
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?
>> 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.
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.
ASKER
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?
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?
>> 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.
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.
ASKER