Showing posts with label sp1. Show all posts
Showing posts with label sp1. Show all posts

Thursday, March 29, 2012

FTP Task and Temporary Internet Files

I have created a package within SQL Server SSIS which includes an FTP Task, deployed it to our SQL Server (2005 SP1) msdb database and am running this job under SQL Agent on Windows Server 2003. Due to company security requirements this job has to be run under a service account within SQL Agent. The problem with this is that even though a directory is specified within the FTP Task to place any downloaded files into, the files are first written to the TIF (Temporary Internet Files) directory of "Default User" which is on the system drive. Based on corporate standards the system drive (C:) on our servers are only configured with enough space for the OS and other system files. All of the files being transferred are compressed, but some are still well over 1GB in size. The result is that many of our downloads are failing due to the system drive running out of space.

I have attempted to run IE by using "Run As" with the service account credentials, and have changed the location of the TIFs to a different drive, rebooted and verified the settings. When the SQL Agent job was run again, the files were still being written to the "Default User" directory on the system drive. I also created a new template account with the TIFs pointing to a non-system drive and used the User Profiles functionality of System Properties to copy the new template account to "Default User", but still the files are being written to the system drive.

My questions are:

    is there a way to stop the FTP Task from using TIF (i.e. just directly write the file to the location specified) is there a best practice around how to setup a service account and have it create a proper user profile that can be managed separate from "Default User" short of specifying during the OS install, is there a way to move the "Default User" profile directory to a different drive

In case anyone else runs into this situation, I thought I would describe what ended up happening.

The page file was moved off of the system drive; otherwise the server was locking up when the system drive ran out or space. Now that the page file is on a separate drive, once the system drive runs completely out of space during a download the FTP Task (or whatever is managing the FTP transfer in the background) just starts writing the file being downloaded to the specified location (apparently it's smart enough to stop caching without crashing once there is no space left). Once the file that filled up the system drive completes downloading then it is removed from the TIF; normally the files aren't removed from the TIF until the entire package finishes execution.

The only downside to this is that the time period between the system drive becoming full and the file download completing, there are difficulties with logging into the machine. The difficulty is that some error messages are displayed and not being able to use your user profile - the Default User profile is used instead.

|||I am experiencing a similar problem. Does anybody out there know how to address this issue please?

Chris Lund, Software Installation Limited, Manchester UKsql

Monday, March 26, 2012

ft search error in win2k3 sp1 with sql 2k sp4?

I just setup my server with win2k3 sp1 and ALL the latest patches, plus
sql 2004 standard sp4.
I keep getting this error on a table with 39000 records where I am
running a basic wildcard CONTAINS fulltext query for "cer*"
Server: Msg 7643, Level 16, State 1, Line 1
Your search generated too many results. Please perform a more specific
search.
There should be about 30000 records.
I noticed on technet that this was a problem pre sp2 in sql, but was
since patched. Has the bug recently been re-introduced in some combo
of win2k3 and sql patches?
Phadke,
Yes, there was some additional logic added in SP3 that generates the below
error message. SQL FTS is optimized for returning 2000 results per query,
primarily for performance reasons. You may want to re-write your query using
CONTAINSTABLE and use the Top_N_by_Rank parameter to limit the results to
2000 or use a similar TOP restriction in the select list.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129632193.545575.218860@.g47g2000cwa.googlegr oups.com...
>I just setup my server with win2k3 sp1 and ALL the latest patches, plus
> sql 2004 standard sp4.
> I keep getting this error on a table with 39000 records where I am
> running a basic wildcard CONTAINS fulltext query for "cer*"
> Server: Msg 7643, Level 16, State 1, Line 1
> Your search generated too many results. Please perform a more specific
> search.
> There should be about 30000 records.
> I noticed on technet that this was a problem pre sp2 in sql, but was
> since patched. Has the bug recently been re-introduced in some combo
> of win2k3 and sql patches?
>
|||John,
Just to clarify, is this a hard limit of 2000, because I am able to see
the result set for queries that return a con query with 17-18k records
with no problem.
I saw another technote that the problem limit was at approx 20k
records:
http://support.microsoft.com/default...;en-us;Q296456
Thanks for the info,
Aditya
|||Also, is this limitation common to sql 2005?
|||Ok, now I am really confused...I ran a fulltext query for "c*" on a
database on another server. This database has 2.4 Million Records.
This server is Win2k3 (no SP) and SQL 2k SP3.
When I run the query, I get 900,000 records back without an error
message.
|||Aditya,
Yes, this is the KB article that I couldn't find early this morning when in
a rush to get off to work...
Still for performance reasons, it might be best to use Top_N_Rank with
CONTAINSTABLE or is there a specific reason you want to return this many
rows?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129660216.247521.205300@.g47g2000cwa.googlegr oups.com...
> John,
> Just to clarify, is this a hard limit of 2000, because I am able to see
> the result set for queries that return a con query with 17-18k records
> with no problem.
> I saw another technote that the problem limit was at approx 20k
> records:
> http://support.microsoft.com/default...;en-us;Q296456
> Thanks for the info,
> Aditya
>
|||Aditya,
I need to confirm this with a large table in order to generate the large
results. However, I would suspect that it is still in SQL 2005 until proved
otherwise...
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129660966.529955.176010@.g44g2000cwa.googlegr oups.com...
> Also, is this limitation common to sql 2005?
>
|||Aditya,
Different servers can behavior differently for FTS results based upon the OS
platform (different wordbreakers) and due to different noise word file
contents. In this case, and on this server is the single letter "C" removed
from the language-specific noise word file (noise.enu = US English) for the
specific FT-enable column's "Language for Word Breaker"?
As they say the devil is in the details... ;-)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129672239.794380.257680@.o13g2000cwo.googlegr oups.com...
> Ok, now I am really confused...I ran a fulltext query for "c*" on a
> database on another server. This database has 2.4 Million Records.
> This server is Win2k3 (no SP) and SQL 2k SP3.
> When I run the query, I get 900,000 records back without an error
> message.
>

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

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

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