Transaction Log File

16 Sep 2005 10:03 AM

Prabhat

Hi All,

I have few questions for Transaction Log of the SQL 2000 DB.

I use the below command to truncate the Transaction Log datafile.
backup log databasename with truncate_only
dbcc shrinkfile (databasename_log, 100)

But how do I do this using the Enterprise manager?

I can see that the option available for that but not able to specify the
size there.
(Select DB - Right Click - All Task - Shrink Database... - Click on
"Files..." button and select the Log File. But it does not allow to set the
"Shrink file to" as per my size.

How do I do that?

My 2nd Question:

In DB property - Transaction Log TAB : I have the "Unrestricted file growth"
option selectd under the Maximum file size group. What will have if I select
the "Restrict file growth (MB) option and set to 100". Will it automatically
truncate the log once it reaches to 100 MB or it will fire Error messages on
reach?

Please suggest

Thanks
Prabhat

16 Sep 2005 10:02 AM

Geoff N. Hiten

If you are using the TRUNCATE_ONLY option, go ahead and set the recovery
model to SIMPLE. SQL Server will then truncate the inactive portion of the
log following each checkpoint.

Don't constantly grow and shrink log or data files. Find a size that works
and leave it alone.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Show quote

"Prabhat" <not_a_m***@hotmail.com> wrote in message
news:eR9JnTquFHA.2624@TK2MSFTNGP12.phx.gbl...
> Hi All,
>
> I have few questions for Transaction Log of the SQL 2000 DB.
>
> I use the below command to truncate the Transaction Log datafile.
> backup log databasename with truncate_only
> dbcc shrinkfile (databasename_log, 100)
>
> But how do I do this using the Enterprise manager?
>
> I can see that the option available for that but not able to specify the
> size there.
> (Select DB - Right Click - All Task - Shrink Database... - Click on
> "Files..." button and select the Log File. But it does not allow to set
> the
> "Shrink file to" as per my size.
>
> How do I do that?
>
> My 2nd Question:
>
> In DB property - Transaction Log TAB : I have the "Unrestricted file
> growth"
> option selectd under the Maximum file size group. What will have if I
> select
> the "Restrict file growth (MB) option and set to 100". Will it
> automatically
> truncate the log once it reaches to 100 MB or it will fire Error messages
> on
> reach?
>
> Please suggest
>
> Thanks
> Prabhat
>
>
>
>

16 Sep 2005 10:14 AM

David Portas

The best answer is don't shrink log files (much). You may want to
shrink them very occassionally under special conditions or on a
development box, but you shouldn't normally need to do this as a
relgular task in production PROVIDED you plan storage and backup cycle
correctly.

Truncating the log is a different matter. The log is truncated when you
BACKUP LOG, which you should do regularly unless you are using the
Simple Recovery model.

> But how do I do this using the Enterprise manager?
> I can see that the option available for that but not able to specify the
> size there.

Honestly I don't know. That option is present and enabled on my system.
In common with many people I find Enterprise Manager more trouble than
it is worth. I suggest you use the DBCC command.

--
David Portas
SQL Server MVP
--

Transaction Log File - How To ?