Home / server / Questions / 116241
Accepted
Ron Tuffin
Asked: 2010-02-25 06:01:22 +0800 CST

How do I shrink the size of a SQL Server log file

  • 772

I can't figure out how to shrink the size of the databases ldf file.

The DBA says I should use backup log dbname with truncate_only

And while that looks like it executed correctly in SQL Query Analyzer the ldf file is still over 2 Gb.

**Clarification based on some comments and some answers below.***The specific database in question is a database is on my laptop and I use it for development processes only. The log file was growing to a point where the looked to cause a full disk. There is no production risk involved. I understand that the method in the question I asked and the answer I accepted are risky in a production environment.*

sql-server log-files shrink

3 Answers

  1. Best Answer

    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:

    1. Your database is in full recovery mode, and it should really be in simple mode
    2. Your database is in full recovery mode, and you should be taking regular log backups
    3. Your database is in full recovery mode, and your log backups are failing for some reason
    4. 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.

    • 11

  2. after doing the "backup with truncate_only" you should issue following command to shrink

    dbcc SHRINKFILE (logfilename,shrink_tosize)

    e.g

    dbcc SHRINKFILE (mydatabase_Log,512)
    • 4

  3. The script you wrote above will mark the log contents for reuse. Follow that script with:

    USE <database>;

DBCC SHRINKFILE (<log logical file name>)

    That will shrink it down for you.

    • 3