We help IT Professionals succeed at work.

SQL 2008 DB Transaction log is not truncated automatically when backed up

goprasad
goprasad used Ask the Experts™
on
Hello there,

When I perform regular 2 hourly transaction log backup,the size of the log does not get truncated automatically.
How can I ensure that TLog files get truncated after each transaction log backup?
DB is hosted in SQL 2008 Ent edtion.
Set-up 2 hourly backup starting at 7 am till 11.59PM and full backup at 1 Am every day.
Please advise.

Thanks and regards.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron ShiloChief Database Architect

Commented:
hi

the size of the log will never get trucated regardless of how many times you back it up.

on the other hand the contents does get truncated so it can be reused.

in order to shrink the file you will nedd to use the "dbcc shrinkfile()"

or the ssms for the same thing under tasks.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Why you want to shrink tlog file? It will grow again till you backup it.
Backup tlog don't shrink file but clear the contents, so empty space will be filled with new data, so if you shrink it after backup, file will grow after. Best thing to do it's give maximum size possible to tlog and leave it alone (only need to backup it).
Cheers
Hi,

I think you should not worry about truncating your log file. Here is the detailed article on the same subject.

http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too%C2%A0big/

Let me know your opinion.

Author

Commented:
The reason I am worried is that, we have application DB with mdf file size of 165 MB and LDF for this applicaion grows at the rate of 2 GB per hour but mdf file size growth remains at steady growth at the rate of 10 MB per day.
My issue is if I backup tlog every 2 hours , from 7 am to 12 PM and then a full backup at 1am the next day, the log size will not get truncated, even though it has been backed up and committed to mdf. Then why cant we regain the space in the Log volume?
Please advise and correct me where I am wrong?

Author

Commented:
the transaction log file size should revert back to the intial size (as set during the DB creation) after each transaction log backup?

Author

Commented:
Is my understanding correct?
Aaron ShiloChief Database Architect

Commented:
no it isent

backing the trn files dosent shrink it.
in order to shrink it you need to use the dbcc shrinkfile()
Top Expert 2012

Commented:
>>My issue is if I backup tlog every 2 hours , from 7 am to 12 PM and then a full backup at 1am the next day<<
If it is growing that much you need to incresase the frequency you are doing Transaction Log backups, to say every 15 minutes.

>>the transaction log file size should revert back to the intial size (as set during the DB creation) after each transaction log backup?<<
No, the tempdb database is the only one that does that.  

Author

Commented:
So if the DB recovery mode is set to full and say size of LDF is 2 GB at 11 am. When I perform transaction log backup at t11 am, my question is will the size of LDF gets reduced? If not then what does LDF contains to keep the size at 2GB
Top Expert 2012
Commented:
>>my question is will the size of LDF gets reduced?<<
No (nor would you want it to).  What it does do is mark transactions inactive so that they can be re-used and therefore require more space and ultimately force another auto-grow which is expensive.

Author

Commented:
What happens when I perform Full backup, does the ldf size remains the same?

Author

Commented:
That is when I perform Full Backup every night when the recovery model is set to FULL. Will the transaction log size gets reduced? OR it wont based on your explanation above?
Top Expert 2012

Commented:
Yes, it does.

The only time the Transaction Log size changes if:
1.  More space is required and Auto-Grow is enabled.
2.  A shrink file command is issued.
Top Expert 2012
Commented:
Let's try that again to avoid confusion.

>>What happens when I perform Full backup, does the ldf size remains the same? <<
Yes, it does.

>>Will the transaction log size gets reduced? <<
No, it will not.

The only time the Transaction Log size changes if:
1.  More space is required and Auto-Grow is enabled.
2.  A shrink file command is issued.