How to shrink 38 gig .ldf file

I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.I got this from a download from a while back. It says 7.0 in the header, but has been modified for SQL 2000. It hads worked for me many, many times.

1. Create the proc in QA
2. Change to the database in which you want to shrink the log file and then execute the proc.

** WARNING **
Use at your own risk ... no warranty applies ... test it first on a database you can afford to trash and restore (the old liability lawyerese)


use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@.azdatabases.com
------------------------
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.max_iterations int = 1000,
@.backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @.db sysname,
@.last_row int,
@.log_size decimal(15,2),
@.unused1 decimal(15,2),
@.unused decimal(15,2),
@.shrinkable decimal(15,2),
@.iteration int,
@.file_max int,
@.file int,
@.fileid varchar(5)
select @.db = db_name(),
@.iteration = 0
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
---- ------- ------- ---- ---- -- --------
2 1245184 8192 925963 0 128 0
*/
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateLSN varchar(32)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @.file_max = @.@.rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @.unused1 = @.unused -- save for later
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
while @.shrinkable * 100 / @.log_size > @.target_percent
and @.shrinkable > @.target_size_MB
and @.iteration < @.max_iterations begin
select @.iteration = @.iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @.file = 0
while @.file < @.file_max begin
select @.file = @.file + 1
select @.fileid = fileid from #logfiles where id = @.file
exec( 'dbcc shrinkfile( ' + @.fileid + ' )' )
end
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
end
if @.unused1 < @.unused
select 'After ' + convert( varchar, @.iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @.unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @.unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @.unused <= 10
else
select 'It took ' + convert( varchar, @.iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
go|||Mike,

Backing up your database should shrink your transaction log for you but if you would like to force a shrink on just the ldf file you can use the following:

DBCC SHRINKFILE (OTG03.ldf)

Hope this helps.|||Mike,

Just a clarification, you should replace "OTG03.ldf" with the sql file name. You can find this by right clicking on the database going to properties and on the "Transaction Log" tab.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.

Use exec sp_helpfile in the database to find the correct value to use with
DBCC SHRINKFILE (?)

Tim S|||Mike,

DBCC SHRINKFILE (OTG03Log) should do the trick for you. Make sure you are running it on your OTG03 database.

Try this:

USE OTG03
GO
DBCC SHRINKFILE (OTG03Log)
GO|||Well ..
If you have no need for the transaction log then you can set the recovery mode to simple and use the following commands
------------
backup log dbname with no_log
go
use OTG03
go
DBCC SHRINKFILE (OTG03Log)
GO

------------

This should do the trick ...
Or for another option ... open the EM ... right click on database name ... select all tasks ... shrink database and then explicitly shrink the log file from there ...

In case you get any error .. revert back with the error text ...