How do I decrease the size of my sql server log file?

Question

So I have been neglecting to do any backups of my fogbugz database, and now the fogbugz ldf file is over 2 and half gigs. Thats been built up over the six months we've been using fogbugz.

I backed up the database, then I backed up, and truncated the transaction log, yet the transaction log is still 2 and a half gigs. I did a shrink on the log file and its still 2 and a half gigs. Nothing I do seems to shrink the file in size.

Is there anyway to fix the problem? Or is the only way back at this point to detach the database, delete the log file and then reattach with a new one?

Answer 1

1. Ensure the database's backup mode is set to Simple (see here for an overview of the different modes). This will avoid SQL Server waiting for a transaction log backup before reusing space.

2. Use dbcc shrinkfile or Management Studio to shrink the log files.

Step #2 will do nothing until the backup mode is set.

Answer 2

Welcome to the fickle world of SQL Server log management.

SOMETHING is wrong, though I don't think anyone will be able to tell you more than that without some additional information. For example, has this database ever been used for Transactional SQL Server replication? This can cause issues like this if a transaction hasn't been replicated to a subscriber.

In the interim, this should at least allow you to kill the log file:

1. Perform a full backup of your database. Don't skip this. Really.
2. Change the backup method of your database to "Simple"
3. Open a query window and enter "checkpoint" and execute
4. Perform another backup of the database
5. Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple)
6. Perform a final full backup of the database.

You should now be able to shrink the files (if performing the backup didn't do that for you).

Good luck!

Answer 3

see this answer on a similar question

Answer 4

Following code always shrinks the Truncated Log File to minimum size possible.

  USE DatabaseName
  GO
  DBCC SHRINKFILE(, 1) BACKUP LOG  WITH TRUNCATE_ONLY DBCC SHRINKFILE(, 1)
  GO

Answer 5

You have to shrink & backup the log a several times to get the log file to reduce in size, this is because the the log file pages cannot be re-organized as data files pages can be, only truncated. For a more detailed explanation check this out.

detaching the db & deleting the log file is dangerous! don't do this unless you'd like data loss