222

I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log?

Community
  • 1
  • 1
Aidan Ryan
  • 11,389
  • 13
  • 54
  • 86

7 Answers7

339

In management studio:

  • Don't do this on a live environment, but to ensure you shrink your dev db as much as you can:
    • Right-click the database, choose Properties, then Options.
    • Make sure "Recovery model" is set to "Simple", not "Full"
    • Click OK
  • Right-click the database again, choose Tasks -> Shrink -> Files
  • Change file type to "Log"
  • Click OK.

Alternatively, the SQL to do it:

 ALTER DATABASE mydatabase SET RECOVERY SIMPLE
 DBCC SHRINKFILE (mydatabase_Log, 1)

Ref: http://msdn.microsoft.com/en-us/library/ms189493.aspx

isapir
  • 21,295
  • 13
  • 115
  • 116
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • 1
    Your answer has just saved my day! I didn't know of the "right-click - Tasks -> Shrink" option. Thank you! – René Apr 20 '11 at 13:51
  • 7
    What DO you do in a live environment? Backup the logs first? – John B Aug 26 '13 at 16:19
  • 1
    I'm no DBA, but yes, I believe that backing up the log will truncate it: http://technet.microsoft.com/en-us/library/ms179478.aspx – Blorgbeard Aug 26 '13 at 22:53
  • 2
    @JohnBubriski If you're using a recovery model other than simple, the logs are the basis for recovering data or rolling back transactions. So, in production, you'll need to backup these logs first before you can shrink the log files. Otherwise, there'd be no actual recovery possibility. Unfortunately, if you're in a recovery situation, you'll have to re-load all the transaction log backups in order to fully recover the DB. Fun times, to be sure! :) – defines Aug 22 '14 at 16:07
  • 2
    in SQL Server 2012 I had to `use mydatabase` before executing `dbcc shrinkfile` – knb Sep 26 '16 at 08:52
  • 1
    Note: The log file is usually called `DatabaseName_log` but it can differ. You can find the name of the file for the current database with `SELECT name FROM sys.master_files WHERE database_id = db_id() AND type = 1` – Simon_Weaver Nov 21 '18 at 02:12
  • Also the `1` in the shrink command refers to the intended size of the log after shrinking. The default is the original log file size, which is why some answers in this question have a value and some don't. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017 – Simon_Weaver Nov 21 '18 at 02:17
  • I have seen the script runs faster than the 'Tasks -> Shrink -> Files' option. DB size is mostly 200+ GB. If I run using the GUI it takes more than 15 mins every time. But script takes max 10 secs to do this. Any idea why GUI takes time? – Rohith Nov 25 '19 at 16:00
135

if I remember well... in query analyzer or equivalent:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

DBCC SHRINKFILE (  databasename_Log, 1)
ila
  • 4,706
  • 7
  • 37
  • 41
  • 2
    This is definitely better than setting the database recovery model to SIMPLE (as in Blorgbeard's answer) because if your recovery model is FULL, you have it set that way for a reason. – Scott Whitlock Oct 19 '10 at 17:13
  • 54
    truncate_only is deprecated in SQL Server 2008 so you have to switch the db to simple recovery http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx – Justin Moore Dec 15 '10 at 22:14
  • 10
    For SQL Server 2012 this works, but without `WITH TRUNCATE_ONLY`. – net_prog Apr 26 '13 at 08:32
  • 4
    Adding to what net_prog said, for SQL Server 2012 I substituted the first line for `BACKUP LOG DatabaseNameHere TO DISK='NUL:'`. – Aaron Newton Nov 30 '16 at 05:39
  • 'TRUNCATE_ONLY' is not a recognized BACKUP option. (SQL Server 2019 RC1) – Tomasz Gandor Nov 17 '19 at 22:03
83

For SQL Server 2008, the command is:

ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL

This reduced my 14GB log file down to 1MB.

Larry Smith
  • 1,863
  • 16
  • 15
Nathan R
  • 1,190
  • 7
  • 13
  • 5
    As the question is ambiguous as to which version and the accepted answer isn't applicable to SQL Server 2008 this answer is still valid regardless of age. – James Law Nov 19 '11 at 14:46
  • Thanks, it helped me reduce a big Log file that didn't react with DBCC SHRINKFILE – Christian Navelot Oct 05 '15 at 11:13
  • 7
    Don't forget to change the recovery model back to FULL when you are done! – Dan Bechard Jul 14 '16 at 20:10
  • You should backup before doing this (or any of the other truncation options). If you do a full backup and check the 'Copy only backup' in SSMS then you don't need the log anymore. (This is just a point in time backup). – Simon_Weaver Nov 21 '18 at 02:23
39

For SQL 2008 you can backup log to nul device:

BACKUP LOG [databaseName]
TO DISK = 'nul:' WITH STATS = 10

And then use DBCC SHRINKFILE to truncate the log file.

Matej
  • 7,517
  • 2
  • 36
  • 45
  • 2
    This is the only one that ended up working in my situation... I got an error when trying to use backup with TRUNCATE_ONLY – TomXP411 Sep 23 '14 at 17:39
  • Note: this can take quite a while, even on SSD (it has to read the log to be able to discard it). For a 30GB log file on an moderately powered Azure VM it's taken 10 minutes to do 40%. Make sure to switch to the 'Messages' take in SSMS to see the percentage processed. – Simon_Weaver Nov 21 '18 at 02:03
3

backup log logname with truncate_only followed by a dbcc shrinkfile command

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
1

Since the answer for me was buried in the comments. For SQL Server 2012 and beyond, you can use the following:

BACKUP LOG Database TO DISK='NUL:'
DBCC SHRINKFILE (Database_Log, 1)
rip747
  • 9,375
  • 8
  • 36
  • 47
-2

Another option altogether is to detach the database via Management Studio. Then simply delete the log file, or rename it and delete later.

Back in Management Studio attach the database again. In the attach window remove the log file from list of files.

The DB attaches and creates a new empty log file. After you check everything is all right, you can delete the renamed log file.

You probably ought not use this for production databases.

Rask
  • 417
  • 6
  • 9
  • 5
    Never do this! There can be data in the log not yet committed to the data file. You would lose such data. – Paul Apr 08 '14 at 08:33
  • If, in your answer, you warn not to try it in production, it's not worth posting at all. – Stan Shaw Oct 06 '15 at 12:00
  • 11
    I disagree with the downvoters -- it is an option. Administrators just need to understand their scenario. For example -- there will be no 'uncommitted' data if there are no open transactions. – Gerard ONeill Nov 12 '15 at 21:42
  • 3
    This is the only solution that worked for me. My drive was filled up and I couldn't backup or shrink and nothing else seemed to be working. Thank you! – Brian Jul 24 '17 at 16:36
  • 3
    I agree; it's not a best practice but it's valuable a tool to have if you have no other options such as Brian's scenario. – ScottFoster1000 Feb 13 '18 at 04:43