Freeing up Log Free Space

We are running SQL 2005 SP1.
We have a T-Log that is set for 10gb, set to auto grow by 200mb.
This database should really be set to SIMPLE, but until we can get the
approval and window of time we have to keep it in FULL recovery mode.
We would like to keep the T-Log at 10gb, but free up space, with the hope
that the T-Log should have to grow but very little, if any at all.
We currently have 1.6gb space available.
After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
available.
Any idea how to reclaim some space?
Message posted via http://www.droptable.comCheck "dbcc shrinkfile" in BOL. Backing up (in your case WITH TRUNCATE_ONLY)
the transaction log more frecuently could help it to reuse the space from
inactive transactions.
AMB
"cbrichards via droptable.com" wrote:

> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||cbrichards,
This article and the articles to which it links can be a help to you in
shrinking the log file and in thinking about what you want to do:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
One way to free up space in the log, of course, is to do BACKUP LOG (without
TRUNCATE) periodically and then save the backups for as long as you need
them, even if just a day or so.
However, if you are truncating the log, then what is the point of FULL
recovery mode? This provides no more transactional integrity or rollback
ability than SIMPLE mode. If you are always truncating, then go ahead and
set the database to SIMPLE mode, since that is essentially what you have.
RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:749f8542dc410@.uwe...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
> --
> Message posted via http://www.droptable.com
>|||Thanks Russell.
One thing is for sure, I do not want to shrink the file.
In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
Russell Fields wrote:[vbcol=seagreen]
>cbrichards,
>This article and the articles to which it links can be a help to you in
>shrinking the log file and in thinking about what you want to do:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>One way to free up space in the log, of course, is to do BACKUP LOG (withou
t
>TRUNCATE) periodically and then save the backups for as long as you need
>them, even if just a day or so.
>However, if you are truncating the log, then what is the point of FULL
>recovery mode? This provides no more transactional integrity or rollback
>ability than SIMPLE mode. If you are always truncating, then go ahead and
>set the database to SIMPLE mode, since that is essentially what you have.
>RLF
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
Only if there are no active transactions in the last virtual log file. You
can check for open transactions using DBCC OPENTRAN.
A|||Both of these commands do the same thing to your log. The both free up space
from inactive virtual log files so that it can be reused.
Neither will change the physical size of the log file.
The difference between these commands is that the second one will save the
transactions that it is clearing out, but the first one won't.
The only way to reclaim physical space is to physically shrink the log file.
Note that physically shrinking the log file is very different from physical
shrinking a datafile. Most of the warnings in Tibor's article have to do
with shrinking data files.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a00a55f6cf9@.uwe...
> Thanks Russell.
> One thing is for sure, I do not want to shrink the file.
> In attempting to understand your comment as it relates to mine:
> 1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my
> TLog?
> 2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?
> Russell Fields wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Now I am really confused. In repeating my earlier thread:
"In attempting to understand your comment as it relates to mine:
1. BACKUP LOG <DBName> WITH TRUNCATE_ONLY will not free up space in my TLog?
2. BACKUP LOG <DBName> TO DISK = 'Path\FileName.bak' will free up space?"
So you are saying that in both 1 and 2 that space is freed "Only if there ar
e
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>Only if there are no active transactions in the last virtual log file. You
>can check for open transactions using DBCC OPENTRAN.
>A
Message posted via http://www.droptable.com|||Thanks Kalen.
I think we are getting closer to a final answer on my inquiry.
Since
1. we cannot set our system to SIMPLE recovery for a couple more weeks, and
2. we do not care to keep TLOG backup files, and
3. we want to keep our TLOG file at 10gb and reuse its space, then
we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY to
reuse the 10gb space, correct?
Kalen Delaney wrote:[vbcol=seagreen]
>Both of these commands do the same thing to your log. The both free up spac
e
>from inactive virtual log files so that it can be reused.
>Neither will change the physical size of the log file.
>The difference between these commands is that the second one will save the
>transactions that it is clearing out, but the first one won't.
>The only way to reclaim physical space is to physically shrink the log file
.
>Note that physically shrinking the log file is very different from physical
>shrinking a datafile. Most of the warnings in Tibor's article have to do
>with shrinking data files.
>
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1|||Correct. - RLF
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:74a08b9896a7f@.uwe...
> Thanks Kalen.
> I think we are getting closer to a final answer on my inquiry.
> Since
> 1. we cannot set our system to SIMPLE recovery for a couple more weeks,
> and
> 2. we do not care to keep TLOG backup files, and
> 3. we want to keep our TLOG file at 10gb and reuse its space, then
> we should be able to run periodic BACKUP LOG <DBName> WITH TRUNCATE ONLY
> to
> reuse the 10gb space, correct?
> Kalen Delaney wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||In article <749f8542dc410@.uwe>, u3288@.uwe says...
> We are running SQL 2005 SP1.
> We have a T-Log that is set for 10gb, set to auto grow by 200mb.
> This database should really be set to SIMPLE, but until we can get the
> approval and window of time we have to keep it in FULL recovery mode.
> We would like to keep the T-Log at 10gb, but free up space, with the hope
> that the T-Log should have to grow but very little, if any at all.
> We currently have 1.6gb space available.
> After running BACKUP LOG <DBName> WITH TRUNCATE_ONLY we still have 1.6gb
> available.
> Any idea how to reclaim some space?
>
as others have said it is a 2-step process
(1) backup the log w/ Truncate_only to clear out as many of the VLF's as
are not in use or hung up on some long-rinning transaction
(2) DBCC Shrinkfile being sure to point to the correct file number --
probably 2.
I just did this to reclaim space from a 6G logfile that had never been
backed up and allowed to just grow. Of the 500M I sized it at -- about
25% of the current DB size -- only 14.3M was actually in use.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu