SQL Server Performance Forum – Threads Archive
Log Truncation
Hi, I have a daily transaction log backup scheduled in my database maintenance plan. I have been making a very close monitoring on the log growth, but iam surprised to discover that on 22nd July at 10.00 am the transaction log size was 952.62mb & then a transaction log backup was taken in the early morning on 23rd July at 1.00 am & when i rechecked the log size on 23rd July at 10.00 am it showed me the same value as 952.62mb. I find it hard to believe until unless it is pure coincedence of having the same log size on 2 consecutive day or the second most probable reason is after the transaction log backup the, server has not truncated the log, but then on other days it has been working fine. Any comments or possible reasons ? Secondly i had a another doubt, if any one can clear, I have a database which has been used for years, but for past few months there has been no activity going on that database. The size of the transaction log has been around 12 gb for those months, even though i have been taking the transaction log backup daily, i am not able to understand why is the size of the log not reducing ? Thanks BhushanDo you use BACKUP LOG …. WITH NOINIT clause? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
To the second question. Try to backup the log BACKUP LOG db_name WITH TRUNCATE_ONLY than shrink it with SHRINKFILE. Bambola.
In the second case, how many space is used and how many is non used?.
You can see that using EM. Luis
For the first case… When Transaction Log backup is done, the free space in the transaction log incerases. If the AUTO SHRINK property is not set, the transaction log size will not decrease. So it may have happned that both the days, you never crossed the allocated transaction log space and so the physical file sixe remained the same. However, if you had monitored the % free space, you would have seen the difference. I would give the same answer to the second question. Is the AUTO SHRINK property set? If it is not, it is likely that you may be having 98-99% free space in the transaction log. For reducing the size of transaction log, refer to previous posts in the forum. Also see previous discussions on reducing the log file size- http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=768 HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Satya, As per the maintenance plan transaction log takes in the default value which is "NOINIT" clause, so even in my case also it is the same. Apart from that iam taking the backup on the hard disk & i feel by using the clause "NOINIT" there should be no problem in truncating the log from the database, so do not feel the problem could be due to this clause. Bambola, If iam not mistaken then by default transaction log is backed up using TRUNCATE_ONLY option, so that is already in place & for the SHRINKFILE option it is actually not enabled neither in the maintenance plan nor in the database properties option tab, so if that can solve the problem then it is fine. Its actually not possible for me to shrink the production database as it has some formal issues pending against it. Thanks Bhushan
Keep in mind truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. Also the active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. To reduce the physical file you must use DBCC SHRINKFILE.
Refer to books online for more information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>