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