Post reply

DBCC SHRINKFILE...

  • April 13, 2006 at 6:21 am

    #113678

    All,

    I have a production database that has a log file of about 128GB.  I need to reduce the size of this file.  Would DBCC SHRINKFILE be the way to go?  Also, is there anything that I need to do before or after running this? 

    Thanks,

    Ben

  • April 13, 2006 at 7:13 am

    #632356

    First of all, check how much space is really used in the log file, you can do this in enterprise manager->your database (in collapsed mode)->view->taskpad

    If most of the space is used, check your recovery model. You need to back up the transaction log to a file first if you are not in simple mode (usually you should not in simple mode for production server). Then shrink the file (as you said by dbcc shrink file, or by enterprise manager)

    If most of the space is not used, you can shrink the file directly.

    It's suggested to have a full DB backup after that but not necessary.

     

     

     

  • April 13, 2006 at 7:49 am

    #632379

    can you open query analyser and post the results of the following statement

    DBCC SQLPERF(LOGSPACE)

    that should let us know how to advise you to proceed

    MVDBA

  • April 13, 2006 at 8:35 am

    #632412

    Here is what the DBCC SQLPERF(LOGSPACE) returned.

    Database Name  Log Size (MB)  Log Space Used (%)  Status     

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

    Production     124251.8       0.18981564          0

  • April 13, 2006 at 8:44 am

    #632416

    then just run a dbcc shrinkfile

    or even use enterpise manager tools to shrink the file (even though they suck)

    no problems at all

    just make sure that after you shrink the file that they are set to autogrow so that any further operations that ned additional space can claim it.

    MVDBA

  • April 13, 2006 at 8:57 am

    #632422

    Returned this statement:

    Cannot shrink log file 2 (axdb_Log) because all logical log files are in use....

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

    Does the database have to be in Single-User mode to do this?

  • April 13, 2006 at 9:09 am

    #632426

    can you post the exect syntax of the shrinkfile statement

    as far as i'm aware you don't have to be in single user mode, although i have known shrinkfile to be a little tempramental

    i would advise using enterprise manager and using the shrink database option, then click on the files option and choose the log file

    click on the trucate free space from file option and click ok

    MVDBA

  • April 13, 2006 at 9:13 am

    #632430

    Have you ever run a database log backup on this? How are you invoking the shrink?

    It can slow things but the shrink is an online operation that can be run while the database is being used.

  • April 13, 2006 at 9:17 am

    #632433

    the log % used is 0.18981564% so i'm guessing a backup or switch to simple must have occurred at some point

    MVDBA

  • April 13, 2006 at 9:28 am

    #632439

    Interesting...going through EM did the trick.  The log file was successfully reduced. 

    The log is backed up on schedule every two hours via a Maint. Plan.  The recovery model is "Full".

    Thanks for all of your assisance!  You've been a great help.

Viewing 10 posts - 1 through 9 (of 9 total)

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