53

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?

Jonathan Beerhalter
  • 7,229
  • 16
  • 68
  • 78
  • 1
    see [this answer](http://stackoverflow.com/questions/40402/what-is-the-command-to-truncate-a-sql-server-log-file/40420#40420) on a similar question – Christian Hagelid May 06 '09 at 13:32
  • Possible duplicate of [How do you clear the SQL Server transaction log?](https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log) – underscore_d Apr 18 '18 at 13:37
  • What is returned in the log_reuse_wait_desc column of sys.databases for your db? It will tell you why you cannot shrink the log. Though to be honest 2GB isn't that large for a log, and if disk space really isn't a problem and you do not have any mismatched VLF size issues, might be best to just leave alone. https://www.google.com/amp/s/www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/amp/ – Matthew Darwin Jul 28 '19 at 09:38

6 Answers6

50
  • 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)
underscore_d
  • 6,309
  • 3
  • 38
  • 64
banny
  • 859
  • 7
  • 12
  • 2
    The recovery type of my database was FULL and I couldn't find a way to change it via the GUI in management studio 2014. This answer solved that problem. The 12GB log file is now back to 1MB, thanks a lot! – Mixxiphoid Oct 04 '16 at 10:21
  • 2
    If your RECOVERY model was FULL, you need to remember to leave back with "ALTER DATABASE Database_Name SET RECOVERY full" – Angel M. Jan 02 '19 at 21:47
  • 1
    only after the last query (DBCC SHRINKFILE (Database_Name_log , 1)) my .ldf file shrank :) – AlvaroV Mar 13 '19 at 08:48
  • are all of those steps necessary? I dont know why you would switch to simple and full then back to simple again – rdans Aug 08 '19 at 08:28
  • Have to put the log file name in square brackets if it contains special characters. e.g. DBCC SHRINKFILE([ACCOUNTS_V3.3_log] , 1) – Paul McCarthy Feb 08 '21 at 10:38
39

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!

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
11

This is one of the best suggestion in which is done using query. Good for those who has a lot of databases just like me. Can run it using a script.

https://medium.com/@bharatdwarkani/shrinking-sql-server-db-log-file-size-sql-server-db-maintenance-7ddb0c331668

USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO
TPG
  • 2,811
  • 1
  • 31
  • 52
5

  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.

Richard
  • 106,783
  • 21
  • 203
  • 265
  • 1
    How I do I use dbcc shrinkfiles in Management Studio? Is that any different than selecting Tasks->Shrink->Files? Because I've done that and the file doesn't get any smaller. Also, I can easily set fogbugz to simple and just solve the problem, but I feel like I'm missing something here, like the log file should go down in size and its not. – Jonathan Beerhalter May 06 '09 at 13:33
  • @WindyCityEagle: I would suggest reading up on SQL Server backups in BOL, there the details are explained. Quickly: in full backup mode all transactions are retained in the log files until a backup of the log file is performed, then that space can be reused. This makes sense in many production environments (backups always absolutely consistent), but in simpler cases is too much effort. So simple mode works. – Richard May 06 '09 at 13:39
  • @WindyCityEagle: "dbcc..." is what you use in SQL (from whatever client), in management studio it is Tasks | Shrink | ... but that's just a GUI wrapper around the dbcc command. – Richard May 06 '09 at 13:40
  • @Richard: In general you're correct (about how and when the log gets truncated). However, there are things that can prevent the truncate from happening (open transaction, unreplicated transactions, etc.) – Adam Robinson May 06 '09 at 14:31
4

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.

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

hdoghmen
  • 3,175
  • 4
  • 29
  • 33
Nick Kavadias
  • 7,542
  • 2
  • 37
  • 46
2

I had the same problem, my database log file size was about 39 gigabyte, and after shrinking (both database and files) it reduced to 37 gigabyte that was not enough, so I did this solution: (I did not need the ldf file (log file) anymore)

(**Important) : Get a full backup of your database before the process.

  1. Run "checkpoint" on that database.

  2. Detach that database (right click on the database and chose tasks >> Detach...) {if you see an error, do the steps in the end of this text}

  3. Move MyDatabase.ldf to another folder, you can find it in your hard disk in the same folder as your database (Just in case you need it in the future for some reason such as what user did some task).

  4. Attach the database (right click on Databases and chose Attach...)

  5. On attach dialog remove the .ldf file (which shows 'file not found' comment) and click Ok. (don`t worry the ldf file will be created after the attachment process.)

  6. After that, a new log file create with a size of 504 KB!!!.

In step 2, if you faced an error that database is used by another user, you can:

1.run this command on master database "sp_who2" and see what process using your database.

2.read the process number, for example it is 52 and type "kill 52", now your database is free and ready to detach.

If the number of processes using your database is too much:

1.Open services (type services in windows start) find SQL Server ... process and reset it (right click and chose reset).

  1. Immediately click Ok button in the detach dialog box (that showed the detach error previously).
user704187
  • 21
  • 2