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

  • Mahesh Gohel
    May 22, 2011 7:05 pm

    We have lost some of transaction due to sudden power failure, it is possible to recover those lost transactions. We are using SQL Server 2005 with sp3.

    We even notice any problem after power was established, but after receiving complaints regarding some transactions lost we came to know that there are some of the last minute transactions are lost.

    Reply
    • Mr.Mahesh,

      You can recover lost transactions by restoring tail log backup. Before that you have to restore all logs which you have taken after full backup of your database…

      Reply
  • Dear pinal,

    kindly please tell me how to find max values among multiple columns in separate column which directly shows the max values of every row in that column .

    regards
    uttam

    Reply
  • Dear All,

    I would like to shrink my database transaction log file as I already tried by detaching database, ranaming log file and attach it again but its not allowing me to do so. Could you please tell me otherways to shrink log file.

    Thanks

    Reply
  • Hi Pinal,

    Thanks a lot for your post. I am looking for a script which monitors the log file and sends us alert to us and then automatically triggers log backup job once exceeds max threshold.

    Please help me if you have any regarding this.

    Thanks,
    KKK

    Reply
  • Gracias. Tenia una BD que se habia vuelto bien loca y por el manegement intentaba hacer esto mismo pero no lo hacia finalmente.

    Reply
  • Albin Sebastian
    August 11, 2011 3:55 pm

    Thank you Pinal,

    I just used this script. Working fine

    Reply
  • my database size is 12 gb but log file 36 gb. Recovery model is set to full.
    i want to shrink my log file to 10mb. plz tell me how it is possible without lossing any data. can i apply dbcc shrinfile command on active database?

    Reply
  • This might not be the best approach but this was how I reduce my log file.

    — First, use the below code to get the file associated with the database in question.

    USE [database_name]
    GO

    SELECT FILE_ID, name, size from sys.database_files;
    GO

    — Second, use the code below to truncate the log file

    dbcc shrinkfile(3,truncateonly);
    GO

    NOTE :
    Only perform this step if and only if you don’t need to restore from the log file you need to truncate.
    Make sure you perform full Backup immediately after truncation is perform JUST IN CASE!

    Reply
  • Phil Davenport
    August 27, 2011 2:45 am

    Here’s what works for SQL 2008.

    ALTER database eConfigDev SET RECOVERY SIMPLE
    GO

    Then confirm that it’s set to Simple (no logs used for recovery)

    Select name, recovery_model_desc from sys.databases
    GO

    THEN, right-click on database, Tasks, SHRINK, Files, select log file, set it to maybe 1MB or even 0mb

    Here’s the article that worked (took me quite a few before I found one that actually told me how to do it)

    Reply
  • Hello
    in sql2008r2 “WITH TRUNCATE_ONLY”
    BACKUP LOG dbLog WITH TRUNCATE_ONLY
    is deprcated command
    how can shirnk the log of db after backup
    best regards


    GO
    Use dbLog
    if exists
    (select name from master.sys.databases where recovery_model = 1 and name = ‘dbLog’)
    begin
    if exists
    (Select * from
    (
    select dateadd(n , 90, max(backup_finish_date)) as maxLogdate
    from msdb..backupset bckset
    where database_name = ‘dbLog’ and type = ‘L’) mm
    where maxLogDate > getdate())
    begin
    BACKUP LOG dbLog WITH TRUNCATE_ONLY
    dbcc Shrinkfile (‘dbLog_Log’, 50, truncateonly)
    end
    end
    else
    begin
    BACKUP LOG dbLog WITH TRUNCATE_ONLY
    dbcc Shrinkfile (‘dbLog_Log’, 50, truncateonly)
    end

    Reply
  • Hi Praveen,

    Your suggestion was years ago but it worked. Thanks. :)

    Reply
  • I had this problem with SQL 2005 and worked like a charm!!

    Reply
  • Naresh –
    Make sure you take the FULL backup of the database after doing such. This method break the LSN chain for your transaction logs and if you forgot to take the FULL backup – all the t-Log backup after this operation will be insignificant. Means you lost the point in time recovery of the database.

    Thanks,
    Arshpreet

    Reply
  • HI Pinal ,
    Shall i delete ERRORLOG.. its growing very big..
    its about 100 GB

    Reply
  • Does Transaction Log file decrease the performance of query execution if it become full?

    Ratan Sharma
    India

    Reply
    • Yes. The query will run slow and sometimes you may get error

      Reply
      • Thank you for reply.

        my log file became 40Gb i want to shrink it to 5 Gb. I don’t want to backup of log file.
        Can i execute follwing query:-
        DBCC SHRINKFILE(log_name, 5000)

        plz help me

      • VIJAY ANAND MADHURANAYAGAM
        November 18, 2011 4:10 pm

        Hi Ratan,

        After reading many topics from Pinal I have given below the suggestions:

        1. Take the log backup first (whether you want or don’t want). Then only SQL Server engine knows it and issues checkpoints to close the already opened transactions. (You may need to take a full backup first if your DB is recently restored and not taken the FULL backup till now)

        2. USE ;
        GO
        — Truncate the log by changing the database recovery model to SIMPLE.
        ALTER DATABASE
        SET RECOVERY SIMPLE;
        GO
        — Shrink the truncated log file to 5120 MB.
        DBCC SHRINKFILE (Log_name, 5120);
        –You cannot expect the same size it may be varying depending upon the nature of the logs stored.
        GO
        — Reset the database recovery model.
        ALTER DATABASE
        SET RECOVERY FULL;
        GO
        3. Take a FULL backup
        4. Take transaction log backups in a regular interval to avoid the unconditional growth (This is very best way of practicing to reduce the log growth).

        Thanks & regards,
        Vijay

  • Jignesh Nakrani
    October 6, 2011 6:44 pm

    Hi Pinal,

    I’ve SQL Express 2008 database. I have limited hard disk space on machine. I don’t care if there is log file or not. I don’t need to recover from the log file also. So I don’t want the the log file to grow bigger. Is it the best solution to execute just one command “DBCC SHRINKFILE(, 10)” periodically? or do I still need to use below three steps that you suggested originally

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

    Reply
  • VIJAY ANAND MADHURANAYAGAM
    November 16, 2011 4:35 pm

    Hi Jignesh,

    If you dont care if there is log file or not then please change your Recovery model from FULL to SIMPLE.

    If your DB’s Recovery model is SIMPLE you don’t need to use the code mentioned by PINAL.

    Thanks & regards,
    Vijay Anand Madhuranayagam

    Reply
  • maybe this is a dummy question….
    but is possible to add this code is the SSIS mantaince tool? which toolbox is the correct one

    –Shrink the truncated log file to 1 MB.
    –DBCC SHRINKFILE (, )
    –DBCC SHRINKFILE (‘DATABASE_log’,1)

    thanks in advance for your answer,

    Regars, Cris

    Reply
  • Thanks Buddy…………

    Reply
  • Hi Pinal

    I am using the SQL 2005, the log file is really big, I have found the reason and I can delete the item from the database,however what I should do with the log file, Is it possible if we can just modify the log file by items,I mean just delete the specific item in LogFile, or what I can do is just shrink or delete the log file?

    Thanks for reading and waiting for any reply.

    Reply

Leave a Reply

Menu