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,
PrabhuShrinking 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/TransactionLogFileGrows_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/TransactionLogFileGrows_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
>
>|||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...
> 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/TransactionLogFileGrows_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
>>|||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...
> > 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/TransactionLogFileGrows_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
> >>
> >>
> >>
>
>|||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...
> 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...
>> > 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/TransactionLogFileGrows_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
>> >>
>> >>
>> >>
>>|||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...
> > 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...
> >> > 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/TransactionLogFileGrows_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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||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...
> 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...
>> > 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...
>> >> > 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/TransactionLogFileGrows_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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>