June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Shrinking Truncate Log File – Log Full

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
314 Comments

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

SQL Backup and Restore, SQL Log, SQL Scripts
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

  • Arshpreet
    November 3, 2010 3:10 am

    Can anyone please tell me….

    During the DBCC shrinkfile\shrinkdatabase command running on a particular database.

    1. Am i able to access that database?
    2. Will it impact any OLTP transactional querries?
    3. Under what kind of locking does this command works?
    4. Do this command needs to run in maintenance mode only?

    Will appreciate any commands/suggestion on these questions.

    Thanks in advance,
    Arshpreet

    Reply
  • regev
    November 22, 2010 3:28 pm

    Helped me too – Thanks!

    Reply
  • satishk
    December 14, 2010 7:09 pm

    hi pinal,

    I have facing huge ldf file as 45 GB. I am going to

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

    in the above scenario for what 1 stands for ?

    and for the same size how can I decide the shrink target size for ldf.

    Reply
  • Lisa
    December 17, 2010 11:19 pm

    How do you truncate the log if db mirroring is setup?

    Reply
  • Muhammad Imran
    January 3, 2011 6:25 pm

    Thanks a lot
    I tried it and it worked!

    Regards.
    Muhammad Imran

    Reply
  • Nagesh Verma
    January 7, 2011 6:14 pm

    Yup …Everything is fine but can you tell me is there any way to truncate log file automatically ..because above mentioned ways are manual process.

    One more thing what is the Demerit of Restriction of Log File Size.??

    Reply
  • Ruhul
    January 16, 2011 5:31 pm

    Hi,

    I have tried your code but shows error on Ms-SQL 2008, 64 bit. Please let us know the reason behind this error
    ==================================
    USE MBS
    GO
    DBCC SHRINKFILE(MBS_log, 1)
    BACKUP LOG MBS_ALL WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(MBS_log, 1)
    GO
    =========================================
    Error
    Msg 155, Level 15, State 1, Line 2
    ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

    Reply
  • Kajal
    February 8, 2011 8:09 am

    Hi,,
    Actually i m in turable..
    In my office we used SQL SERVER 2008..
    and for some personal resion i took a backup of my Database..That is not in our company term and condition…But i took…
    Now it show in
    SQL SERVER–> Management –> Sql Server Logs…..
    like
    ‘Database name,Date,task-Backup’…..

    I want to delete this particular Log….So wat can i do..Pls guide me…

    I have used some method also:
    i) sp_detach_db ‘groupdeals’

    EXEC sp_attach_single_file_db @dbname = ‘groupdeals’ ,
    @physname = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\groupdeals.mdf’

    ii) USE groupdeals

    GO
    DBCC SHRINKFILE(‘groupdeals_log’, 1)
    BACKUP LOG groupdeals WITH TRUNCATE_ONLY
    DBCC SHRINKFILE (‘groupdeals_log’, 1)

    iii)GO
    DBCC SHRINKFILE(Current – 2/8/2011 7:45:00 AM, 1)
    BACKUP LOG groupdeals WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(Current – 2/8/2011 7:45:00 AM, 1)
    GO

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

    But not usefull for me.. Please guide me Sir……………..

    Reply
    • Raushan
      February 15, 2012 6:22 pm

      Kajal

      use the below script for SQL server 2008. hope it will helps..

      USE DatabaseName
      GO
      DBCC SHRINKFILE(, NOTRUNCATE)
      DBCC SHRINKFILE(, TRUNCATEONLY)

      Reply
  • Lalit
    March 25, 2011 12:33 pm

    Hi Pinal

    What can i do if my SQL Server 2000 is not opening and i want to shrink the log file without using SQL Server.
    Is there any other Solution to shrink Log files other than using SQL Query Analyzer.

    Thanks in Advance

    Lalit

    Reply
  • Udayaa
    April 25, 2011 3:32 pm

    Hi Pinal,

    I did speak to you at TechEd, Bengaluru.

    I have a transactionlog file of 28 GB.
    When I ran the DBCC OPENTRAN, I get the following,

    Transaction information for database ‘iPlan_AmbaCOL’.

    Replicated Transaction Information:
    Oldest distributed LSN : (0:0:0)
    Oldest non-distributed LSN : (89755:86:19)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    As per the article, we have to kill SPID, I am not able o find the SPID in the above output.

    Kindly help

    Regards,
    Uday

    Reply
  • Padmanabha
    May 9, 2011 8:23 pm

    Hi All,

    I’m new sql server database domain.

    Could please anyone explain, what are the impacts if database file gets shrink and log files truncate in live production server(MS sql server 2000).

    It would be great help, if can provide information in order track when last trucate and shrink run on particular database.

    Thanks in Advance.

    Thanks and Regards,
    Padmanabha.D.V

    Reply
  • vasupol
    May 10, 2011 10:18 am

    I’m Use Command (on SQL 2008 it Work)

    backup log [Databases Name] to disk =’NULL’

    Reply
  • satwinder saggi
    May 10, 2011 8:54 pm

    Hi @ Padmanabha

    Shrinking causes massive fragmentation in the Database , So Avoid to use this in Production Server if situation is not critical.

    Regards
    Satwinder

    Reply
  • suneel
    May 19, 2011 5:37 pm

    what are the versione nodes in sql server?

    Reply
  • Devang
    May 20, 2011 5:22 pm

    Hi…

    Can anyone tell me, how can I able to linked two difference SQL servers. Also, Is it possible to take a backup of linked server which is installed in same network or other network at local Intalled LTO ?

    Regards,
    DevAng

    Reply
  • 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
    • Devang
      May 24, 2011 7:18 pm

      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
  • uttam atri
    June 22, 2011 10:48 am

    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
  • Hitesh
    July 16, 2011 4:12 pm

    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
  • Kiran K
    August 2, 2011 9:11 am

    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
  • Deisbel
    August 8, 2011 9:09 am

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

    Reply
« Older Comments Newer Comments »

Leave a Reply

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 90 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.