Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    michigan
    Posts
    17

    Unanswered: Shrinking LDF file - query running indefinitely

    My intention is to include a sql job to schedule for shrinking log files to half of its original log file size (if half of their original size is greater than 5MB)

    The code is below. But while executing the code it is keeps on running indefinitely. can somebody throw some light what cause it to go into an indefinite loop.

    Thanks
    Babu

    code:
    ====
    DECLARE db_file_name CURSOR FOR
    SELECT name , ceiling(size/128.0) * 0.5
    FROM master..sysaltfiles
    Where rtrim(lower(filename)) like '%.ldf'

    DECLARE @f_name varchar(128), @new_f_size int

    OPEN db_file_name
    FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
    WHILE @@FETCH_STATUS = 0
    BEGIN
    If ( @new_f_size > 5)
    BEGIN
    DBCC SHRINKFILE ( @f_name, @new_f_size )
    FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
    END
    END
    CLOSE db_file_name
    deallocate db_file_name

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try this:

    DECLARE db_file_name CURSOR FOR
    SELECT name , ceiling(size/128.0) * 0.5
    FROM master..sysaltfiles
    Where rtrim(lower(filename)) like '%.ldf'

    DECLARE @f_name varchar(128), @new_f_size int

    OPEN db_file_name
    FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
    WHILE @@FETCH_STATUS = 0
    BEGIN
    If ( @new_f_size > 5)
    BEGIN
    DBCC SHRINKFILE ( @f_name, @new_f_size )
    END
    FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
    END
    CLOSE db_file_name
    deallocate db_file_name
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, this is a revised version, that takes into account the possibility of entries in sysaltfiles while database may not be present for whatever reason:

    Code:
    create procedure sp_shrink_db_file as
       DECLARE db_file_name CURSOR FOR
       SELECT name , ceiling(size/128.0) * 0.5
          FROM master..sysaltfiles
          Where rtrim(lower(filename)) like '%.ldf'
       
       DECLARE @f_name varchar(128), @new_f_size int
       
       OPEN db_file_name
       FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
       WHILE @@FETCH_STATUS = 0 BEGIN 
          If ( @new_f_size > 2) BEGIN
             if exists (select 1 from (
                select 
                   IsDetached=DATABASEPROPERTY(d.name, N'IsDetached'),     
                   IsShutdown=(case when DATABASEPROPERTY(d.name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(d.name, N'IsShutdown') end),     
                   IsSuspect=DATABASEPROPERTY(d.name, N'IsSuspect'), 
                   IsOffline=DATABASEPROPERTY(d.name, N'IsOffline'),     
                   IsInLoad=DATABASEPROPERTY(d.name, N'IsInLoad'),     
                   IsInRecovery=(case when DATABASEPROPERTY(d.name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(d.name, N'IsInRecovery') end),     
                   IsNotRecovered=(case when DATABASEPROPERTY(d.name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(d.name, N'IsNotRecovered') end),     
                   IsEmergencyMode=DATABASEPROPERTY(d.name, N'IsEmergencyMode'), 
                   IsInStandBy=DATABASEPROPERTY(d.name, N'IsInStandBy'), 
                   [has_dbaccess]=has_dbaccess(d.name),     
                   d.status, 
                   d.category, 
                   d.status2 
                   from master.dbo.sysdatabases d (nolock)
                   inner join master.dbo.sysaltfiles saf on d.dbid = saf.dbid and saf.name = @f_name) x
                where IsDetached|IsShutdown|IsSuspect|IsOffline|IsInLoad|IsInRecovery|IsNotRecovered|IsEmergencyMode = 1)
                print 'Database is not accessible'
             else begin
                print 'Shrinking ' + @f_name
                DBCC SHRINKFILE ( @f_name, @new_f_size )
             end
          END
          FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
       END
       CLOSE db_file_name
       deallocate db_file_name
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    michigan
    Posts
    17
    Hi Rdjabarov,

    Thanks for your detailed reply and additional time spent in adding some intelligence to the script for DB status checking. Only problem is before the DBCC command the relevant database needs to be in USE (might be a restriction for DBCC), so I am trying to find a solution for that by getting the name from sysdatabases linking sysdatabases.dbid = sysaltfiles.dbid, but dont know how to USE the dbname before DBCC shrinfile command.

    But your reply helps me why it was into infinite loop.
    Many thanks.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    RDJabarov
    GO man GO!!!!

    WOO HOO!!!!!

    i have to go to birmingham next week but when i get back.
    it's margaritaville.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by contactrb
    Hi Rdjabarov,

    Thanks for your detailed reply and additional time spent in adding some intelligence to the script for DB status checking. Only problem is before the DBCC command the relevant database needs to be in USE (might be a restriction for DBCC), so I am trying to find a solution for that by getting the name from sysdatabases linking sysdatabases.dbid = sysaltfiles.dbid, but dont know how to USE the dbname before DBCC shrinfile command.

    But your reply helps me why it was into infinite loop.
    Many thanks.
    Here we go:

    Code:
    alter procedure sp_shrink_db_file as
       declare @cmd varchar(8000), @f_name varchar(128), @new_f_size int, @dbname varchar(128)
       DECLARE db_file_name CURSOR FOR
       SELECT ltrim(rtrim(name)), ceiling(size/128.0) * 0.5, db_name(dbid)
          FROM master..sysaltfiles
          Where rtrim(lower(filename)) like '%.ldf'
    
       OPEN db_file_name
       FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size, @dbname
       WHILE @@FETCH_STATUS = 0 BEGIN 
          If ( @new_f_size > 0.0) BEGIN
             if exists (select 1 from (
                select 
                   IsDetached=DATABASEPROPERTY(d.name, N'IsDetached'),     
                   IsShutdown=(case when DATABASEPROPERTY(d.name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(d.name, N'IsShutdown') end),     
                   IsSuspect=DATABASEPROPERTY(d.name, N'IsSuspect'), 
                   IsOffline=DATABASEPROPERTY(d.name, N'IsOffline'),     
                   IsInLoad=DATABASEPROPERTY(d.name, N'IsInLoad'),     
                   IsInRecovery=(case when DATABASEPROPERTY(d.name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(d.name, N'IsInRecovery') end),     
                   IsNotRecovered=(case when DATABASEPROPERTY(d.name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(d.name, N'IsNotRecovered') end),     
                   IsEmergencyMode=DATABASEPROPERTY(d.name, N'IsEmergencyMode'), 
                   IsInStandBy=DATABASEPROPERTY(d.name, N'IsInStandBy'), 
                   [has_dbaccess]=has_dbaccess(d.name),     
                   d.status, 
                   d.category, 
                   d.status2 
                   from master.dbo.sysdatabases d (nolock)
                   inner join master.dbo.sysaltfiles saf on d.dbid = saf.dbid and saf.name = @f_name) x
                where IsDetached|IsShutdown|IsSuspect|IsOffline|IsInLoad|IsInRecovery|IsNotRecovered|IsEmergencyMode = 1)
                print 'Database <<<' + @dbname + '>>> is not accessible'
             else begin
                print 'Shrinking ' + @f_name + ' for database ' + @dbname + '...'
                set @cmd = 'use ' + @dbname + '; DBCC SHRINKFILE (' + 
                   @f_name + ', ' + cast(@new_f_size as varchar(25)) + ') with no_infomsgs'
                exec (@cmd)
             end
          END
          FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size, @dbname
       END
       CLOSE db_file_name
       deallocate db_file_name
    go
    Last edited by rdjabarov; 07-08-04 at 04:37. Reason: forgot to change the name of the stored procedure
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Ruprect
    RDJabarov
    GO man GO!!!!

    WOO HOO!!!!!

    i have to go to birmingham next week but when i get back.
    it's margaritaville.
    Ala-freaking-bama...Remember 'My Cousin Vinnie'?

    Hey, give me a call, man, we need to seriously drink, hey?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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