dcsimg
Results 1 to 6 of 6

Thread: shrinking logs when still in-use

  1. #1
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114

    shrinking logs when still in-use

    Hi, my log has grown out of proportion after performing an indexdefrag in bulk-logged mode!
    Now I cant shrink the damn thing coz I have forgotton how to shrink it when the logical log files are still in use.


    Can somebody enlighten me please. thankyou.

    error msg: "
    Cannot shrink log file 2 (MyDatabase_Log) because all logical log files are in use."

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    put the database in autoshrink mode for two days. EM. right click on database. properties.

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Backup log first, then shrink it in em. You can choose to move log to beginning of the file in em.

  4. #4
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    thanks guys.
    I turned on auto-shrink, and the log dropped 80% off the excess space. Once it reaches the size Im happy with, I will turn off the auto-shrink.

    I was told by my past managers that shrinking the db/log files was not recommened using EM nor using 'Auto-Shrink' on.

    What are your opinions?

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    Depends on the amount of traffic in the database and how often it would go up or down. In a very busy database with a lot of inserts and deletes, you can end up with a lot of resizing events throughout the day, both up and down. Resizing blocks other traffic while resizing!!

    Soemone had accidentally enabled autoshrink on one of our super-busy databases at work, and the autoshrink was being blocked by replication and autoshrink was blocking averything else.

    On all but 3 of our databases, we have autoshrink and simple recovery enabled.

  6. #6
    Join Date
    Feb 2003
    Location
    Aussie Land
    Posts
    114
    thankyou RawHide.
    The experiences that you have encountered will no doubt help me make my decisions on the database options alot easier.

Posting Permissions

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