SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Note: Please read the complete post before taking any actions.

This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:

“Hi Pinal,

If you could remember, I and my manager met you at TechEd in Bangalore.

We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.

The error was:

Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

The code was:

DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)
GO

I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Configuration of our server and system is as follows:

[Removed not relevant data]”

An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.

“Hi Mr. DBA [removed the name]

Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'
GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel‘s two posts here and here and Brent Ozar‘s Post here.

Kind Regards,
Pinal Dave”

I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.

Update: Small typo correction and small detail corrected based on feedback.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

69 thoughts on “SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

    • 1. There are (still) log reader tools out there that might allow you to check the transaction log for commands and undo some of them, DELETE being such a case.

      2. Usually, a backup is restored under a different name and you will use a SELECT INTO command, to copy the record from the restored database to the old one. This technique assumes that you have a backup management policy in the first place.

      3. Please try to be more specific next time, because there are a plethora of solutions for a plethora of scenarios :-)

      best regards,
      calin

  1. Hi Pinal,

    I would like to tell you that shrinking the database log file doesn’t cause database fragmentation.

    • Hi Prabhankaran,

      Yeah. Shrinking the database file or database creates the fragmentation.

      In case of log, if proper backups are taken they will not grow this big.

      Kind Regards,
      Pinal

    • Hello sir,I am facing a problem in a database sql server 2008 which is very slow to load the application in windows forms.I manually increased the size of mdf and ldf file but I dont know the proper way too improve the performence in database tuning I also did the tuning advisor no use.I working.as a windows developer and I am struggling every day with this issue.
      please help me

  2. Hi,
    If I have a DW Database that generate a 20GB Log file some times.
    In SQL Server 2005, I use “backup log with truncate only” and after “shrinkfile”. In SQL Server 2008 I will need realize backup to disk and after delete the file?

    Thanks

  3. Thanks Bryan,

    In SQL Server 2005, A database with simple recovery model generate a Log with more than 20 GB with a great insert. This isn’t minimaly logged.
    The table already have data and index.

      • And then SHRINK ONLY THE LOG FILE. But if its know that the application will need that logs space, then its recommended that the space is reserved for the log file. However in a BI case, I would manually grow the log before the ETL process, and shrink ONLY THE LOGFILE after with SHRINK FILE with the name of the logical log file.

  4. if autogrow option is enable means is it effect the performance?
    WHat is best method to follow when mdf and ldf files are growing?

  5. Hi,

    What about changing the database model from Full to Bulk-Loged then do some bulk insert operations then switch it back to Full, does it case the 2 points mentioned at the end of this post?

    Thanks

  6. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

    • Dear pinal,

      As i am going through the space issue in my server and my database has a grown bigger so i had deleted some tables from the database which were not necessary .now i have 6 gb unallocated space availlable in my db i shrink the db and my db recovered around 4 gb spaces as after doing R&D in my db i came to know that my 2 gb spaces is been occupied by the log file .
      as my platform is sql server 2008 i can’nt truncate the logfile but as i am taking regular log_backup every 1 min.i want to make space free of log file.kindly suggest how to recover the 2 gb space or what to do.

  7. @Fabricio: I highly recommend either do the insert in batches, or leave the log as is, after it has grown. SQL Knows how to handle internally your transaction log and what to clear in case of the SIMPLE recovery model. If you constantly shrink a log file that is doomed to grow again, you might end up having physical fragmentation (check your transaction log file with a defragging tool).

    @kishore: you want to check a feature called instant file initialization (google is your friend) for the auto-grow of the data file (and only the data file!!). As for the log file, simply define your database to have a large enough transaction log to play with. Unfortunately this also relates to your recovery model (namely transaction log management), so I have no idea what to say about what size is the correct size for the transaction log. No, 25% of the data file, as Microsoft used to recommend is not quite the best starting point.

    @Smith: the only major issue is for switching from either FULL of BULK-LOGGED to SIMPLE, or vice-versa, from SIMPLE to either FULL or BULK-LOGGED. Switching from FULL to BULK-LOGGED has no effect on the backup chain whatsoever (the dreadful log sequence number, aka LSN chain). But please keep in mind that BULK-LOGGED minimally logs some operations, and that’s all. Because there are some special pages that are affected by bulk operations, the next transaction log backup has to reflect those changes, so expect it to be bigger than you would normally think.

    @Pinal: apologies if I replied to some of the questions, I took the liberty of doing this because I’m sure you don’t have the time to do it right now.

    best regards,
    calin

  8. sir, i am working in Oracle 10g i want record every transaction (INSERT,ALTER,DELETE,…) in log file it is possible throw oracle. if possible please explain with examples.

    Yours
    SIVANANDA REDDY

  9. Pingback: SQL SERVER – Shrinking NDF and MDF Files – Readers’ Opinion Journey to SQL Authority with Pinal Dave

  10. Hi,

    I have a scenario where I have 2 SQL servers and a db mirrored between them. I need to backup and truncate the log file, preferably with sql job instead of doing it manually. I see the backup with truncate option has been removed from SQl 2008 unless you do a manual tranlog backup then the option still exists.
    Is there some code I can run to emulate transaction log truncation with a backup?
    I can’t change the recovery model to simple because I will loose the mirror connection if I had to do this.

    Thanks, Shawn.

  11. Hi Pinal,
    I am permanent reader of your blog, I am fresh as a DBA.We migrated our DB from SQL server 2005 to 2008. At SQL Server 2005 size of log file was 27GB but at 2008 it increases up to 300GB and DB is pretty Slow now.Please tell me the ideal size of log File and how to reduce the log file size.We are using Full back backup option.
    I need help in this case.
    Thanks.

    Azhar Iqbal
    DBA Lahore, Pakistan.

  12. I have to write code to get backup in log file
    The code is
    —————
    Backup LOG profusion_dev
    To Disk = ‘E:\SQL_Backup_LOB\back_up_files\prof_dev_log.bak’
    I got an error this
    ———————–
    Msg 4208, Level 16, State 1, Line 1
    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP LOG is terminating abnormally.

    Plz any body correct this coading

    • Hi Dinesh.,
      Check the recovery model of the database ‘profusion_dev’.
      If the database is in ‘Simple’ recovery model, it will not allow ‘Log Backup’.
      Because, the log file is consequently truncated, and there is nothing to backup.

      Regards,
      Riyaz

  13. Hi guys,

    I have followed the recommendations and started backing up my database hosted in MSSQL 2008R2 once a day in with a maintenance plan that does this:

    1. full backup of the DB
    2. shrink DB
    3. History cleanup

    Comment: in the Studio Manager 2008 R2 you have the possibility to check the T-SQL statement in each maintenance plan step you plan to implement.
    On the step 2 (shrink DB) I see the statement is:

    USE [myDBname]
    Go
    DBCC SHRINKFILE (N’myDBname’, 10, TRUNCATEONLY)

    … hm so this means that the maintenance plan is actually using TRUNCATEONLY even though Microsoft suggest stopping using this practice. I’ve stopped the scheduled DB maintenance now, because after 2 runs my LDF file is already 7GB bigger than before, so bad news for me.

    So my final question to you guys is: how do I “properly” backup the transaction log? Maybe I mustn’t use a full DB backup and shrink, but other option. I hope you can shine some light. Thank you in advance.

    Lucian

  14. I’m in the same boat here. 2008 SQL. I use Symantec BE2010 and also backup using a Maint plan to external HD. My ldf files are growing without bounds. None of the dbcc shrinkfile commands work. Symantec tells me to switch to full from simple recovery. I have tried both no luck. What is the OFFICIAL way to full backup and truncate the logfiles every night so they do not grow and remain small. Please don’t leave any syntax out if you have a script. I have tried many and none work with 2008 SQL. Either bad syntax or just don’t work saying no longer supported.

  15. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  16. Dear Pinal,
    I have some question.
    1. We have SQL 2008 R2 installed. Our system use DPM to backup SQL database periodicaly and other important data. Currently we use full recovery mode to all database and it made log file grow rapidly and create insufficient disk space error. Could we change the recovery mode to simple?
    2. What is the difference between DPM and SQL backup?

    thank you.

  17. dbcc shrinkfile(x_log,1)

    backup log x to disk = ‘nul:’ –with truncate_onlyto disk = ‘nul:’

    dbcc shrinkfile(x_log,1)

  18. due to low disk space , i didn’t configure log backup. But Database is in full recovery model. For this reason log file size goes huge every week. Shall I shrink log file weekly once.. OR What i Want to do before shrnk the log file.

  19. Hi Pinal,

    due to low disk space , i didn’t configure log backup. But Database is in full recovery model. For this reason log file size goes huge every week. Shall I shrink log file weekly once.. OR What i Want to do before shrnk the log file. If i shrink log file weekly once will it make physical fragmentation?

  20. Hi Pinal,

    Can you please help me. I want to use check point with my job, actually when my job run CPU takes 90-96 % usages which is not good, So please suggest what is the best solution to resolve this issue, so that the job not take a huge CPU usages.

    Regards,
    Sunil Kumar Kaushal

  21. i have SQL Server 2008 DB. usually was not performing log backup. now log size is grow to 60 GB and eating drive space. in order to gain space need to shrink log file.

    My database is in FULL recovery and can not change it to simple because i m using sharepoint 2010 database and simple mode is not support in production.

    for that i took log full back.
    truncate_only is not supported at SQL Server 2008. so i shrink file using management studio interface when i select log file and click to shrink screen close quickly without giving any error or message. and files does not shrink and its size is still same. please tell how to shrink file and get drive space back. thx.

  22. Pinal,
    I see the below mentioned code on the MS site,and according to the below mentioned code we can toggle the recovery model, so what is the best method to shrink the log file in 2008?

    Shrinking a log file to a specified target size
    The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

    Transact-SQLCopy
    USE AdventureWorks2008R2;
    GO
    — Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    — Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    — Reset the database recovery model.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO

  23. We have a database of 3 Terabytes. its set to Simple mode. Since we were running out of space therefore one of our junior DBA truncated few tables which were no more required. These were 10 tables which were being used for archive purpose.

    the data files were placed on different drives which were running out of space. after executing truncate table commands. The database is showing lot of space that can be freed (off coarse). But we are unable to Shrink database. It either takes too long or gives timeout.

    What is the quickest way ?

  24. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  25. Hi Pinal,
    Sir, I have application on sql server 2008 r2, on a daily basis as a process of End of day, we move data from couple table to archive them and delete the data from the database.

    Question:
    How do truncate the log ( as we take full back every day during Night)
    How to Free the unused space in the data file.

    Please advice, Thanks in advance.

  26. I have implemented code, virtually identical to what you mentioned in the first section where I am altering the recovery model to simple, shrinking the log file, the setting it back to full. In our case, this is only on our production (live) servers. This works fine for us when we need to shrink down excessively large log files to recover hard drive space. But everything I am reading is telling me this is bad & causes fragmentation. Okay. Makes sense.
    We make nightly backups of our database and log files (in our production/live environment. In development, we only do database backups because in that environment our recovery model is set to SIMPLE.)

    So I went to your next suggestion of “BACKUP LOG [TestDb] TO DISK = N’C:\Backup\TestDb.bak'” and tried that with our scenario. Unfortunately, the log file backup that gets created is ~27k-40k in size, this coming from a 97gig log file. That cannot be right. And the 97 gig log file does not shrink down at all.

    Am I missing something?

    I read Brent Ozar’, and Paul Randel’ sections as you suggested. In my opinion, the weren’t as cut & dry as yours was, although I’m a big fan of Brent Ozar & have watched many of his videos.
    Not sure what to at this point. My backups, despite being done daily, do not seem to be shrinking log files down to a manageable size & the SHRINKFILE comes into play with me more often then I am comfortable with.

    Thank you for your time.

  27. dear pinal,
    One of our sql server 2008 server, database log file cosume total dsk space around 150gb space,i am trying to backup log with truncate_only ,show error its not a prefered option.as said aboue if we take log backup to specific loction(BACKUP LOG [TestDb] TO DISK = N’C:\Backup\TestDb.bak’)
    how much space we need for 150 gb log space.In our server only 17gb space available in C drive remaining drives are full.Recovery model is full if i change it simple there might be a data loss is there any other options to get back server space.
    Please give me better suggesion .thanks in advance (shirnking log file also not happening, log_reused_space in sys.database is LOG-BACKUP)
    please help me.

  28. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  29. Below commands works well

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

  30. Hi there, been reading your site for a while now, great work & easy to follow! :-)

    I had a bit of an issue with a new SQL 2012 setup, it somehow managed to get the Log files set to minimum size of 12GB… not sure how, I used the below to shrink it down (After backing up the Database of course)

    USE
    GO
    DBCC SHRINKFILE(_log, 1)
    BACKUP LOG to disk =’nul’
    DBCC SHRINKFILE(_log, 1)
    GO

    So now the log files are initial size of 1mb again, my question is… do you have any ideas on how it might of got the initial size to be so large? My understanding of this value is it should remain at what you set it to, & when you backup the log files, shrink back down to this size.

    For reference this is a 5GB Database with only minimal changes per day.

  31. I also faceed the problem of

    Msg 155, Level 15, State 1, Line 1
    ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

    Then I used the second query. It works fine for me.
    Thanks a lot

  32. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  33. Hi pinal, i am more happy by seeing this blog having good stuff..my problem is
    my data base mdf size is around 75 gb and available size is around 50 gb . when i am trying to shrink it is getting the error 1222. can i check with smaller size or is there is any solution to shrink the db. please help me to resolve in best method.

  34. This works for me perfectly. I have used Pinal’s query, but it does not works for me. Recently from msdn I got this and it works upto my satisfaction level. My DB file now reduced to 1mb. Here is the query-
    –This is for Back-up
    — SELECT PROPER DATE BEFORE TAKE THE BACK-UP

    BACKUP LOG [GBKSMART] TO DISK = N’C:\Backup\DBName_log.bak’

    –**** Shrinking Database

    –USE [DBName];
    GO
    — Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE DBName
    SET RECOVERY SIMPLE;
    GO
    — Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (DBName_Log, 1);
    GO
    — Reset the database recovery model.
    ALTER DATABASE DBName
    SET RECOVERY FULL;
    GO

    Thank You.

    • If you keep doing this, dont forget to make a new full backup of your database. When you change your database to simple, you just truncate your TLog every transaction commited. When you change back to full, you must make a full backup of the database that will keep the transactions in the TLog since your full backup.

      Regards

  35. Hi Pinal, i am facing space issue in SQL Data and in sql logs for microsoft sql server 2008. Due to this so often i have to shrink the databases but inspite of shrinking for 2-3 hours i am still able to free only 5-6 gb of space. In sql data i have files like .ndf, .mdf,.ldf . so could you suggest some better way to shrink this files .
    Thanks

  36. I have a database task setup to take log backups every hour during the day and a task to do a full backup nightly (SQL Server 2012). My log ldf keeps growing, should i change how i am taking backups or shrink the log files?

  37. i have done test database its successfully executed log file size has been reduced ..shall i execute live database without any upshot?? pls advice me

  38. I have two SQL Server 2008 R2 DB Log file (in FULL recovery model) of size 60GB and 9GB respectively, I found that even after using the SQL Server Management Studio UI to backup the transaction log files, their size did NOT reduce significantly but only reduced by around 1%.

    What should I do ?

  39. Hi, I have always found solutions from this site. Need your help to shrink the .LDF file of a database in SQL Server 2008.I did run the scripts below but my .LDF never shrinks. Also, I have read in many blogs to avoid changing the recovery type from simple to full and vice versa as it would corrupt the database.

    Need your help at the earliest as I am in a very critical situation.

    — Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE
    SET RECOVERY SIMPLE;
    GO
    — Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (, 1);
    GO
    — Reset the database recovery model.
    ALTER DATABASE
    SET RECOVERY FULL;
    GO

  40. Hi, I am having some issue in SQL Server 2012 Miorroring. Can any body help me out here over the mobile. My No is : +91 9711842009.

    Regards;

    Raj

    • I found that if issue this command after a database backup it reduce the log size but it does not return the empty space back to the Windows as free space. You need to issue also DBCC SHRINKFILE (Logical Log File) after that.

  41. It is possible to shrink log file without truncate option. I need the script for shrink the log file without truncate.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s