shrinking logs when still in-use

[KingSexy182]

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."

[MAK]

put the database in autoshrink mode for two days. EM. right click on database. properties.

[rmiao]

Backup log first, then shrink it in em. You can choose to move log to beginning of the file in em.

[KingSexy182]

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?

[Rawhide]

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.

[KingSexy182]

thankyou RawHide.
The experiences that you have encountered will no doubt help me make my decisions on the database options alot easier.