Oh, the horror! Please stop telling people they should shrink their log files!
If you've gotten yourself in this situation, then one of the following cases is extremely likely:
- Your database is in full recovery mode, and it should really be in simple mode
- Your database is in full recovery mode, and you should be taking regular log backups
- Your database is in full recovery mode, and your log backups are failing for some reason
- You are running massively huge transactions that are blowing the log file up to massive sizes
The answer for each of these is as follows:
If (1), then switch the database to simple mode
If (2), then schedule regular log backups
If (3), then fix your scheduled log backups
If (4), then just don't do that :) Instead, do work in smaller batches.
Note that NONE of these require the use of the (deprecated) "backup log dbname with truncate_only"
Instead, once you do clear the log file using one of the above techniques, then shrink the (now empty) log with:
DBCC SHRINKFILE ('log logical name', 2000)
Always specify a reasonable final size, otherwise it'll shrink down to nearly 0, and the next time it is needed, will have to take the time to grow.
Best Answer
Are you taking periodic log backups? Like every 30 mins? Either start taking log backup, or change the recovery model to simple.
You can always see the reaosn which holds the log resuse in sys.databases , the log_reuse_wait_desc column: