Free up the transaction log

Hi ,
When full db is backed up (using maintenance plan) , does transaction
log get shrunk (inactive transactions are dropped from log) . Also does this
space get released to OS .
Can you pl. let me know answer for same question , when log is backed up
explicity (using backup log without using truncate_only option ) .
What could be the reasons , why my log is not getting shrunk after DB and
log backup . What are the other options available apart from using
truncate_only option and dbcc shrinkfile .
Thanks,
Prabhu
Shrinking is NOT part of a backup, log or db. You must use either DBCC
SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
> Hi ,
> When full db is backed up (using maintenance plan) , does transaction
> log get shrunk (inactive transactions are dropped from log) . Also does
> this
> space get released to OS .
> Can you pl. let me know answer for same question , when log is backed up
> explicity (using backup log without using truncate_only option ) .
> What could be the reasons , why my log is not getting shrunk after DB and
> log backup . What are the other options available apart from using
> truncate_only option and dbcc shrinkfile .
> Thanks,
> Prabhu
|||Thanks for your reply Kelly .
I understand shrinking the physical file(s) is not part of backup .
Truncation does not reduce the size of a physical log file, it reduces the
size of the logical log file
But I have 2 weird situations .
1. My tempdb log just grows out of control . Only way we could reuse the
space is after explicitly using backup log with truncate option and dbcc
shrinkfile .
But , Log truncation should occur ,every time a checkpoint is processed,
provided the database is using the simple recovery model (tempdb is in
recovery model).
2. Other user DBs log files also grow , they are in full or Bulk_Logged
recovery model . We have daily fulldb maintenance plan to take only full DB
backup . Does it truncate the log ? But looks like unused space in log files
are not being reused and log files are just extending .
I have also used backup log command (without truncate_only) but still unused
space is not reused but log file grows .( log truncation should occur after
BACKUP LOG statement , right . )
Thanks,
Prabhu
"Andrew J. Kelly" wrote:

> Shrinking is NOT part of a backup, log or db. You must use either DBCC
> SHRINKDATABSE or SHRINKFILE to do that. Have a look at these:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
> http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
> and AutoShrink
> http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
> 2000 with DBCC SHRINKFILE
> http://www.support.microsoft.com/?id=873235 How to stop the log file from
> growing
> http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
> http://www.support.microsoft.com/?id=307487 Shrinking TempDB
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:0CC4883D-7A2F-4701-94CC-4521F3B81A61@.microsoft.com...
>
>
|||Do you have the latest service pack? I have heard of other postings in
which the tempdb log grows but I can't find any KB on it. I think it was
fixed with a service pack. This KB might be helpful:
http://support.microsoft.com/Default.aspx?id=110139
As for # 2 if you are in Full or BulkLogged recovery mode you must do
regular log backups otherwise the log will continue to grow. A full backup
does not truncate the log only a proper Log backup will do that as long as
there are no open transactions.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply Kelly .
> I understand shrinking the physical file(s) is not part of backup .
> Truncation does not reduce the size of a physical log file, it reduces the
> size of the logical log file
> But I have 2 weird situations .
> 1. My tempdb log just grows out of control . Only way we could reuse the
> space is after explicitly using backup log with truncate option and dbcc
> shrinkfile .
> But , Log truncation should occur ,every time a checkpoint is processed,
> provided the database is using the simple recovery model (tempdb is in
> recovery model).
> 2. Other user DBs log files also grow , they are in full or Bulk_Logged
> recovery model . We have daily fulldb maintenance plan to take only full
> DB
> backup . Does it truncate the log ? But looks like unused space in log
> files
> are not being reused and log files are just extending .
> I have also used backup log command (without truncate_only) but still
> unused
> space is not reused but log file grows .( log truncation should occur
> after
> BACKUP LOG statement , right . )
> Thanks,
> Prabhu
> "Andrew J. Kelly" wrote:
|||Hi Andrew ,
1. I have SP4 ( 8.00.2040 ) .
2. If DB is in simple recovery mode , log is getting truncated (but space
not released to OS , as expected) but for other recovery models backup log
command sometimes truncates log and sometimes it won't .
Thanks,
Prabhu
"Andrew J. Kelly" wrote:

> Do you have the latest service pack? I have heard of other postings in
> which the tempdb log grows but I can't find any KB on it. I think it was
> fixed with a service pack. This KB might be helpful:
> http://support.microsoft.com/Default.aspx?id=110139
> As for # 2 if you are in Full or BulkLogged recovery mode you must do
> regular log backups otherwise the log will continue to grow. A full backup
> does not truncate the log only a proper Log backup will do that as long as
> there are no open transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:8B5506DD-860D-4851-AAE6-AEB4005D753F@.microsoft.com...
>
>
|||If there are any long running open transactions it can not truncate that
portion of the log.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...[vbcol=seagreen]
> Hi Andrew ,
> 1. I have SP4 ( 8.00.2040 ) .
> 2. If DB is in simple recovery mode , log is getting truncated (but space
> not released to OS , as expected) but for other recovery models backup log
> command sometimes truncates log and sometimes it won't .
> Thanks,
> Prabhu
>
> "Andrew J. Kelly" wrote:
|||Andrew ,
I have SP4 but still have issues with tempdb log (issue #1) , as I described
below . Can you pl. suggest me any solution . I also read from MS support
site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
tempdb . Right .
Pl. let me know .
--Prabhu
"Andrew J. Kelly" wrote:

> If there are any long running open transactions it can not truncate that
> portion of the log.
> --
> Andrew J. Kelly SQL MVP
>
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:14032F27-857D-4F34-9864-00A4D9514E67@.microsoft.com...
>
>
|||As I stated if you have long running open transactions in the db the log
file may not be able to be truncated. Try running DBCC OPENTRAN() in the
affected db's.
Andrew J. Kelly SQL MVP
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B04001DD-4233-4E2B-B62C-F4E6C960D77A@.microsoft.com...[vbcol=seagreen]
> Andrew ,
> I have SP4 but still have issues with tempdb log (issue #1) , as I
> described
> below . Can you pl. suggest me any solution . I also read from MS support
> site ,using dbcc shrinkdatabase (and/or dbcc shrinkfile) could currept
> tempdb . Right .
> Pl. let me know .
> --Prabhu
> "Andrew J. Kelly" wrote: