Sql Public Role: log

Showing posts with label log. Show all posts

Friday, March 23, 2012

Purging Transaction Log

I am trying to purge the logfile (ldf) for our database.
I thought I had done it in the past through Enterprise
Manager by clicking the database, selecting
AllTasks/Shrink Database and then reducing the size of the
transaction log to its minimum. The logfile is currently
150 MB. I specified to shrink it to the minimum (8 MB).
It told me that it succeeded but the size is not changing.
Is this the appropriate way to reduce the log size?
THanks,
-RobYou must back up the log if you are in Full Recovery Mode prior to
shrinking...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob C" <robnocspam@.ctnosoftwarespam.com> wrote in message
news:0a4701c3bf26$c8d635d0$a401280a@.phx.gbl...
> I am trying to purge the logfile (ldf) for our database.
> I thought I had done it in the past through Enterprise
> Manager by clicking the database, selecting
> AllTasks/Shrink Database and then reducing the size of the
> transaction log to its minimum. The logfile is currently
> 150 MB. I specified to shrink it to the minimum (8 MB).
> It told me that it succeeded but the size is not changing.
> Is this the appropriate way to reduce the log size?
> THanks,
> -Rob|||Hi Rob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Our MVP, Wayne had provided a good explanation of how to shrink the log
file.
I just want to add some more information of this part.
When SQL Server finishes backing up the transaction log, it automatically
truncates the inactive portion of the transaction log. This inactive
portion contains completed transactions and so is no longer used during the
recovery process. Conversely, the active portion of the transaction log
contains transactions that are still running and have not yet completed.
SQL Server reuses this truncated, inactive space in the transaction log
instead of allowing the transaction log to continue to grow and use more
space. That is why although you use Enterprise Manager to set the log file
size, but it remains the same. You shoud backup it first to let it become
inactive.
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain.
Until a full database backup is created, the database is not protected from
media failure. Use manual log truncation only in very special
circumstances, and create a full database backup as soon as practical.
The ending point of the inactive portion of the transaction log, and hence
the truncation point, is the earliest of the following events:
1)The most recent checkpoint.
2)The start of the oldest active transaction, which is a transaction that
has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll
back transactions during recovery.
3)The start of the oldest transaction that involves objects published for
replication whose changes have not been replicated yet. This represents the
earliest point that SQL Server still has to replicate.
You can refer to SQL Server Books on Line (BOL) for more information for
active portion and inactive portion of log files and some DBCC commands to
carry out log truncation by searching :
"Truncating the Transaction Log" in the BOL
I hope will be helpful to resolve your question. If you still have any
questions, please feel free to post any new message here, I am ready to
offer you further help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you both fo you replies.
I was able to successfully shrink the log once I ran the
backup process on the transaction logfile.
I also appreciated the explanation behind it.
Regards,
-Rob
>--Original Message--
>Hi Rob,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>Our MVP, Wayne had provided a good explanation of how to
shrink the log
>file.
>I just want to add some more information of this part.
>When SQL Server finishes backing up the transaction log,
it automatically
>truncates the inactive portion of the transaction log.
This inactive
>portion contains completed transactions and so is no
longer used during the
>recovery process. Conversely, the active portion of the
transaction log
>contains transactions that are still running and have not
yet completed.
>SQL Server reuses this truncated, inactive space in the
transaction log
>instead of allowing the transaction log to continue to
grow and use more
>space. That is why although you use Enterprise Manager to
set the log file
>size, but it remains the same. You shoud backup it first
to let it become
>inactive.
>Although the transaction log may be truncated manually,
it is strongly
>recommended that you do not do this, as it breaks the log
backup chain.
>Until a full database backup is created, the database is
not protected from
>media failure. Use manual log truncation only in very
special
>circumstances, and create a full database backup as soon
as practical.
>The ending point of the inactive portion of the
transaction log, and hence
>the truncation point, is the earliest of the following
events:
>1)The most recent checkpoint.
>2)The start of the oldest active transaction, which is a
transaction that
>has not yet been committed or rolled back.
>This represents the earliest point to which SQL Server
would have to roll
>back transactions during recovery.
>3)The start of the oldest transaction that involves
objects published for
>replication whose changes have not been replicated yet.
This represents the
>earliest point that SQL Server still has to replicate.
>You can refer to SQL Server Books on Line (BOL) for
more information for
>active portion and inactive portion of log files and some
DBCC commands to
>carry out log truncation by searching :
>"Truncating the Transaction Log" in the BOL
>I hope will be helpful to resolve your question. If you
still have any
>questions, please feel free to post any new message here,
I am ready to
>offer you further help.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||How does one manually truncate a transaction log?|||Hi Gayle,
Thank you for using MSDN Newsgroup!
You can manually truncate the trasaction log. Please refer to the following
article for detailed information:
http://support.microsoft.com/?id=272318
If you still have question, please post new message here and I am ready to
help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for replying! The drive where the transaction log resides -
drive L - ran out of space (the transaction log is 97GB). This has,
naturally, made it to where we cannot access our databases through
Enterprise Manager. When we try to open Query Analyzer, it times out
but then still shows a query window with the Master database selected,
but we cannot change from the Master to any of our application's
databases. We tried to do the following via Query Analyzer anyway:
backup database (to the S drive, which had space)
backup log with truncate_only
dbcc shrinkfile on the log file
backup database
We let it run ("Processing query batch") for an hour before we stopped
it. We assume that it didn't work because it was trying to write to
the transaction log but couldn't. Is that correct? Does SQL write
administrative queries (like backup statements) to the transaction
log? We know now what we need to do to prevent the transaction log
from outgrowing its space and we're going to try to set up an alert so
that SQL will page us when the log gets too big.
I guess what I'd like to know is, should our preventive measures fail
and the log's drive fill up again, what is the best approach to take?
Thank you for any info you can share.sql

Purging the SQL Server Log

I am required by my customer to audit all logins to the SQL Server database. I have the Audit Level set to 'All'. What I have noticed is that SQL Agent is constantly logging in which is rapidly bloating the size of the ERRORLOG. It's now at 90MB, but once it gets beyond 12 MB it becomes pretty much unusable as a tool.

Is there some way to eliminate SQL Agent from the Audit process?

Alternatively, is there some way to "roll" the log (much like SQL Server does at start-up) while SQL Server is still running?

Any input is welcome.

Regards,

Hugh Scottwhat version of SQL Server are you running? If 2k then try sp_cycle_errorlog.

I am not aware of a way to exclude a user's activity from the audit process.|||Thank you sir!!

Hugh Scott

Originally posted by Paul Young
what version of SQL Server are you running? If 2k then try sp_cycle_errorlog.

I am not aware of a way to exclude a user's activity from the audit process.

Purging SQL Server Log Files

I am currently maintaining a database at work that somehow has a
transaction log of 20 GB. Is there an easy way to purge this log
file?
Thanks,
JeffCertainly :-) Have a look at
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650
To understand why it has grown check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff|||Hi,
This is because of the recovary model you selected, if the database is
defined as "FULL" recovary, you may need to perform a Transaction log backup
using (Backup Log) command, otherwise the Transaction log file records and
wont clear the Logs and it grows to higher limit incase you are not limiting
the transaction log size.
In your case do,
1. Change the recovary model to Simple
2. Run "Backup log dbname with no_log"
3. Use dbcc shrinkfile command to shrink the log file
4. Change the recovary model to "FULL"
5. Schedule a Backup log command atleast twice a day .
Thanks
Hari
MCDBA
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

Purging SQL Server Log Files

quote:

> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

|||Hi,
This is because of the recovary model you selected, if the database is
defined as "FULL" recovary, you may need to perform a Transaction log backup
using (Backup Log) command, otherwise the Transaction log file records and
wont clear the Logs and it grows to higher limit incase you are not limiting
the transaction log size.
In your case do,
1. Change the recovary model to Simple
2. Run "Backup log dbname with no_log"
3. Use dbcc shrinkfile command to shrink the log file
4. Change the recovary model to "FULL"
5. Schedule a Backup log command atleast twice a day .
Thanks
Hari
MCDBA
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...

quote:

> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

sql

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnJohn J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date >
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much. I
> was hoping for something like the above but will a limit of say 100 records
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> > getdate()-90". The problem is this really puts a load on the system.
>> I would like this to be an idle job that does not load the system as
>> much. I was hoping for something like the above but will a limit of say
>> 100 records each time, is that possible?
>> Regards,
>> John
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> >
>> getdate()-90". The problem is this really puts a load on the system.
>> I
>> would like this to be an idle job that does not load the system as much.
>> I
>> was hoping for something like the above but will a limit of say 100
>> records
>> each time, is that possible?
>> Regards,
>> John
>>|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>> John,
>> Guess I did not see it before, thanks. By the way the BOL says to use
>> top
>> in new development since ROWCOUNT will no longer be supported. If I
>> understand correctly then it should be the following:
>> delete top(100) [<table>] where data < @.earlierstdate;
> TOP is only for SQL 2005.
>> I was thinking of putting this in a JOB set to when idle Do you think
>> this
>> would cause too much thrashing or would it be better to put it as you do
>> but
>> with a waitfor delay. It would be nice to allow it to run until the
>> server
>> became busy and then exit until next idle.
>> Something like:
>> while(@.@.rowcount > 0 and @.@.cpu < (something)
>> delete top(100) [<table>] where data < @.earlierstdate;
>> FROM BOL:
>> Important:
>> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements
>> in
>> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
>> INSERT, and UPDATE statements in new development work, and plan to modify
>> applications that currently use it. We recommend that DELETE, INSERT, and
>> UPDATE statements that currently are using SET ROWCOUNT be rewritten to
>> use
>> TOP.
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>> regards,
>> John
> John
>

Purging Log File

How can I reset a log file for a database that is active. The file is 8
gigs. I want to restrict its growth to 50 MB and purge the contents.
How can I do this.General info on file shrinking as well as link to shrinking log file size:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ross B." <ross@.__computaught__.com> wrote in message
news:u%2382JFQDEHA.1600@.tk2msftngp13.phx.gbl...
> How can I reset a log file for a database that is active. The file is 8
> gigs. I want to restrict its growth to 50 MB and purge the contents.
> How can I do this.
>

Wednesday, March 21, 2012

Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WByou have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||When you do a log backup, the log file(s) will be emptied. It is not normal to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should investigate *why* the log
keep growing despite your regular log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log file
>> is growing significantly faster. At the present time my data file is around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to prevent
>> the file from getting to large? Any suggestions on solving this problem are
>> greatly appreciated.
>> WB
>>
>|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB'
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek|||I don't understand. You say you shrunk it down to 100MB. Then you say the file is still 9GB?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max file
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB'
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log
> file
>> is growing significantly faster. At the present time my data file is
> around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to
> prevent
>> the file from getting to large? Any suggestions on solving this problem
> are
>> greatly appreciated.
>> WB
>>
>|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> > Thank you for all the input. I was able to backup the log file and then
> > shrink it down to under 100MB. The problem now is I can't set the max
file
> > size to say 2GB because the file size is still 9GB. How can I prevent
the
> > file from growing beyond 2GB'
> >
> >
> > "WB" <none> wrote in message
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> My database file is growing and growing and growing. This is good...
> >> The bad news, at least for storage limitations, is the Transaction log
> > file
> >> is growing significantly faster. At the present time my data file is
> > around
> >> 150 MB and the Transaction log file is just under 10GB......
> >>
> >> Do I need to let the Transaction log file grow or is there a way to
> > prevent
> >> the file from getting to large? Any suggestions on solving this
problem
> > are
> >> greatly appreciated.
> >>
> >> WB
> >>
> >>
> >
> >|||I see. I don't use the EM GUI much, but I assume it means that the file size is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might require a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you say the
> file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> > Thank you for all the input. I was able to backup the log file and then
>> > shrink it down to under 100MB. The problem now is I can't set the max
> file
>> > size to say 2GB because the file size is still 9GB. How can I prevent
> the
>> > file from growing beyond 2GB'
>> >
>> >
>> > "WB" <none> wrote in message
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> >> My database file is growing and growing and growing. This is good...
>> >> The bad news, at least for storage limitations, is the Transaction log
>> > file
>> >> is growing significantly faster. At the present time my data file is
>> > around
>> >> 150 MB and the Transaction log file is just under 10GB......
>> >>
>> >> Do I need to let the Transaction log file grow or is there a way to
>> > prevent
>> >> the file from getting to large? Any suggestions on solving this
> problem
>> > are
>> >> greatly appreciated.
>> >>
>> >> WB
>> >>
>> >>
>> >
>> >
>|||ok, thanks for staying with the thread.
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
> I see. I don't use the EM GUI much, but I assume it means that the file
size is 9GB and it is only
> used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might
require a few BACKUP,
> SHRINK, BACKUO, SHRINK iteration where you monitor the status between
using DBCC LOGINFO. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "WB" <none> wrote in message
news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> > Yes, There are two listings for the transaction file.
> >
> > 1. Current size = ~ 9GB
> > 2. Space used = ~100MB
> >
> > I am referencing the details listed under Shrink Database -> files
> >
> > WB
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> >> I don't understand. You say you shrunk it down to 100MB. Then you say
the
> > file is still 9GB?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "WB" <none> wrote in message
news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> >> > Thank you for all the input. I was able to backup the log file and
then
> >> > shrink it down to under 100MB. The problem now is I can't set the
max
> > file
> >> > size to say 2GB because the file size is still 9GB. How can I
prevent
> > the
> >> > file from growing beyond 2GB'
> >> >
> >> >
> >> > "WB" <none> wrote in message
> > news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> >> My database file is growing and growing and growing. This is
good...
> >> >> The bad news, at least for storage limitations, is the Transaction
log
> >> > file
> >> >> is growing significantly faster. At the present time my data file
is
> >> > around
> >> >> 150 MB and the Transaction log file is just under 10GB......
> >> >>
> >> >> Do I need to let the Transaction log file grow or is there a way to
> >> > prevent
> >> >> the file from getting to large? Any suggestions on solving this
> > problem
> >> > are
> >> >> greatly appreciated.
> >> >>
> >> >> WB
> >> >>
> >> >>
> >> >
> >> >
> >
> >
>|||And rather than setting a max size for the file, then remember to put it to
SIMPLE recovery - that will keep the file in a decent size but it have the
space to grow if it needs it for some reason.
/Steen
WB wrote:
> ok, thanks for staying with the thread.
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
>> I see. I don't use the EM GUI much, but I assume it means that the
>> file size is 9GB and it is only used 100MB. You need to shrink the
>> file using DBCC SHRINKFILE. It might require a few BACKUP, SHRINK,
>> BACKUO, SHRINK iteration where you monitor the status between using
>> DBCC LOGINFO. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more
>> info.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "WB" <none> wrote in message
> news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
>> Yes, There are two listings for the transaction file.
>> 1. Current size = ~ 9GB
>> 2. Space used = ~100MB
>> I am referencing the details listed under Shrink Database -> files
>> WB
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you
>> say the file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message
> news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> Thank you for all the input. I was able to backup the log file
>> and then shrink it down to under 100MB. The problem now is I
>> can't set the max file size to say 2GB because the file size is
>> still 9GB. How can I prevent the file from growing beyond 2GB'
>>
>> "WB" <none> wrote in message
>> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is
>> good... The bad news, at least for storage limitations, is the
>> Transaction log file is growing significantly faster. At the
>> present time my data file is around 150 MB and the Transaction
>> log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way
>> to prevent the file from getting to large? Any suggestions on
>> solving this problem are greatly appreciated.
>> WB|||A terrible solution and an indication that you have not yet discovered the
true purpose of the transaction log.
You have much left to learn, Grasshopper.
Anthony Thomas
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>

Purge Transaction Log File

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the las
t
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exportin
g
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Niall" wrote:

> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used f
or
> McAffee protection pilot (A policy driven AV managment system). Over the l
ast
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is onl
y
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems
to
> have had no effect on the ldf file. I sopke to someone who sugested export
ing
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - th
e
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.sql

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the last
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exporting
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"Niall" wrote:
> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used for
> McAffee protection pilot (A policy driven AV managment system). Over the last
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems to
> have had no effect on the ldf file. I sopke to someone who sugested exporting
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - the
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the last
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exporting
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.
Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"Niall" wrote:

> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used for
> McAffee protection pilot (A policy driven AV managment system). Over the last
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems to
> have had no effect on the ldf file. I sopke to someone who sugested exporting
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - the
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.

purge a log file

Hello,

I would like to use the built in logging feature to log to a text file.

Is there a way to purge the log peridically (only keep enties for the last 30 days, etc).

Thanks,

Michael

There's no built in feature for doing this. You would have to build something yourself.

If you require this as a feature then request it at Microsoft Connect.

-Jamie

purge .txt log files

SS2005, SP2
Anyone has a quick way of deleting log files for db maint. plans? By default
they are text files created under the LOG directory. Tired of googling for
it 'cause there arn't many posts...I believe this is in one of the maint tasks.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"YPD" <y.ding@.neu.edu> wrote in message news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
> SS2005, SP2
> Anyone has a quick way of deleting log files for db maint. plans? By default
> they are text files created under the LOG directory. Tired of googling for
> it 'cause there arn't many posts...
>|||Have tried the History Cleanup Task and Maint Cleanup Task. Neither does the
job. They simply cleanup the actual backup files, or the backup/restore
history tables in msdb. I want something to clean up the log files otherwise
they keep accumulating...
I can write a homegrown process to handle this. But I'd be amazed that
there isn't anything out of the SSIS box that can do this.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>I believe this is in one of the maint tasks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "YPD" <y.ding@.neu.edu> wrote in message
> news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>> SS2005, SP2
>> Anyone has a quick way of deleting log files for db maint. plans? By
>> default they are text files created under the LOG directory. Tired of
>> googling for it 'cause there arn't many posts...|||> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does the job.
I see, I though this was part of the "standard process". But Maint Cleanup Task isn't limited to
deletion of backup files. Did you try to add one more such task for deletion of the .txt files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"YPD" <y.ding@.neu.edu> wrote in message news:%23dWriGrsHHA.1728@.TK2MSFTNGP06.phx.gbl...
> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does the job. They simply
> cleanup the actual backup files, or the backup/restore history tables in msdb. I want something to
> clean up the log files otherwise they keep accumulating...
> I can write a homegrown process to handle this. But I'd be amazed that there isn't anything out
> of the SSIS box that can do this.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>>I believe this is in one of the maint tasks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>> SS2005, SP2
>> Anyone has a quick way of deleting log files for db maint. plans? By default they are text files
>> created under the LOG directory. Tired of googling for it 'cause there arn't many posts...
>|||Yes, I tried but it didn't work. I also tried to fool the manit plan to
purge regular text files that were renamed just like the backup files. It's
smart enough to know what files are real backup files and what are not, and
only delete REAL backup files. interesting! Fortunately those txt log files
are tiny (1 or 2 kbs each) so leaving them uncleaned really isn't a big
deal.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:078461FC-C310-454D-BEE9-721958C7E312@.microsoft.com...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does
>> the job.
> I see, I though this was part of the "standard process". But Maint Cleanup
> Task isn't limited to deletion of backup files. Did you try to add one
> more such task for deletion of the .txt files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "YPD" <y.ding@.neu.edu> wrote in message
> news:%23dWriGrsHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does
>> the job. They simply cleanup the actual backup files, or the
>> backup/restore history tables in msdb. I want something to clean up the
>> log files otherwise they keep accumulating...
>> I can write a homegrown process to handle this. But I'd be amazed that
>> there isn't anything out of the SSIS box that can do this.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>>I believe this is in one of the maint tasks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>> SS2005, SP2
>> Anyone has a quick way of deleting log files for db maint. plans? By
>> default they are text files created under the LOG directory. Tired of
>> googling for it 'cause there arn't many posts...
>>
>|||I'm a bit confused here. When I open the "Maintenance Cleanup Dialog", there's an option to delete
"Maintenance Plan text reports". Perhaps this was introduced with sp2? Or did you try it and it
didn't work (of so, you should file a bug on http://connect.microsoft.com/sql)?.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"YPD" <y.ding@.neu.edu> wrote in message news:eCfoO8ysHHA.4548@.TK2MSFTNGP04.phx.gbl...
> Yes, I tried but it didn't work. I also tried to fool the manit plan to purge regular text files
> that were renamed just like the backup files. It's smart enough to know what files are real backup
> files and what are not, and only delete REAL backup files. interesting! Fortunately those txt log
> files are tiny (1 or 2 kbs each) so leaving them uncleaned really isn't a big deal.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:078461FC-C310-454D-BEE9-721958C7E312@.microsoft.com...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does the job.
>> I see, I though this was part of the "standard process". But Maint Cleanup Task isn't limited to
>> deletion of backup files. Did you try to add one more such task for deletion of the .txt files?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message news:%23dWriGrsHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither does the job. They simply
>> cleanup the actual backup files, or the backup/restore history tables in msdb. I want something
>> to clean up the log files otherwise they keep accumulating...
>> I can write a homegrown process to handle this. But I'd be amazed that there isn't anything out
>> of the SSIS box that can do this.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>>I believe this is in one of the maint tasks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>> SS2005, SP2
>> Anyone has a quick way of deleting log files for db maint. plans? By default they are text
>> files created under the LOG directory. Tired of googling for it 'cause there arn't many
>> posts...
>>
>|||Tibor, that's a good catch. I overlooked it.
However, i still was unable to get it to work. I have tried it on one
default instance and two clustered instances, with a separate cleanup task
and proper length of days /weeks to purge - log files are not deleted.
A similar bug was logged in the Feedback list earlier this month and MS
marked it as resolved. The ticket wished to build both purging backups and
log files into one interface. So from the wording, it seems someone has
successfully gotten it worked.
I'll try more instances in case it's just a permission issue. Since I've
already had my purge routine in place, it won't be an issue any more even if
it doesn't work. but I'll try to send them a comment on this.
Thanks for your time on helping this.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EB5BC91A-5A5A-4204-99EA-E6743554D034@.microsoft.com...
> I'm a bit confused here. When I open the "Maintenance Cleanup Dialog",
> there's an option to delete "Maintenance Plan text reports". Perhaps this
> was introduced with sp2? Or did you try it and it didn't work (of so, you
> should file a bug on http://connect.microsoft.com/sql)?.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "YPD" <y.ding@.neu.edu> wrote in message
> news:eCfoO8ysHHA.4548@.TK2MSFTNGP04.phx.gbl...
>> Yes, I tried but it didn't work. I also tried to fool the manit plan to
>> purge regular text files that were renamed just like the backup files.
>> It's smart enough to know what files are real backup files and what are
>> not, and only delete REAL backup files. interesting! Fortunately those
>> txt log files are tiny (1 or 2 kbs each) so leaving them uncleaned really
>> isn't a big deal.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:078461FC-C310-454D-BEE9-721958C7E312@.microsoft.com...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither
>> does the job.
>> I see, I though this was part of the "standard process". But Maint
>> Cleanup Task isn't limited to deletion of backup files. Did you try to
>> add one more such task for deletion of the .txt files?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:%23dWriGrsHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither
>> does the job. They simply cleanup the actual backup files, or the
>> backup/restore history tables in msdb. I want something to clean up the
>> log files otherwise they keep accumulating...
>> I can write a homegrown process to handle this. But I'd be amazed that
>> there isn't anything out of the SSIS box that can do this.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message
>> news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>>I believe this is in one of the maint tasks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>> SS2005, SP2
>> Anyone has a quick way of deleting log files for db maint. plans? By
>> default they are text files created under the LOG directory. Tired of
>> googling for it 'cause there arn't many posts...
>>
>>
>|||> I'll try more instances in case it's just a permission issue. Since I've
> already had my purge routine in place, it won't be an issue any more even if
> it doesn't work. but I'll try to send them a comment on this.
This is what I also tend to do. When Maint plans don't do what you want, do it yourself... :-)
Let us know if you find out anything more about this... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"YPD" <y.ding@.neu.edu> wrote in message news:eOtl7aDuHHA.1204@.TK2MSFTNGP03.phx.gbl...
> Tibor, that's a good catch. I overlooked it.
> However, i still was unable to get it to work. I have tried it on one
> default instance and two clustered instances, with a separate cleanup task
> and proper length of days /weeks to purge - log files are not deleted.
> A similar bug was logged in the Feedback list earlier this month and MS
> marked it as resolved. The ticket wished to build both purging backups and
> log files into one interface. So from the wording, it seems someone has
> successfully gotten it worked.
> I'll try more instances in case it's just a permission issue. Since I've
> already had my purge routine in place, it won't be an issue any more even if
> it doesn't work. but I'll try to send them a comment on this.
> Thanks for your time on helping this.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:EB5BC91A-5A5A-4204-99EA-E6743554D034@.microsoft.com...
>> I'm a bit confused here. When I open the "Maintenance Cleanup Dialog",
>> there's an option to delete "Maintenance Plan text reports". Perhaps this
>> was introduced with sp2? Or did you try it and it didn't work (of so, you
>> should file a bug on http://connect.microsoft.com/sql)?.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:eCfoO8ysHHA.4548@.TK2MSFTNGP04.phx.gbl...
>> Yes, I tried but it didn't work. I also tried to fool the manit plan to
>> purge regular text files that were renamed just like the backup files.
>> It's smart enough to know what files are real backup files and what are
>> not, and only delete REAL backup files. interesting! Fortunately those
>> txt log files are tiny (1 or 2 kbs each) so leaving them uncleaned really
>> isn't a big deal.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:078461FC-C310-454D-BEE9-721958C7E312@.microsoft.com...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither
>> does the job.
>> I see, I though this was part of the "standard process". But Maint
>> Cleanup Task isn't limited to deletion of backup files. Did you try to
>> add one more such task for deletion of the .txt files?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:%23dWriGrsHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> Have tried the History Cleanup Task and Maint Cleanup Task. Neither
>> does the job. They simply cleanup the actual backup files, or the
>> backup/restore history tables in msdb. I want something to clean up the
>> log files otherwise they keep accumulating...
>> I can write a homegrown process to handle this. But I'd be amazed that
>> there isn't anything out of the SSIS box that can do this.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message
>> news:8F12D2C7-FA8E-4CFD-A9A4-B273084C46C9@.microsoft.com...
>>I believe this is in one of the maint tasks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "YPD" <y.ding@.neu.edu> wrote in message
>> news:eA$PGNqsHHA.4968@.TK2MSFTNGP06.phx.gbl...
>>> SS2005, SP2
>>>
>>> Anyone has a quick way of deleting log files for db maint. plans? By
>>> default they are text files created under the LOG directory. Tired of
>>> googling for it 'cause there arn't many posts...
>>
>>
>>
>

Friday, March 23, 2012

Purging Transaction Log

Purging the SQL Server Log

Purging SQL Server Log Files

Purging SQL Server Log Files

Purging old records

Purging old records

Purging Log File

Wednesday, March 21, 2012

Purge Transaction Log File

Purge Transaction Log File

Purge Transaction Log File

Purge a Transaction Log?

Purge a Transaction Log?

Purge a Transaction Log?

purge a log file

purge .txt log files

purge .txt log files

Sql Public Role

Blog Archive

About Me