SQL SERVER – Shrinking Truncate Log File – Log Full

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL

, ,
Previous Post
SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.
Next Post
SQL SERVER – Simple Example of Cursor

Related Posts

314 Comments. Leave new

  • Tom Wheatley
    March 7, 2013 5:45 am

    Very nice, worked well for my situation, thank you :-)

    Reply
  • Hi, can you please help me?

    At this moment I’m using this for purge the log files:

    USE myDB

    DECLARE @LOG_FILE VARCHAR(100)
    SELECT @LOG_FILE = UPPER(LTRIM(RTRIM(NAME))) FROM SYSFILES
    WHERE RIGHT(LTRIM(RTRIM(FILENAME)),3) = N’LDF’

    CHECKPOINT
    BACKUP LOG mYDB WITH NO_LOG
    DBCC SHRINKFILE (@LOG_FILE,TRUNCATEONLY)

    Is that correct? I need to know if it is eficient, if this command doesnt generate a performance damage to sqlserver.

    Do I need to purge tempdb in the same process?

    I run this command every 30min.

    Thanks in advance

    Reply
    • Marc Jellinek
      June 11, 2013 8:22 am

      Every 30 minutes? You are tripling or quadrupling the I/O on your disk without appreciable benefit. Why are you doing this?

      You’ve broken the backup log chain, so unless you are taking full backups, you have lost the ability to restore to a point in time or do a valid transaction log restore. Any further transaction log backups are useless.

      You’re writing to the transaction log file as transactions are submitted to the database. Then you are taking a backup with NO_LOG (removed from SQL Server 2012) . Then you do the SHRINKFILE. Then, with the next transaction, you autogrow the transaction log and start the cycle all over again.

      Since transaction log files do not benefit from instant file initialization, as you grow the transaction log file, it must first be zero’d out, then it can be written to. With each autogrow, you are pausing the database. If your autogrow increment is small, you have massive file-system level fragmentation; if it is large, you have noticeable pauses.

      Last, with NOTRUNCATE, only empty pages at the end of the transaction log file are released. If you are constantly filling up the transaction log, you are likely not accomplishing anything at all.

      Reply
      • Marc, thanks for your reply.

        I really dont use the logs, I use full backups. But what I have really noticed is when I truncate or purge the logs, my app’s performance increase dramatically, that is the reason why I truncate every 30min.

        What I need is:
        – the best scenario for truncate the logs, thinking that I dont need them
        – what I must do whit tempdb, do I need to truncate it?
        – the correct sql command (transact sql) for doing this

        Thanks again

  • A tiny layout-tip:
    The first line of code should read:
    USE
    (that makes the markup more/100% consistent) :cool:

    Reply
  • can log be full for a particular table, even if there is enough space in the drive?

    Reply
  • Hi, Please help me to find answer for the follwing:

    I am using SQL Server 2008 Standard Edition.

    1. Can i take compressed backup with Standard Edition?

    2. What is correct time to shrink the transaction log? Before or After Full backup?

    3. Is it necessary to backup transaction log if Full backup is completed successfully?

    4. If I shrink transaction log once Full backup is completed, will i loose any data?

    Reply
  • goondla siva prasad
    April 2, 2014 10:02 am

    i need to delete my records in a table as well as reduce my .logfile size how?
    plz give me replay@@

    Reply
  • Prathamesh Deshpande
    December 10, 2014 1:24 pm

    Hi Pinal,

    I want to monitor log files of all DBs. If any of the log file is grown above 80%, then it should shrink. Please provide solution. Any script?

    Reply
  • Hello Pinal,

    I want to truncate my sharepoint config database and WSS_Logging database logs the size of sharepoint_config database is growing at a pace of ~10GB every week. I have scheduled a weekly full backup. Current .ldf file size is 113GB.

    I am using SQL server 2012 with Always On High Availability feature. I am not able to convert the recovery mode from Full to Simple.

    Please suggest in my case to reduce the log file what I need to do.

    Thanks & Regards,
    Paresh Jain

    Reply
    • 1. Are you taking regular transaction log backups?
      2. Is the secondary replica synchronized?
      What do you in for below?

      Select log_reuse_wait_desc from sys.databases

      Reply
      • 1. Are you taking regular transaction log backups?
        I am taking weekly full backup using maintenance plan in sql management studio. No, separate backup for transaction log backups has been schedule.

        2. Is the secondary replica synchronized?
        While I raised this query on my secondary replica it was showing Not-Synchronizing, but later I had to go to individual database under my Always On replica. and had to click Resume Data movement. Wanted to understand after failure is it normal that we will have to individually click on Resume Data Movement? Could you suggest.

        After I clicked on resume data I took log backup and shrink the log file, the log file size was reduced drastically.

      • You are not taking t-log backups and still you are able to get the space back? I don’t believe so. I can bet my money that log backup is taken on other secondary replica.

      • Hello Pinal,

        No, I said I am taking full database backup weekly, not taking transaction log separately. And the file size got reduced as I took manual transaction log backup due to oversizing/growing size of my sharepoint config database log file. Manual backup on both primary and secondary replica helped me to reduce my file size.

        Now 2 queries:

        1. Is it mandate to take a transaction log backup apart from weekly full backup to keep my transaction log in control.

        2. Wanted to understand after failure over is it normal that on each database, we will have to individually click on Resume Data Movement, for secondary replica (primary replica got a roll of secondary after failover) to function in synchronizing mode? Could you suggest.

  • @Paresh
    1. Yes. You MUST take transaction log backups to keep LDF size under control. This is irrespective of availability group.

    2. If movement is suspended then resume is needed.

    Reply
  • Hi,

    I m trying to use the following code, but showing me the error . i m using sql 2008r2

    ‘truncate_only’ is not a recognized BACKUP option.

    use dbname
    go
    dbcc shrinkfile (xyz_log, 1)
    backup log dbname with truncate_only
    dbcc shrinkfile (xyz_log, 1)
    go

    Please solve my query, it’s very urgent for me and my client.

    Reply
    • nachope6315@gmail.com
      May 17, 2016 5:56 pm

      use dbname
      go
      dbcc shrinkfile (xyz_log, 1)
      dbcc shrinkfile (xyz_log, 1)
      go

      that will work without he backup. you can do the back up before

      Reply
  • Jimesh sutar
    April 5, 2016 1:23 pm

    Thanks a lot .

    Reply
  • Hello Team,
    We have a SQL database server and the disk space becomes full due to the transaction log file which is huge as 750 GB. And the remaining space on the disk is around 20 GB, and I’m pretty sure this gonna be full at some particular point of time.
    To avoid any future interruption on the transaction’s which are happening on daily basis, I need to shrink the file which will be relatively very small when done using the SQL server management studio. As to my knowledge this should be the procedure (select database -> Task -> Shrink -> Log and select the desired option in next window and click OK), please correct me if there is any alteration needed.
    At this point, I have 2 questions
    i) What will be the total time needed to complete the task (Log file data size is 750 GB).
    ii) While this process is ongoing will there be any transaction mismatch/lost or affect anything from user side. In parallel is there any impact gonna happen due to this shrinking.
    However, If the process is gonna take for several hours, can we make a partition/split this file to 6 or 7 sub-files depending on the size and time elapsed?
    Kind Regards,
    Thomas John

    Reply
  • Hi,
    I am getting below error,can anybody help me out

    Cannot shrink log file 2 (TestLog) because total number of logical log files cannot be fewer than 2

    i am unable to fix

    Reply

Leave a Reply

Menu