Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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?

like image 707
Jonathan Beerhalter Avatar asked May 06 '09 13:05

Jonathan Beerhalter


People also ask

How do I free up log space in SQL Server?

Change the database from full to simple, force a checkpoint and wait a few minutes. The SQL Server will clear the log, which you can then shrink using DBCC SHRINKFILE. ...but I have done it dozens of times without issue.

Why is my SQL log file so large?

Large database transactions, such as importing large amounts of data, can lead to a large transaction log file. Transaction log backups not happening fast enough causes the SQL log file to become huge. SQL log files also enlarge due to incomplete replication or availability group synchronization.


1 Answers

  • 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 queries 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)
like image 110
banny Avatar answered Oct 19 '22 03:10

banny