I build FT catalog for my database which has 10GB size. Catalog was
built and populated ok.
But our customer has the same database (120 GB) and I run population
and two weeks I see 'Paused', 'Recovering' and so on. FT Catalog
does not finish populating.
Server machine has 2 CPU 3000 GHz, 6G RAM, Win2000 Advanced server
What can I do?
The most common reasons for a catalog entering the paused state due to space
or memory issues. It will also go in the paused state if you are running it
on a laptop and go to battery power.
Check the gatherer logs to try to find out why.
Recovery normally happens when you reboot or restart MSSearch and MSSearch
mounts the catalogs. There is some restoration here as MSSearch figures out
where it is or was in the indexing process. Normally recovery happens very
quickly. The most common reason I have seen for catalogs remaining in the
recovery state is MSSearch running under a different account than the local
system account.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Win32nipuh" <oleg@.opel.com-dot-ua.no-spam.invalid> wrote in message
news:94Odnd2jeuHx4BDfRVn_vg@.giganews.com...
> I build FT catalog for my database which has 10GB size. Catalog was
> built and populated ok.
> But our customer has the same database (120 GB) and I run population
> and two weeks I see 'Paused', 'Recovering' and so on. FT Catalog
> does not finish populating.
> Server machine has 2 CPU 3000 GHz, 6G RAM, Win2000 Advanced server
> What can I do?
>
Showing posts with label 10gb. Show all posts
Showing posts with label 10gb. Show all posts
Monday, March 26, 2012
Wednesday, March 7, 2012
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.com
Check "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 (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
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-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/Forums.aspx/sql-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 are
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 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.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-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/Forums.aspx/sql-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
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
Check "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 (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
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-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/Forums.aspx/sql-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 are
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 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.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-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/Forums.aspx/sql-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
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
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
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.sqlmonster.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 SQLMonster.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.sqlmonster.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 SQLMonster.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.sqlmonster.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:
>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
>> We are running SQL 2005 SP1.
>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 SQLMonster.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:
>>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
>> We are running SQL 2005 SP1.
>>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>> 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
--
Message posted via http://www.sqlmonster.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:
>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.
>> Thanks Russell.
>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||Correct. - RLF
"cbrichards via SQLMonster.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:
>>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.
>> Thanks Russell.
>>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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
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.sqlmonster.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 SQLMonster.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.sqlmonster.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 SQLMonster.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.sqlmonster.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:
>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
>> We are running SQL 2005 SP1.
>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 SQLMonster.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:
>>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
>> We are running SQL 2005 SP1.
>>[quoted text clipped - 12 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 are
no active transactions in the last virtual log file."?
Aaron Bertrand [SQL Server MVP] wrote:
>> 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
--
Message posted via http://www.sqlmonster.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:
>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.
>> Thanks Russell.
>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1|||Correct. - RLF
"cbrichards via SQLMonster.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:
>>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.
>> Thanks Russell.
>>[quoted text clipped - 28 lines]
>> Any idea how to reclaim some space?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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
Subscribe to:
Posts (Atom)