10

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

Ron Tuffin
  • 495
  • 2
  • 9
  • 12
  • surely this is a duplicate question? – JamesRyan Feb 24 '10 at 14:22
  • I looked and could find only one that was asking a question about when the SHRINKFILE fails. At the time it did not make sense, so I posted this question. I did consider deleting the question but then I figured that there will be others that are in the same boat. If you can find a duplicate question (that actually asks the same question, not a similar one), I will be happy to delete this one. – Ron Tuffin Feb 24 '10 at 14:30
  • There are about 8 answers on the first page of a search that include it but I guess you have to know what you are looking for. I see it so regularly as part of an answer, I was surprised that it has not been asked in such a straight forward manner. – JamesRyan Feb 24 '10 at 15:02
  • The answer very much depends on the database's recovery option: simple or full? – Richard Feb 24 '10 at 15:50
  • 1
    Thanks for clarifying, Ron. Since its a dev database, you'll want to change the recovery model to SIMPLE in addition to shrinking the log file down, otherwise your problem will recur. – BradC Mar 02 '10 at 17:53

3 Answers3

11

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.

BradC
  • 2,200
  • 4
  • 25
  • 35
  • its too bad the accepted answer was so quick. This is one of the questions I use to weed out SQL admins during interviews. If they come back with backup with truncate_only that counts as 2 strikes off the bat. – Jim B Feb 24 '10 at 16:25
  • 2
    I agree that its the absolute last thing to do, shrink the file. Correct maintenance obviates the need for this. But once its big and you want it smaller, you have to shrink it. However, when shrinking, it's better to shrink the file as small as possible, then grow the file to the correct size in 8GB increments. This will optimize the number of VLFs in the file. See - http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx. – Brian Knight Feb 24 '10 at 17:24
  • 1
    Interesting link, it seems that it only applies if your trans log is over 8Gb. I think BradC's point (or at least mine) is that yes there are emergencies that will casue you to shrink your logfile but you should recognize that if you run the notorious backup/w trunc, followed by shrinkfile you've just hosed your backup chain (hope that wasn't anything important), and aside from disk space issues you've quite likely got some serious sql server issues probably from a database design perspective or an architecural one. Without fixing the underlying caue you've bought yourself some time at best. – Jim B Feb 25 '10 at 00:06
4

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)
Mani
  • 146
  • 8
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.

Brian Knight
  • 1,165
  • 2
  • 8
  • 17