Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    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:

    PHP Code:
    DBCC SHRINKFILE (OTG03.ldf
    Hope this helps.

  4. #4
    Join Date
    Aug 2004
    Posts
    54
    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.

  5. #5
    Join Date
    Oct 2004
    Posts
    2
    I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
    and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.

  6. #6
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Quote Originally Posted by mikef1
    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

  7. #7
    Join Date
    Aug 2004
    Posts
    54
    Mike,

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

    Try this:

    Code:
    USE OTG03
    GO
    DBCC SHRINKFILE (OTG03Log)
    GO

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •