How to free up SQL 2000 transaction log space?

I have tried all of the Shrink database options provided by Enterprise
Manager to try and shrink my transaction log from its current size of 2Gig,
but no matter what I execute it stays 2Gig.
I selected the option to reduce to its minimum allowable size (which it
showed as about 300Meg), but even after that the file still shows as 2Gig.
Is there a different mechanism I need to do to free up this huge amount of
space? A stored procedure or other system proc?
Many thanks!
Marksee
http://www.nigelrivett.net/SQLAdmin...ileGrows_1.html
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Nigel explains on his site about LSN's. For a large database to have a 2 GI
G
log is not unheard of. For example, if a backup hasn't been performed for
several days and there is a rollback or a deadlock and the database has to
track forward from that time, the database log file can swell quickly. You
may want to look at the cause of the log size moreso than just trying to
shrink the log. If there is an unresolved transaction from a particular day
and the LSN's for a part of the log have overlapping values (ie LSN start of
Wednesday is less than LSN start for Tuesday and matches LSN end of Monday),
you may be able to simply take the database offline and run a restore
operation to clean up the log. Nigel's instruction page is very clear. I
hope it helps you with your problem.
--
Regards,
Jamie
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Thanks for the great help Jamie, that answered my question and more!
Thanks,
Mark
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:30313C61-ECCC-4294-8AC9-8EB6AD22FF78@.microsoft.com...
> Nigel explains on his site about LSN's. For a large database to have a 2
> GIG
> log is not unheard of. For example, if a backup hasn't been performed for
> several days and there is a rollback or a deadlock and the database has to
> track forward from that time, the database log file can swell quickly.
> You
> may want to look at the cause of the log size moreso than just trying to
> shrink the log. If there is an unresolved transaction from a particular
> day
> and the LSN's for a part of the log have overlapping values (ie LSN start
> of
> Wednesday is less than LSN start for Tuesday and matches LSN end of
> Monday),
> you may be able to simply take the database offline and run a restore
> operation to clean up the log. Nigel's instruction page is very clear. I
> hope it helps you with your problem.
> --
> Regards,
> Jamie
>
> "Mark Findlay" wrote:
>

How to Free SQL Server 7 Transaction Log Space

Hi Guys,
My company SQL Server has allocated 6G+ of space for the transaction log space. However, only about 300MB of them are used.
Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
Thanks in advance.
TangQ1 Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

Originally posted by DBA
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||RE: This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

Originally posted by DBA
RE:

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||RE: Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

You are welcome.

You may be interested to know that MS supposedly changed the shrink algorithm somewhat in Sql Server 2k to make it act noticeably more quickly (apparently many users do not like to wait).

Actually I think actions taken by impatient 7.0 users to find a "quicker way" caused frequent headaches at Microsoft, and that led to the speedier shrink performance in Sql Server 2k. ;)|||The following is from BOL:

Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary.sql

How to find space used by transaction logs ?

Hello all,
how can I find space in use in transaction log file of a database
at given moment of time ? ( programmatically)
( not using dbcc sqlperf(logspace)
thank you
jagdishHere is the code :

declare @.log_size float(53)
declare @.log_space_used float(53)
declare @.total decimal(20,4)

select @.log_size = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Size (KB)'
select @.log_space_used = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Used Size (KB)'

set @.total = (@.log_space_used/@.log_size) * 100

select 'Using ' + convert(varchar(20),@.total) + '% (' + convert(varchar(20),@.log_space_used) + ' MB) of ' + convert(varchar(20),@.log_size) + ' MB' as 'Report'

.................
note : this is script against pubs database ,so change the name !

Cheers .
srdjan|||Thank you for your help