How do I decrease the size of my sql server log file?
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?
4 Solutions collect form web for “How do I decrease the size of my sql server log file?”
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:
- Perform a full backup of your database. Don’t skip this. Really.
- Change the backup method of your database to “Simple”
- Open a query window and enter “checkpoint” and execute
- Perform another backup of the database
- Change the backup method of your database back to “Full” (or whatever it was, if it wasn’t already Simple)
- 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).
Perform a full backup of your database. Don't skip this. Really. Change the backup method of your database to "Simple" Open a query window and enter "checkpoint" and execute Perform another backup of the database Change the backup method of your database back to "Full" (or whatever it was, if it wasn't already Simple) Perform a final full backup of the database. Run below query one by one 1- USE Database_Name 2- select name,recovery_model_desc from sys.databases 3- ALTER DATABASE Database_Name SET RECOVERY simple 4- DBCC SHRINKFILE (Database_Name_log , 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.
dbcc shrinkfileor Management Studio to shrink the log files.
Step #2 will do nothing until the backup mode is set.
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