Post reply

Consolidate two physical log files to one.

  • May 18, 2004 at 9:03 am

    #85112

    I have a database that has two physical log files in which transactions are being logged.  One is very small and is hardly ever used by the database for logging.

    I would like to remove this second log file entirely but SQL Server will not let go.

    Has anyone run into this and performed this task?  If so, how?

     

  • May 18, 2004 at 9:47 am

    #506812

    Backup the database or truncate the transaction log. Run the following statements.

    DBCC SHRINKFILE ('<logical file name>', EMPTYFILE )

    ALTER DATABASE <Database name>  REMOVE FILE <logical file name>

    Do a full backup immediately.


    TL Broadbent

  • May 18, 2004 at 10:39 am

    #506822

    I ran the following...

    DBCC SHRINKFILE (RECOMMEND_Log2, EMPTYFILE )

    ALTER DATABASE RECOMMEND REMOVE FILE RECOMMEND_Log2

    GO

    SQL Server returns the following...

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Server: Msg 5042, Level 16, State 2, Line 3

    The file 'Recommend_Log2' cannot be removed because it is not empty.

     

  • May 18, 2004 at 11:15 am

    #506825

    SQL Server will move through the log files and log to one, then the other, using virtual log files within each physical file. You need to add some transactions, truncate the log and try again. If it doesn't work repeat. Eventually you'll use up the space and commit the transactions in the small file and you'll be able to remove it.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 18, 2004 at 11:26 am

    #506828

    Did you perfrom a full backup or a log backup with the truncate_only optionprior to running teh shrinkfile command?


    TL Broadbent

  • May 18, 2004 at 11:29 am

    #506830

    WITH TRUNCATEONLY...

     

     

  • May 19, 2004 at 3:48 pm

    #507041

    Use db

    GO

    DBCC shrinkfile (db_Log1, notruncate)

    DBCC shrinkfile (db_Log1, truncateonly)

    Create table t1 (char1 char(4000))

    GO

    Declare @i int

        Select @i = 0

     While (1 = 1)

     BEGIN

      While (@i < 100)

      Begin

       Insert into t1 values('a')

       Select @i = @i + 1

      End

       Truncate table t1

         Backup log db with truncate_only

     End

    drop table t1

  • May 19, 2004 at 4:07 pm

    #507042

    I ended up shrinking the transaction log for this database in order to force the transaction log to fill up entirely (as suggested by Steve Jones).

    Once the transaction log was completely full, I expanded the size of the transaction log in order to make space for the contents of the transaction file that was about to be removed.  Once expanded, I reissued the following as suggested by "newbie"...

    DBCC SHRINKFILE (RECOMMEND_Log2, EMPTYFILE )

    ALTER DATABASE RECOMMEND REMOVE FILE RECOMMEND_Log2

    GO

    The small transaction log file emptied into the larger one and the smaller one was removed successfully.

    Thanks everyone!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply