How do I shrink the size of a SQL Server log file
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.*
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.
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)