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

About these ads

308 thoughts on “SQL SERVER – Shrinking Truncate Log File – Log Full

  1. Thanks for your code for SQL SERVER – Shrinking Truncate Log File – Log Full

    I tried it and it worked!!!

    I wish there was something as plain and simple in Books online.

    Albin

  2. SQL Server 2005 Books Online:

    “[TRUNCATE_ONLY]

    This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

    We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log…”

  3. Yeah Its True JAHA …all situations depends;

    The NO_LOG or TRUNCATE_ONLY options truncate the transaction log. When you use these options, you might not receive a full database backup!!

  4. I feel that in OLTP envirnment where DML commands are running most of the time .
    it creats a lock on .LDF and doesnt allow physical update on LDF ..is it true champ then what we can do such situation .

    As you mentioned above in the article that use syntax
    USE DatabaseName
    GO
    DBCC SHRINKFILE(, 1)
    BACKUP LOG WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(, 1)

    , 1 refers to MB if i am not wrong …my question is
    1) can we set the minimum size for transaction log
    2) If we can ,then we need to mention the minimum size rather then 1 .if i am not wrong .
    3) if we simply mention the DBCC SHRINKFILE (ABC_LOG)without mentioning the Size then how much will it reduse .
    4) I found sometime command dosent work it comes with error ..

  5. 1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.

    SELECT file_id, name FROM sys.database_files

    If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

  6. 1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
    i.e.
    SELECT file_id, name FROM sys.database_files

    If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

  7. Thanx SAM
    …..! Can we Shrink LOG file while using DML commands by users !
    If yes Why , If no Why ..!

    Thanx in advance

  8. If primary transaction log file is exceed in large volume then is it good to restrict size , is there any wrong impact if we restrict
    the size.

  9. Very good question Nirav,

    If log are left to un-restricted growth they will grow bigger than hard disk space available it will crash SQL Server completely.

    The best way to handle SQL Server logs are to have un-restricted grown with frequent back up of log.

    This will also add security to your system when it fails you can restore using logs (conditionally you have full backup to begin with).

    Kind Regards,
    Pinal Dave (http://www.SQLAuthority.com)

      • @Deb,

        Shrinking is not an Operating System Operation, Its a SQL Server Process.

        If you detach your databases from SQL Server, what it means is, you are breaking the link between the database files on Operating System and SQL Server. In this scenario SQL Server have no control on database files and from SQL Server you cannot issue any command for this database files.

        So answer to your question, You cannot shrink log file if you detach database, you can only shrink when database is attached to SQL Server, because shrinking is a process of SQL Server not OS.

        ~IM.

  10. …..! Can we Shrink LOG file while using DML commands by users !
    If yes Why , If no Why ..!

    Thanx in advance

  11. Pinal ,

    If my Log Grows bigger then Disk space , will my database comes in SUSPECT MODE ..or crashes the SQL server completly .

    Scenrio :
    SQL install on C: and Log files on D: ….in this condition also can crash my SQL INSTANCE …

    Praveen

  12. Thanx Pinal Dave ….

    In both the snerios as i mentioned a previous post !!

    And offcourse i would like to thanx for this Shairpoint where we can discuss our views Thanx once again !

  13. How to truncate log file in SQL Server 2005

    SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.

    Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.

    I. Shrink the log file size at the right time

    I found out this trick:

    Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files

    set the file size, say, 1MB.

    Then, click OK and you are done.

    II. Eliminate the log file completely

    Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

    a. Detach the database

    b. Rename the log file

    c. Attach the database without the log file

    d. Delete the log file

    Let’s say, the database name is testDev. In the SQL Server Management Studio,

    Highlight the database-> Tasks->Detach..-> Click OK
    Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
    Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
    After this is done, you can verify the contents of the attached database and then delete the log file.

    • Hi There…

      I am having a problem… i have a database with 12GB log file.. and i have tried to shrink the log file which has not worked then i have tried doing the steps as suggested by Praveen Barath, but while attaching the mdf file with out ldf files. I am getting following error. Please help me to resolve this issue.

      TITLE: Microsoft SQL Server Management Studio
      ——————————
      An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
      ——————————
      BUTTONS:OK
      ——————————

      Thanks
      Ram

  14. Hi Pinal,

    I was wondering if there is a way to shrink all log files of all databases. I’m trying to do a script to acomplish this task and so far I cannot make it work.

    The problem is that you have to issue a Use DatabaseName before using the shrinkfile(Filename_log) statement. I tried using the Exec and the dynamic command execution but it doesn’t seem to work. I appreciate any help you could give me and keep up the good work!

    Regards.

    • Hi Jean Paul Craig, did you find the answer to this?
      I’m trying to do the same thing on all databases.
      Thanks in advance.
      Tom

      • Hi, Jean and Tom

        Enjob the Below Script…..

        DECLARE @DbName VARCHAR(10)
        DECLARE Crsr CURSOR
        FOR
        SELECT name FROM sys.databases
        OPEN Crsr
        FETCH NEXT FROM Crsr INTO @DbName
        WHILE @@Fetch_Status=0
        BEGIN
        PRINT ‘USE ‘ + @DbName
        PRINT ‘GO’
        PRINT ‘DBCC SHRINKFILE(‘ + @DbName + ‘_log, 10)’
        PRINT ‘GO’
        PRINT ‘BACKUP LOG ‘ + @DbName + ‘ WITH TRUNCATE_ONLY’
        PRINT ‘GO’
        PRINT ‘DBCC SHRINKFILE(‘ + @DbName + ‘_log, 10)’
        PRINT ‘GO’
        FETCH NEXT FROM Crsr INTO @DbName
        END
        CLOSE Crsr
        DEALLOCATE Crsr
        GO

        Regards,
        Nirav Raval

      • In the given script I assume that log file name must be with _log name after dbname…. as I am always having the same…. :)

  15. We recently migrated a server to a new PC image. We reinstalled the SQL server to the same versions of the original PC. After this migration our TempDB has started to grow tremendously fast.

    We have implemented a purge of the TempDB to prevent the server from crashing.

    Is there a way to see what is making the Tempdb grow?

  16. Is the SQL Server log files can help us to restore our database in original situtation if it crashesh.
    IF Yes
    HOW?? (In SQL 2000 and 2005)
    ELSE IF No
    THEN WHAT IS THE USE OF LOG FILES???
    END IF

  17. Hi
    This is Dhirendra , n have the problem in SQL Server 2000 as Log file is full while in my Databse Table not much more data.

    Thanks

    • use dbname BACKUP LOG dbname WITH TRUNCATE_ONLY
      DBCC SHRINKFILE(dbname_log,2)
      use tempdb
      BACKUP LOG tempdb WITH TRUNCATE_ONLY
      DBCC SHRINKFILE(templog,20)

  18. Pingback: SQL SERVER - Shrinking Truncate Log File - Log Full - Part 2 Journey to SQL Authority with Pinal Dave

  19. Pingback: SQL SERVER - Solution - Log File Very Large - Log Full Journey to SQL Authority with Pinal Dave

  20. Thanks pinal for code
    It is working fine

    i am ahmad from jordan.
    i need ask you that if i have machine installed sql server 2000 database
    if i want to take a backup from database then i want repair it on another machine.can i do?if yes how i can do it

    thanks and regard..

  21. 1. backup your database from machA.
    2. Copy it to machB.
    3. Restore the backup by select “file”, type in the new database name, and change the path if neccessary.
    4. Now you can repair it on machB.

    After finish repairing it, you can detach and replace the database on machA.

  22. Thanks for this tip. I used it today and it worked for me. I had a log file that was 10GB causing significant problems on my production server. Now I have reduced it to 30MB. This was very helpful. Thanks

  23. Hello,

    I saw your code and is very helpful, what I assume or understood from the code is, we are taking backup of the log file and then shriniking it with truncating_only option ( I think it realease the space to operating system, I assume I am not sure).

    Is there any way, where we can truncate and shrink the log file with out taking backup.

    Like I did some transactions for that I dont need a log backup. I want to reduce the size of log file, shrinking would be the option. But without taking backup can we shrink the file.

    I tried it many times, the size of the log file did reduced initially later when I tried it to reduce more, the usage space is increasing and the free space is reducing the total space remaining same as previous, meaning its logging every operation. Which I dont want it to do.

    Is there any way to do this !

  24. regarding my previous questions, reducing the size of log file with out taking backup.

    Can we use this :

    dump transaction Db_name with no_log

    I know Dump is for older version and is synonym to back_up as explained in this thread.

    So does this code still takes backup, I am sure this time it will not log, because I gave no_log option.

    but can we execute this statement when we dont have space on the disk, Lets say no disk space, if this code does takes backup then this is not what I am looking at ?

    Please clarify !

  25. My database (sys files) getting rename and i could not open my database and it also not porformed backup database action…………// please tell me what i do
    regards

  26. Pinal,

    Excellent tip thanks a lot!

    I had a log that was 17GB in size now its ok

    Best regards and keep up the good work!

  27. Praveen,

    I tried the detach method of shrinking a log file and it did not work. When I reattach it errors stating that the log file can’t be found.

    Thank
    Dave

  28. Hi Pinal,

    I had this problem and it took me hours trying to figure out how to reduced the size of the file and now just by copying and pasting your command and running it just did it.
    Your approach is 200% better than the article I was trying to follow on MS KB.

    Problem SOLVED!

    Thanks again Pinal. You are a legend.

    Regards,

    Peter

  29. Hi Dave O,

    Please understand that the DETACH and ATTACH methods are not meant to truncate log files.

    What Praveen told was just a workaround to get rid of a transaction log file altogether which has grown too large and is no more required because if you don’t specify a transaction log file while ATTACHING a database, one is automatically created for that database and you can delete that unwanted transaction log file which grew in size.

    DETACH and ATTACH method is ideally used to move your files (data and transaction log files, i.e. .mdf and .ldf files) around on a server. For example, if you want to move data and transaction log files of a database that are located on say X:\Data\ and X:\Log\ folders respectively, to a different drive and location, say Y:\Data\ and Y:\Log\ respectively, then you can DETACH the appropriate database and copy its .mdf and .ldf files to the target locations as shown above and then simply ATTACH the database, specifying the correct path of the files, that is, your target file locations.

    In order to truncate the transaction log files, I suggest you use the code that Pinal has already displayed in his previous blogs.

    I am pasting the code for your reference:
    USE DatabaseName
    GO
    DBCC SHRINKFILE(, 1)
    BACKUP LOG WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(, 1)

    I also absolutely agree with Pinal on having frequent transaction log backups, as the transaction log gets truncated automatically after every transaction log backup happens.

    Should you still have any confusion regarding truncating the transaction logs, you may write to me or Pinal Dave.

    Cheers!
    Munshi Verma

  30. Hi Praveen,

    How are you doing? Hmm! Nice to see you here.

    Well let me answer your question that you asked on August 7, 2007, as I see no explanation to this question of yours.

    Your question was:
    …..! Can we Shrink LOG file while using DML commands by users !
    If yes Why , If no Why ..!

    Thanx in advance
    ————————————————————————–
    Answer to your question is:
    Yes, We can shrink the transaction log files while DML commands are being executed by users on the associated database because the DML commands would affect the active portion of the transaction log file and the inactive portion can still be truncated and shrunk.

    Note that we can’t truncate the active portion of a transaction log file.

    Cheers!
    Munshi Verma

  31. Hi, Munshi Verma.

    Thanks for your reply.
    Execuse me all i was not in touch with blog for long time , cought in some production work.

    BR
    Praveen

  32. TRUNCATE_ONLY is a backup Log statement. It performs a checkpoint to manually force the transaction log to be truncated. This truncates the log by discarding everything from the log. This option frees space but risk possible data loss. After the log is truncated by using NO_LOG or TRUNCATE_ONLY in a backup log statement, the changes recorded in the truncation portion of the log are not recoverable until the next full database backup.

    Therefore for recovery purpose, after using either of this options, immediately take a full database backup. Never use TRUNCATE_ONLY to manually truncate the transaction log(especially production DB in full recovery mode) because these breaks the log chain. Until the next full DB backup, your database is not protected from failure.

  33. Penal:

    I was little confused and my result seems to be same for reducing log and data space. There is little secret running the dbcc shrinkdatabase script.

    Truncating did not help me.
    backup log ‘databasename’ with truncate_only (did not help much)

    Below is the correct way to get rid of all data and log file full issue. Now my available free space is same as file capacity.

    This works for me and reduced my log file to its full capacity.

    dbcc shrinkdatabase (N’database name’, 1, truncateonly)
    dbcc shrinkdatabase (N’database name’, 2, truncateonly)
    dbcc shrinkdatabase (N’database name’, 3, truncateonly)
    dbcc shrinkdatabase (N’database name’, 4, truncateonly)

    Best solution:

    sp_helpdb gives you fieldid in a database, thats how I got those 1, 2, 3, 4, 5 etc.

    * Find how many data & log fileid your database carry.
    * Run one by one as my above dbcc shrinkdatabase script.
    * Remember 1, 2, 3, 4, 5 is depend how many file consists of the database.

    This site is a life saver for me. Keep up the good work Mr. Penal.

    -sikander

  34. Thank you VERY much. I have been all over looking for a simple solution, and have run a number of scripts and wizards to no avail. I appreciate your easy and EFFECTIVE script!

  35. hi

    I am deepak Sharma

    i am facing log full space full problem

    My ldf file size is 90 GB and my datadase size is 20 gb. I want to delete my old ldf file while i am taking backup
    is that possible or not ?

    Is that any tecnique to delete old ldf file after taking full database backup through wizard?

    How can i overcome this problem ?

  36. Hi Sharma,

    The size of your log file tells me that your DB is in full recovery mode and you are not taking a transaction log back.. or you are running a long transaction.

    To get out this situation,
    take a full backup of your db, take a transaction log back.

    Transaction log backup(dumps) removes transaction that has commited from the log file. Once you have taken a transaction log back, Shrink your log file.

    NOTE: You can not delete your log file.

  37. Munshi Verma/Pinal Dave,

    Hi..
    Please correct me… Is it good practise to use BACKUP LOG WITH TRUNCATE_ONLY? I have also given the problem am currenly facing. Any help is appreciated.

    Currently, the production database is 85Gb but the log file grows really huge to about 200GB in a week time.

    Everytime I use the below steps:
    1.backup log with no_log
    2.shrink the log file. Right click the correct database and choose shrink file ->chosse the log -> ok

    Please give me a good solution for my above problem. As well let me know a way to automate the truncation of the log file.

    Thanks,
    Janani

  38. Hi

    i have set up a databse mirroring.

    i will like to know if i use any above option do i need to stop mirroring at the time of operation.

  39. Thank you for your helpfull sulution
    but only i make small notation in syntax by example :

    USE sherbydb
    GO
    DBCC SHRINKFILE(‘sherbydb_log’, 1)
    BACKUP LOG sherbydb WITH TRUNCATE_ONLY
    DBCC SHRINKFILE (‘sherbydb_log’, 1)

  40. thnk you for your successfull solution
    but i will add an example as a help for qutation locations:

    USE mydb
    GO
    DBCC SHRINKFILE(‘mydb_log’, 1)
    BACKUP LOG mydb WITH TRUNCATE_ONLY
    DBCC SHRINKFILE (‘mydb_log’, 1)

  41. Hello,

    After i run this statments, i take the message tha the folder is incorrect.

    The log file is in logical size now, but when i try to open a table in the database i teke the message “the system cannot find the file specified”

    Please help me

    Thanks George

  42. Also, my database name is EP14SQL, so i use the command “USE EP14SQL”. But in my sql server i have many databeses, and i see that i take this message to all tables at all databeses.

    All .mdf and .ldf files have modyfied date the current and i hope not to damage all databases.

    Please i need your help immediatly

    Thanks again

  43. Sorry for my extented posts.

    I restarted the machine and all look to work correct!!!

    I guess the problem was that when i run the statments, i was out of disk space and there was a problem with the file system.

    Thanks guy’s

  44. Hi Guys,

    I am a bit confuse in Truncate and shrinking……when we say truncating the log file what does it mean?

    As per my understanding shrining means – to compress the the log file size which is left empty. e.g. if in 100 gig log file 40gig is used and 60 gig is unused then we can shrink it upto 60 gig. Now wts truncate here and wts the + and – points of truncate as shrinking???

  45. Hi,

    I am working as a DBA, for supporting Development team, My Question is:

    I am handling different database. I Created Developer role and give it to all the developers. and another Role “Admin” created and give it to Team leads. My requirement is if the TL change any database objects, is there any way to capture the changes? like auto generated mail to my mail box? that mail should tell that, from Machine A, the object changed on particular Database….like that?

    I will appriciate, if any one give the solution…

    Thanks in Advance.

  46. Hello, I am looking for a sql query that will return the location of the logfiles, as a string. Could you please help?

  47. Hi,

    What are the correct steps to DAILY backup, with Full recovery, without loosing the transactionlog chain?

    This is what I want to do.

    Full Backup Database (Already heaving the first backup)

    Daily:
    Transaction Bakcup (23.00 hours)
    Full database Backup (23.05 hours)
    And than:
    USE mydb
    GO
    DBCC SHRINKFILE(’mydb_log’, 1)
    BACKUP LOG mydb WITH TRUNCATE_ONLY
    DBCC SHRINKFILE (’mydb_log’, 1)

    If i need to recover to a poin in time I also need to make an additional backup of the active Transactions.

    Is this sufficient or do I have a kink in the chain?

    Regards,

  48. @DBAtor
    Sorry:
    You can set up an WMI alert(SQL Server 2005) that will send an email to you when database object has changed.

  49. @DBAtor.
    sorry: This will help. just modify it
    CREATE TRIGGER [ddlDatabaseTriggerLog]
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘sysname’);
    SET @schema = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]‘, ‘sysname’);
    SET @object = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘sysname’)

    IF @object IS NOT NULL
    PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema + ‘.’ + @object;
    ELSE
    PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema;

    IF @eventType IS NULL
    PRINT CONVERT(nvarchar(max), @data);
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = ‘youremail@yourdomai.com’,
    @body = ‘somebody has made a modification.’,
    @subject = ‘MODIFICATION MADE’
    END;

    ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
    GO

  50. Hi Pinal,

    I am having issue with Replication. I have Created Replication was working great. When hard drive fail I lost distribution database. Now I am trying to Recreate Replication It will not allow me to Create Replication. I have remove replication. I tried to delete distribution database it says It is alredy using replication.
    When I query from master database using
    select * from sys.objects I can see distribution database in list.
    Please Help my on this issue.

    Thanks
    Rakesh

  51. Hi,

    I’ve a peculiar problem with the SQL Server installation on one of our servers.

    The SQL Server Agent is not visible in the Management Studio on one of our Servers. Also, the following error is getting displayed when we log in to the server :

    Could not find stored procedure ‘master.dbo.xp_MSplatform’.

    Any idea what could be the problem and how to resolve this ?

    Thanks in Advance

    Girinath

  52. Hi,

    My database is in full recovery and i regeraly take the backups

    nw i Want to shrink my log file.I want to knw wether is it necessary for me to execute

    Backup log dbname with truncate_only

    or directly execute

    Dbcc shrinkfile()

    So what is the difference between the Backup with truncateonly and shrinkfile

  53. Please help me understand. I am taking a full backup once a day, and transaction logs once every hour. Why is my LDF file not retaining a “normal” size? It continues to grow. I do not want to break the chain and use truncate only, though I have done this and it fixes the problem. I would very much like to understand the underlying problem.
    thank you in advance

  54. @LauraV.

    You did not mention which version of SQL Server you are using, Either 2000 or 2005, ( would be easy for us to narrow our answer to questions)

    When ever you take transactional log backup, no matter if you scheduled it or if you take it manually, SQL Server by default will empty transactional log. Meaning after the transactional backup ( Be Careful NOT FULL BACKUP) SQL Server will remove inactive transactions from logfile.

    Which means your transaction file is empty after you take transactional log backup, Empty doesnot mean your logfile became small, NO. the size of the log file will still be the same but it will be empty, all you have to do is shrink logfile, run below command,

    use database_name
    Dbcc shrinkfile (logfilename, 1)

    and it will shrink the log file to its minimum size possible,

    In SQL Server 2000:

    Right click database name -> all task -> backup -> in the dialog box, select transactional backup, and click on options ( at the top left side of the box) you will see “remove inactive entries from Transactional log”.

    This means when you take transactional Log backup, SQL Server is removing all inactive entries from SQL Server log file. This is default setting.

    In SQL Server 2005:

    Right Click database name->task ->Backup in the Dialog box, select backup type Transactional backup, and click options tab (at left side up) and under Transaction log section you will see “Truncate the Transaction log”.

    Which mean when SQL Server 2005 performs transactional backup it truncates logfile. This is also default setting.

    NO matter if you use SQL Server 2000 or SQL Server 2005, SQL Server by default (can be changed) will truncate log after performing Transactional backup.

    So simple solution will be shrink log file after you take transactional log backup. You dont have to do it manually You can send this as a response to this transactional bacup job, when ever this job succeed, response to that would be run this script

    use databasename
    dbcc shrinkfile ( logfile_name, 1)

    That might work.

    I will take one more minute to explain what is this active and inactive transaction in the logfile . Please correct me if I am wrong.

    This is how SQL Server works,

    1. When a transaction comes to SQL Server, it first comes to transactional log buffer, and then it is hardened to disk ( log file, .ldf ) and then it is written to data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.

    On the other hand, when a transaction comes to SQL Server and it is entered into log buffer and also in transactional log, but not yet entered in data file, its still in the process, then we will say this transaction as active transaction.

    One important point to think is, all the inactive transactions in the log file ( transactions which completed their tasks and are entered in data file) are also present in data file, and SQL Server is smart enough to think, inactive transactions are already in data file and also, they have been backed up( by transactional log backup) hence it thinks, this is the time to get rid of this data and it removes all the inactive transactions.

    But for Active transactions, which are either incomplete or could not complete because of disaster ( sudden power failure….) will be stored in transactional log and will be called acive transactions untill they are entered in data file.

    This will be little bit confusing…

    Read more about this in books online.

    Hope this helps.
    Imran.

  55. Pingback: SQL SERVER - Behind the Scene of SQL Server Activity of - Transaction Log - Shrinking Log Journey to SQL Authority with Pinal Dave

  56. Thanx Pinal Dave ….

    Thanks for your code for SQL SERVER – Shrinking Truncate Log File …

    I tried it and its working fine

  57. Hi Pinal,

    I keep on follow your blogs – really very good expla.

    1. can you tell me what measures I need to take on server level to perform my application will run fast – .NET 2.0 web application?

    2. I want to write a script to create a database – parameterized script.

    Regards
    Seema Reddy Akkala

  58. I am setting up a maintenance plan to provide daily backups for a set of new databases in SQL Server 2005. I obviously want to keep the log files from groing. Will “Full” backups of each database accomplish this? Do I need to set up a Full Backup and a Transaction Log backup?

    Thanks,
    Frank

  59. Most of these scripts have a serious (but easy to fix) flaw – they set the size of the log file to 1 megabyte!

    Emeka (#43 above) has already pointed out the DANGER of using the TRUNCATE_ONLY option – do not use that option if you require full-time availability and no data loss. It can be made to work – Kimberly Tripp recommends taking a full backup, truncating the log, and then taking another full backup. But, you have to be able to afford the downtime.

    If you are going to shrink your log, NEVER shrink it to 1 megabyte! You want to shrink it to where it should be. Otherwise, you get a very inefficiant log file with lots of internal fragmentation.

    And where should it be? In a system with proper backups of the log file running successfully and with no long running transactions, the log file IS ALREADY THE SIZE IT SHOULD BE.

    As was stated above by Emeka (#46 above), if a log file keeps growing continuously, you have another problem which needs to be fixed, before you shrink the log file.

    When you do shrink a log file that got out of control, don’t try to set it to 1 single megabyte!!! Set it to 2 or 3 times the size of the database, if you have no other clues as to the proper size.

    David

  60. Hi Pinal…

    We have an option with one of our client like disk space alert, when the usage os disk reaches 90-95% it wil create an alert and send an email to all DBA.

    All the time Critical suituation is due to growth of LOG files only, rarely on MDF files. Is there any query to find which file MDF/LDF is growing or which is adapting more space.

    Is there any way to find the file in short time period because we have to resolve it as quick as posible.

    Regards,

    Senthil kumar Rajendran.

  61. Hi,
    I have a question.
    I am going to delete a huge chunk of data for which my log files will be increasing a lot.

    I want to know is there is any kind of dml statements which will not log any data into the log files.
    Or is there is any way that the to truncate the log file before i start deleting.

    I will be deleting large chunk of data and then i will have to shrink the database as shrink file did not work that efficiently.

    Also is there is any way while i delete the tables can i disable all the constraints of that table. My deletion is done dynamically or using the ad-hoc query. So for that reason my contraints gives problem.

    I want something like this..

    disable table name constaraints.

    delete table

    enable constarain of that table.
    Thanks in advance.

  62. Palin, thanks for the info to shrink SS2000 log files. Backups failed for a while and the log files grew. Now the backups are working again but I needed to reset the log file and your instructions were very helpful!

  63. Pinal says:
    >>If log are left to un-restricted growth they will grow bigger
    >>than hard disk space available it will crash SQL Server >>completely.

    Is this really true even with recent versions of SQL such as 2008?

    It’s hard to believe that SQL treats running out of disk space so ungracefully.

    regards,
    Lee

  64. Thank you VERY much. I have been all over looking for a simple solution, and have run a number of scripts and wizards to no avail. I appreciate your easy and EFFECTIVE script!

  65. No one has put the light on the script which can run on all databases on the server.
    EX: If I have 100 Databases and want to run the command DBCC Shrinkfile on all the DBs at one go…Any suggestions/solution appreciated.
    Thanks in Advance

  66. Pinal,

    Excellent tip thanks a lot!

    I had a log that was 17GB in size now its ok

    Best regards and keep up the good work!

  67. Hi all;

    Been reading all the comments and would like to “shrink” my log file down to a respectable size and get rid of all the “white” space.

    Can I run these commands below if there has not been a backup on either the db’s or logs in quite a while?

    Also what implications are there using the NO_LOG option?
    Thanks in advance, Steve

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

  68. Hi Pinal,

    I am new to this forum. We have a DB crash down yesterday due to increasing log file ran over the disk space. We have restored the DB from the backup taken on mirrored server.

    We want to reduce the size of log file. Should we use shrink file comand? It would be helpful if you reply with the commands to be run over the SQL server 2000

    Thanks in Advance

  69. All the info sounds exactly like what I need. I am not SQL conversant.
    Where do I start with the truncate action? Where do I need to be? Command prompt?
    Server Management Studio? Server Management Configuration?

    Then what steps (right down to the key strokes) do I need to follow?

    Server has stopped responding. I have stopped the Integration Services, Fulltextsearch, reporting services, etc. My email box for requests is growing faster than I can respond.

    Please advise.

  70. Hi,

    I am unable to use the above command succesfully…
    when am trying to execute the below command:

    USE Retail_HL_Analytical
    GO
    DBCC SHRINKFILE(‘Retail_HL_Analytical_PROD_20090120_log’,1)
    BACKUP LOG Retail_HL_Analytical WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(‘Retail_HL_Analytical_PROD_20090120_log’,1)
    GO

    the following error msg is getting displayed:

    Msg 8985, Level 16, State 1, Line 1
    Could not locate file ‘Retail_HL_Analytical_PROD_20090120_log’ for database ‘Retail_HL_Analytical’.
    Msg 8985, Level 16, State 1, Line 3
    Could not locate file ‘Retail_HL_Analytical_PROD_20090120_log’ for database ‘Retail_HL_Analytical’.

    plz let me know where i went wrong in using the command…

    Thanks

  71. Another best way to truncate log file with out taking transactional backup would be,

    1. Change recovery model from Full to Simple ( using Interfaces) and then change it back to Full. By doing this your log file will be truncated.

    2. Run DBCC ShrinkFile ( ‘LogfileName’ , 1)

    By doing this, you will definitely loose your transactional log data that was stored in log file. But this will truncate Log file with out taking any backup.

    Regards,
    IM

  72. @srikanth

    try and use the Logical Name of the logfile, that you can find in the Database Properties. This name doesn’t have to be the same as the Windows Explorer filename.

  73. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  74. Hi Pnal,

    I want to truncate the log file automatically after a specific interval. Is there any way to do this?

    Thanks
    Sanjay

  75. Harsha,

    you can quickly delete your database table data with this script:


    – DELETES ALL TABLE ROWS and resets all IDENTITY fields to start from 1!

    /*Disable Constraints & Triggers*/
    exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
    exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

    /*Perform delete operation on all table for cleanup*/
    exec sp_MSforeachtable ‘DELETE ?’

    /*Enable Constraints & Triggers again*/
    exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
    exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

    /*Reset Identity on tables with identity column*/
    exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT(”?”,RESEED,0) END’

    Regards,

    Vedran Kesegic

  76. You can also use ‘TRUNCATE TABLE ?’ istead of ‘DELETE ?’.
    Should be quicker on big tables and also resets identity to starting value, so you do not need last command in script (“reset identity” part).

  77. I’ve been searching everywhere, and I can’t seem to find an answer. This is for SQL Server 2005 SP2.

    I’ve got a data warehouse database that is essentially remade each night with new data from our production environment. Since all of the data in the data warehouse exists and is being backed up elsewhere, we can rebuild it in the case of a catastrophic failure and thus we have set the database to SIMPLE logging.

    Every night however, the SSIS jobs that fill the data warehouse will completely fill up the log file. This is 80 GB worth of drive space (the .mdf files are on a separate drive), and our log drive is getting full. I’ve scheduled two truncates during the night, and it’s STILL getting full. I’m at my wits end trying to figure out why a SIMPLE log is getting SO much stuff inside it. Any suggestions would be much appreciated.

  78. Hi!

    I m using SQL 2K5 standard edition and my database server is mirrored with another server. Now, as my log file size growing and it becomes 35 GB, can anybody guide me how can I reduce size of this log. Just remember that Database is mirrored and I dont want to disturb my mirror setup.

    Thanks,
    Devang

  79. @Devang

    SQL Server by default Truncates Transactional log file when it takes log backup.

    But when databases are being mirrored, this condition is not valid. Even though Sql Server takes backup of log file, it does not truncate log file.

    You cant even shrink Transaction log file of the database that is being mirrored. Shrinking database will only shrink Principal database but not Mirrored database. (Please look at the link provided below)

    Take a look at below link. Author do suggest a method to truncate log file with out actually stopping Database Mirroring, I dont know if this will work. Please do not run these script on Production, first test it out in TEST/DEV Env.

    Link : http://sqlship.wordpress.com/2008/06/17/how-to-truncate-mirrored-database-log-file/

    Note: Again I am not sure, if method described in above link will work. The only way I know of, to truncate log file is to stop Database Mirroring, and then truncate log file and then re initiate database mirroring.

    Once you truncate log file, then you can shrink database by using stored procedure provided by Microsoft to be used on mirrored databases.

    Link : http://support.microsoft.com/kb/937531

    ~ IM.

  80. Imran i m very thankful to you.

    It’s very helpful suggestions for me which you provided here.

    will work on test env.and let u kno…

    Thanks again…

    Devang

  81. Imran,

    After executing following query

    BackupLog to disk=‘D:\dbname.trn’

    error shows as ‘ incorrect syntax near the keywork to’

    i tried with changing queries by other way but not succeed.

    I like 2 know how can we backup database on network drive?

    let me know..

    thanks
    Devang

  82. @Devang

    Script :

    backup log database_name to disk=’\\myserver\sharenam\foldename\database_name_datetime.bak’

    You should have created a share folder on some other server where you will be taking backup to. Folder should have hand symbol.

    Remember, the share folder should have write access. Meaning normally when we create a share folder, by default read only permissions are assigned. But since you want to write backup into this shared folder, you should assign write permissions.

    Let me know what error you see when you execute above script. Change database_name and file name as per your standards.

    ~ IM.

  83. Imran

    Query for backup log executed successfully, but database log remains in same size, I m using Mirroring and it is not possible to shrink database in mirror mode. How can I shrink database / truncate transaction log if database in mirror mode.

    How can we use transaction log in SQL? What exactly use of this log? U may feel this is very sily que.. but, i want to kno depth on it.

    Thanks!
    Devang

  84. @Devang

    First check the occupied space and free space in logfile.

    Execute below script in master db.

    dbcc sqlperf (‘logspace’)

    This will give you how much log file space is occupied and what percentage of it is free. If you see that your log file for your database is all empty, then you can think of shrinking.

    How do I shrink MY databases which are being mirrored,
    I stop database mirroring and then I truncate log file and then I re initiate database mirroring. I do this in Maintenance window.

    Microsoft does provides a stored procedure which can be used to shrink database that are being mirrored. I myself have not used it, so I don’t know what will be the impact of executing this stored procedure. I already provided you link for this stored procedure in my previous reply.

    Here is the link : http://support.microsoft.com/kb/937531

    Again, Test it out in DEV/TEST before you implement this in Production.

    In case if log file for that particular database is still occupying a huge space (After you have taken Transactional Log backup ) and there is no or very less free space then I suggest you, in your maintenance window, you stop database mirroring and then take log backup, this will definitely truncates your log file, then shrink log file. And then Re Initiate Database Mirroring ( Re-Initiating involves setting up database mirroring right from the scratch)

    Note : After you take Transactional Backup, I strongly suggest you, that you first test restore your full backup and then Transactional backup and see if database can be restored successfully. It happened to me that after I took transactional backup, I removed database mirroring and then because of some issue when I wanted to restore database backup with point in time recovery, transactional backup did not work.

    So make sure that you test your transactional backup.

    Again, Test it out in DEV/TEST before you implement this in Production.

    Let us know if you see any issues. Its always good to ask and know if you have any doubts.

    ~ IM.

  85. Thanks for the script! I have been able to run it successfully in the past, but now I’m running into a problem. I’m trying to run the script as follows:

    USE ReportingServices
    GO
    DBCC SHRINKFILE(ReportingServices_log.LDF, 1)
    BACKUP LOG ReportingServices WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(ReportingServices_log.LDF, 1)
    GO

    Everything appears to be correct but when i execute the script I get the following:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘.’.
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘.’.

    Any thoughts as to what I’m doing wrong?

  86. Jeremy,

    You cannot have the “.LDF” in your script.

    Remove the file extension and just use the “ReportingServices_log” name (without quotes) and that should solve your problem.

    Christian

  87. I have a variation on the log problem. I need to shrink/delete my replication logs in the unc folder. Is there a script available that I could automatically run every month that would go into my replication log files, and delete all the logs up to the prior day? I really don’t need them. That way, I could just set up the job and forget about it.

    Thanks

  88. For those of you that need a maintenance plan solution.

    Add a backup task for the log backups, and then add a Execute T-SQL Statement Task joined by a green arrow (which means on success) Set up your log backups to run as often as you require and then in the Execute T-SQL statement box, paste the following code:

    use Database
    Dbcc shrinkfile (database_log, 1)

    You may not get immediate results but as the check points are marked by the backups, the log file will get smaller.

  89. That works fine……..

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

  90. PS The code you posted doesn’t work if there are active long-running transactions, a database mirroring SEND queue, or the transactional replication log reader agent job hasn’t scanned the untruncated log.

  91. @Paul Randal,

    Thank you for your comment Sir, I very much agree with you.

    I was only 60 days in blogging and SQL Server when I wrote this post and at that time I was even working with SQL Server 2000.

    Again, thanks for your coming here to help community who are reading this.

    Kind Regards,
    Pinal

  92. Hi Pinal,

    My database (replication with one more server) has grown to the hard disk limit, I tired using the above script, it threw an error, secondly when I take the backup the .bak file size is same as data + Log file size. Please suggest.

  93. Dear Pinal Dev,

    This is the first time i’m coming to this forum. It is very useful. Imran Mohd also doing wonderful job. He is giving info in depth

  94. Hi,
    My database (sql 2005) is only full recovery mode. The transaction log grew way too big about 34GB. So, what I did was
    1. shrink file using dbcc shrink file
    2. backed up the transaction log using to a file
    3. Issue DBCC shrink file

    Now here is my question, I know if I use the truncate only option on step2 and shrink the file, it will reduce the log file size. But If I do this I am seeing messages on event viewer saying

    “BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
    recovery model should be used to automatically truncate the transaction log.”

    Since, I have backed up the log file in step2 to a file, Can I safely truncate the log file? If so Will I be able to recover to a point in time?

  95. I am using the newest Microsoft SQL Version 8. Guess what, the shrinkage methods in SQL SERVER MANAGEMENT STUDIO are not working or make hardly a dent in the Log file size. Also the log files grow much faster than the actual databases.

    The method I used to shrink the log files is the one described here by Praveen Barath and which involves detaching the database in question, renaming the logfile, attaching the database again but now removing the log file and after DB data verification deleting the log file completely.
    I have no idea why the shrinkage utilities within management studio are not working at all.

    Thanks for the tip an keep up the good work.

  96. I found this post very helpful but ran into an issue when using this against a Sharepoint database. Full DB Name below:

    USE SharePoint_AdminContent_065b8fa2-f141-42ab-8b82-7b8bf659f7c2

    The error message that I get is listed below:

    Could not locate entry in sysdatabases for database ‘SharePoint_AdminContent_065b8fa2′. No entry found with that name. Make sure that the name is entered correctly.

    It looks like it is Stopping after 32 characters. Is there something else I must specify to allow it to use more than 32 characters?

  97. I misread the error message but still seek knowledge. It does not like the dashes…but I am not a DBA(this just fell into my lap and now I have to fix it) and not familiar with what my options are at this point.

  98. @Hans

    I’ve seen the issue with the shrink command not working from management studio before. In my case, the logical file names on the .mdf and .ldf didn’t match the name of the database. Fixing this seemed to allow the shrink commands to work.

  99. I work with SQL 2000.

    I use the follwoing script for reducing the log file size.
    There is no need of “use (database)” command

    dbcc shrinkdatabase (powerbrain)
    go
    backup log powerbrain with truncate_only
    go

    I use shrinkdatabase instead of shrinkfile where you may need to use “use database” command. I used to run this script in query analyser.

  100. John: Try USE [SharePoint_AdminContent_065b8fa2-f141-42ab-8b82-7b8bf659f7c2]

    Some database names needs to have surrounding brackets.

  101. This script worked for all my databases, but any database that starts with a number, I get error. any help appreciated. thanks

    USE 8PSTDB
    GO
    DBCC SHRINKFILE(8PSTDB_log, 1)
    BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(8PSTDB_log, 1)
    GO

    ————————
    Error message
    ————————-
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ’8′.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘rinks_log’.
    Msg 319, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘rinks_log’.

  102. in case the above error message is misleading, I copy pasted the error message from another db which starts with 8RINKS – which also throws an error same as the 8PSTDB

  103. Nilesh,

    why don’t you try this one?

    USE 8PSTDB
    GO
    DBCC SHRINKFILE(’8PSTDB_log’, 1)
    BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(’8PSTDB_log’, 1)
    GO

    BTW, it is not a good idea to truncate log manually rather do take backup of your database regularly and also take backup of your log files after every few hours, depends on your risk handling capacity.

  104. Is there a way to shrink all log files for many SQL databases in one go for SQL2005 and SQL2000, as I would a like script for this, for SQL2000 and SQL2005

    Thanks

    • @Rakesh,

      Can you please post your email id here,

      I have a script, with does exactly what you asked for.

      please leave your email id, write it in the post, so that it is visible to me and I will have it emailed.

      ~ IM.

  105. Hi Rakesh,

    this is possible for sure but I don’t recommend to truncate transaction log manually, even if you would like to do it, please refer below given link.

    h ttp://www.sqlhub.com/2009/05/shrink-log-file-for-all-database-in-sql.html

  106. Ritesh,

    The ‘quote’ did not help. Same error. Baffled now !!

    ————————————–
    why don’t you try this one?

    USE 8PSTDB
    GO
    DBCC SHRINKFILE(‘8PSTDB_log’, 1)
    BACKUP LOG 8PSTDB WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(‘8PSTDB_log’, 1)
    GO

    BTW, it is not a good idea to truncate log manually rather do take backup of your database regularly and also take backup of your log files after every few hours, depends on your risk handling capacity.

  107. I LOVE YOU.
    I had a 190Gb transaction log that I could not shrink.

    Used your code and SHAZAAAM.

    Now I need to set up a maintenance plan so this wont happen again.

  108. Hello Abernut,

    What is the recovery model of that database? If the database is not a publisher in transactional replication or log shipping then Changing the db to simple recovery model do all to shrink the log file.
    Is this database being replicated or ever configured for replication?
    Check for any open transaction using DBCC OPENTRAN.
    Try using CHECKPOINT and then BACKUP the transaction log.

    Regards,
    Pinal Dave

  109. Hi,

    I have a bunch of databases SQL Server 2005) whose logs have been growing too big recently. Two of them refuse to truncate the logs after a scheduled job runs a log backup. I have since run manual backups to disk and with truncate_only option but they will not truncate.

    Is there any reason why these will not truncate and how can I force the truncate since we are running out of SAN space?

    Sincerely,
    Nelson.

  110. Hello to all,

    We have SQL 2000 with very big database about 500 GB.
    We deleted about 50% of the data, we dropped all indexes
    With simple recovery option. (allot of image BOLB fields)

    However, the database size increased after delete process, we ran dbcc shrink db
    And shrink file, but the size still same.

    Any idea to get rid of all empty spaces, and what the best practice to reduce the size to 250 GB?
    Note: we don’t have much space to move the database around

    Thanks in advance
    Linda

  111. Hi there,

    I have two queries one pertaining to Sql Transaction log bulky and second regarding to stored procedures.

    Sql Transaction log – I have production database with the data file size of 240 mb and log file size of 47 GB.
    We have scheduled for a full backup everyday. The database is in the simple recovery mode. Intially when the database was backuped, the .bak was same as data file.
    We tired backing up the transactional log, but no luck, and tried with DBCC Shrink no effect, the log file is still the same.

    Kindly suggest.

    Stored procedures – We have written a stored procedure for reports, which uses number of temp tables for computation.
    Now my query is, if the sp is executed by multiple users at same instance or time, will the sp executes, will the output be correct and will the time taken for execution be more.

    Is there any possiblity to generate dynamic temp table,
    for e.g create table with the session Id.

    If there are any suggestion please do suggest.
    We need to implement the report with sp, any work around.

  112. Hi Udaya,

    In database property check the “Space available”. IF this is near about the size of transaction log then it shoud be shrunk. If this value is small then check whether any transaction is running from long using DBCC OPENTRAN.
    It may be that you assigned the 47 GB space to .ldf file at the time of database creation. If that is the case then use DBCC SHRINKFILE and define the free space size.

    About temp table: Don’t worry sql server handle the issue you are suspecting and uses the same approach that you are thinking to plan manually.
    A temp table (not global temp table) is available only to the session that created it. Multiple user can create temp table with same name at the same time because sql server internally use session id in there name to uniquify them.

    Regards,
    Pinal Dave

  113. Many Thanks for the reply Mr Pinal.

    When the database was created the size mentioned was 2 mb and incremental was 10%.

    I have executed DBCC OPENTRAN,
    Output – Replicated Transaction Information:
    Oldest distributed LSN : (2091:12488:1)
    Oldest non-distributed LSN : (2232:124689:1)

    Space Available : 16.85 mb

    I did shrink the database with the above command

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

    but nothing seems to happen, the size is 47 GB.

    — Stored procedure —
    Sorry, missed to mention, we are using single user id login in the reporting application. If any user access the application same authentication is used.

    In this the case, I belive the table would be rewritten, if the second user accesses the report.

    Is there any work around?

    When the sp is executed by users at the same time, the execution time is more. for instance when executed by a single user it takes 30 – 40 secs, where as when multiple user execute, its around a minute +.

    Is this cos the sp is trying to access the same table, its slow.

    Regards,
    Uday

  114. Hi Udaya,

    As the OPENTRAN resturn on process, that means one transaction is running and you need to stop that.
    If the data is not replicated then kill that process.

    Every time sp would be called the temp table would be created and if you think creating a temp table is taking time then keep a table in database and use it in your SP.

    Regards,
    Pinal Dave

  115. Hi Pinal,

    Sorry for the late reply, I was on vacations.

    I went through some of the articles on how to kill a process.
    In some article they refer to SPID. How do I get the SPID for the open transaction.

    Its a production server and replications are active, the replications are scheduled once in every 4 hrs.

    Please let know know if there are any command or script that needs to be executed to kill the process.

  116. Hello,

    I have one question which i am just able to find answer for.
    Suppose I have my .mdf file on D drive and .ldf file on E drive and due to some reason my D drive crash, and all my backup is also on D drive. So now if i want to restore my database in minimum possible time then how will I restore database now that i dont have my mdf file as D drive is not available.
    If any one can answer this question then it will be of great help to me…

    Thanks in advance.

  117. Hi Pinal,

    I deleted all the replication and the replication publisher as wel and recreated the publisher and replications. After doing this, when the backup job was run, the size of .bak was around 260 mb from initial 48 GB.

    Regards,
    Uday

  118. Hi Sunil,

    It is not possible to restore your database if you don’t have MDF as well backup files. only log file is not enough as it doesn’t have any data, it just contain transaction.

  119. I am working in one Data Service Process company. I was deleted 25 lakhs data from Database. After delete process, the mdf file size is increased (Before delete : 90 GB, After Delete : 95 GB). Now i am not considered about log file (ldf).

    How i decrease the size of mdf file without data loss ?

    Please give me its solution

    Thank you

  120. Hi John,

    First check the free space available in mdf file and accordingly shrink the file to set the new size. For example a file named file1 of size 95 GB, has 15 GB free space then shrink with followinf command:
    DBCC SHRINKFILE (file1, 80000)
    Make sure that this process should be run in off-working hours because it could bloack user’s processes.

    Regards,
    Pinal Dave

  121. Hi Pinal,

    I used without SHRINKFILE without target size (if missing, it take default size.. so i missed this parameter)

    DBCC SHRINKFILE (file1)

    It takes long time (above 5 hours) and still the query is working.. how long it need to complete its process ?
    Is it provide any problem ?

    What shall i do now ?

    Thanks in advance

    • Hello John,

      It seems you are shrinking the data file. Shrinking a data file while the database is in use may block or hang the processes.
      If data file has a lot of free space % then use SHRINKFILE with TRUNCATE_ONLY. It would instantly release the free space to OS.

      Regards,
      Pinal Dave

  122. Hi
    Thanks a lot
    Its working
    I tried with many solutions provided by various websites.
    But its working!!!!!!!!!
    Thanking U Again

  123. following only works

    USE MyDB
    GO
    DBCC SHRINKFILE(‘MyDB_log’, 1)
    BACKUP LOG MyDB_log WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(‘MyDB_log’, 1

    after I do below steps

    all u have to do is doing this steps and u will shrink ur database form enterprise manager and query analyzer
    1. open enterprise manager.
    2. right click on the database u wanna shrink it.
    3. click on properties.
    4. from the data properties go to options.
    5. in the middle u will see recovery model make it “simple” then click on “ok” and try to shrink the database.
    it works 100%

    I was getting error that all log files are in use. refer link

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ae4db890-c15e-44de-a2af-e85c04260331

    Lot of thanks to everyone & Dave

  124. Thank you for leaving this up. Its 11:45 pm on a Saturday night and I’m dialed into my server with an outrageously large log file. This saved my bacon, or at least kept me from having to go on site.

  125. Hi I have a Problem with attach/detach
    I detached my database then I am going to attach the database , buy it’s giving some error , the error is as follows..,

    Error 823:I/O error 38(Reached the end of the file.) detected during read at offset 0*000002ac680000 in file ‘S:\MSSQL\Data\ActivateMBBCards_log.ldf’.

    Please Provide the answer

  126. I am curious when is the proper time to the run:

    DBCC SHRINKFILE(, 1)
    and
    BACKUP LOG WITH TRUNCATE_ONLY

    In relation to the database backups.

    Before/after a full backup?
    Before/after a log backup?
    Or doesn’t it matter?

    Thanks,
    ~Peter

  127. Hello Sahasra,

    There is some read-write error in the log (.ldf) file. Restore the database without log file using sp_attach_single_file_db that would create new log file. Use this method as last resort as the older log file is removed and recovery is not performed that can turn your database into inconsistent state.

    Regards,
    Pinal Dave

  128. When is the best time to run:

    DBCC SHRINKFILE(, 1)
    and
    BACKUP LOG WITH TRUNCATE_ONLY

    in relation to backups?

    Before/after a full backup?
    Before/after a log backup?
    or doesn’t it matter?

    Thanks,
    ~Peter

    • Hello Peter,

      If you are keeping the transaction log backup then you should never execute “BACKUP LOG WITH TRUNCATE_ONLY”.

      You should use SHRINKFILE command after taking the full and transaction log backup.

      Regards,
      Pinal Dave

    • Of course it will change the LSN number that is used to identify the next backup log file while restoring. The MinLSN number also decide the inactive part of a transaction log file.

      Regards,
      Pinal Dave

  129. Hi
    I am in deep trouble one of my colleague deleted the .mdf file of db in sql 2005, Now there is no object can be seen in db. I want to take tail log backup for my safe side. Can I recreate .mdf file whereas .ldf file is still available on the disk. I can not recreate the .mdf file then how can I take tail log backup ?
    If I can recreate .mdf file, give me step to recreate the same.

  130. Hi Brijesh,

    Unfortunately log file contain only transaction not real data so you can’t create MDF file from log. there is only option available for you to restore your database with latest backup.

  131. Ok SQL 2008 this will not work on. For that you need to modify this slightly

    use DB1
    alter database DB1 set recovery simple
    alter database DB1 set recovery full
    dbcc shrinkfile(‘DB1_Log’,1)
    dbcc shrinkfile(‘DB1_Data’,1)

    Have fun

  132. What would be best way to implement this theory on SQL 2008 DB? I am looking for something that would assist me with compressing my dbs as well as truncating the log files.

    Rod

    Why are we setting it to simple then to full, I am sorry I did not understand why DB1_Data and DB1_log has to be shrink, shouldn’t it only be log file that should shrink??? Thanks!

  133. My database name is “VTT Breda” andi want to truncate the transaction log.

    but while using the query
    USE VTT Breda
    GO
    DBCC SHRINKFILEVTT Breda, 1)
    BACKUP LOG VTT Breda WITH TRUNCATE_ONLY
    DBCC SHRINKFILEVTT Breda, 1)
    GO

    some error is being shown due to the space in the db name.

    Could you please help me in truncating the transaction log.

    • Hi Preema,
      I am oracle dba now working on MSSQL 2000,2005
      we take the downtime for truncate the log and use following way for truncating producation database log.

      use master
      go
      checkpoint
      go

      dump transaction ReportServer with no_log
      go
      use ReportServer
      go
      dbcc shrinkfile (2)
      go

  134. We Uninstall MSSQL 2000 Standard edition
    Now we reinstall MSSQL 2000 Standard edition
    trying to attach old databases it display error .

    Could not find row in sysindexes for database ID 7, object ID 9, index ID -1. Run DBCC CHECKTABLE on sysindexes.

    We need Help.

  135. Hello Pinal,

    Just wanted to know, what to do in this scenario.

    This statement does not work with SQL Server 2008.
    It works fine with SQL 2000 / SQL 2005.

    ” BACKUP LOG WITH TRUNCATE_ONLY ”

    So, with SQL Server 2008, do we have a replacement statement(s) that could be used to accomplish the same task.

    Also, FYI the Log file has been set to 10 % Auto growth with unrestricted growth. At times, the log file grows as large as 80-85 GB, whereas the actual Database is barely 6-7 GB.
    Any ideas on how we can set SQL Server to Automatically TRUNCATE the LOG file once it exceeds certain size.
    Can DBCC SHRINKFILE (,transactionlogfile, percentvalue) be used ??

    Thanks in advance. Regards,
    Aashish Vaghela.

  136. Hi Ashish,

    The truncate_only option has been removed in SQL Server 2008.

    The only workaround in SQL Server 2008 is :

    1) Change the database recovery model to simple.
    2) shrink the log file using DBCC SHRINKFILE
    3) revert back the recovery model to FULL.

    For your other question you can set up SQL Server agent alert which fires when log file size exceeds a particular value and in the response part of the alert u can fire a job that takes transaction log backup and shrinks log file size.

    Best regards,
    Ashwani

  137. For SQL 2008 you have to follow below way to trunacate log file –

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

    Regards,

    Nilesh Shah

  138. Hello everyone!
    NEED AN URGENT HELP!

    My DB recovery model is set to simple.
    But my logs are getting this message very often and my customer needs an explanation to this.

    “BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

  139. All my servers are Express Edition. So, they have max 4 GB limitation. Is 4 GB limitation include log file size?

    If yes, I may need to run script given by you (after taking full backup).

    Please let me know…

  140. Hi Dudes,

    Very Good Morning.

    i want track all insert, update and delete activity on one table using trigger.

    what ever user done on that table i want save that activity in another table.

    any buddy tell me how can i track all activity in sql server.

    thanks in advance
    satish

  141. Hi pinal sir,

    I am looking a database of website.many users logged into website and feeds some entry and data.
    can i shrink log file(ldf), while user are connected to website,or they will lose some data when i will shrink log file.
    pls guide me

    • sir,
      my log file having 38 gb size after shrink it is 34 gb.
      what i should do now,

      can u provide me script by which log file will shrink automatically after a partikular time.

  142. Hello Ashwani & Nilesh,

    Thanks both of you !

    I’ve tried your suggestions & they have worked for me.

    Our .NET based application uses SQL Server 2005-based
    Database & has a LOG file too. This baby grows tremendously
    over a period of time. We either have to TRUNCATE it manually or use SQL Agent to create a JOB that will truncate it periodically.

    Today I noticed that the LOG file (.LDF) was more than 900MB
    in size & was slowing down the application to a lot of extent.
    Truncating it didn’t help. So I used DBCC SHRINKFILE (LogFileName, 10) & it worked successfully. The 900+ MB Log was reduced to 10MB in a matter of few seconds.

    Thanks once again …!
    Regards,
    Aashish Vaghela.
    Sys.Admin.

  143. Oh ! forgot to ask you all (including Pinal) a question.

    Is there a way to monitor the LOG file & its growth.
    So that once this LOG file crosses certain size limit,
    a TRIGGER gets invoked & performs TRUNCATE or SHRINKDATABASE or SHRINKFILE operations .. ???

    Thanks in advance .. !

    Regards,
    Aashish Vaghela
    Sys.Admin.

  144. hi pinal,

    one database log file frequently full , i want write script log file full alert. please give soluiton to me//

    Thanks
    jernas

  145. Hey there,

    Quick question:

    To truncate a huge log file on SQL 2008…as backup log with truncate_only is not working anymore..Can i use this method instead:

    1. Alter database…set recovery model to Simple.

    2. Shrink the log file by right clicking on the database under option of tasks..and release the space to operating system.

    3. Alter database…set recovery model to Full.

    And, one more thing after doing this, Do i need a full backup of the database to restore it to point in time recovery in case of any disaster…or i still can use my old set of backups chain??

    Does this method have any harm to use…?

  146. this is no more available in 2008.
    If you want then follow this :-
    1) first check the log file usage by dbcc sqlperf(logspace)

    2) you will see the results in % of use, less the percentage more is free to shrink.

    3) if your database have less % then check how much you can release using DBCC LOGINFO(DatabaseName)

    4) There will be 2 entry 0 and 2. the more 0 in bottom the more you can shrink and will be shrinked till last 2 or default size.

    5) run dbcc shrinkfile((‘logical file name’) — it will shrink the file upto default value.

    6) take full backup

    hope this helps

  147. Hi Pinal,
    If i shrink a log file. Is Database must have in simple recovery mode?
    Is it true.. or else we shrink a log file at any recovery mode.. Reply plz

    With Regards
    Balaji.G

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

    Im shrinking the database log files using this method manually.
    But I want to auto shrink the log files when it reaches 1GB.

    can any one help me on this..

    Thanks in Advance..

  149. Hi

    I am working in SQL server2005. there are log files with extention .ldf and .mdf.. These files increasing continuously

    I just want to know what is the use of these files in DB and how to shrink them ?

    Please help…

  150. the above code is not working in SQL 2008.

    It is showing the synatax error i.e ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

    Kindly guide me in this..

  151. Hi Pinal,

    Is there any way I can create a Job which triggers native log backup If the log file size reaches 80% of its capacity in any given time ? This is to clears out committed or aborted transactions after a log backup.

    Thanks!!!

  152. Hi, Pinal

    I am afraid what you have given as a sol’n does not work, at least in some cases – such as mine – see below:

    I attempted to shring log file repeatedly without success.

    The DB is in Simple Recovery Mode

    I use

    DBCC SHRINKFILE(mlsMail_Log, 1)
    BACKUP LOG mlsMail WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(mlsMail_Log, 1)
    GO

    Have tried
    backup log mlsMail with truncate_only
    several times, no change
    —————-
    DBCC sqlperf(logspace) – shows less than .01 % used
    DBCC OPENTRAN – shows no open Trans

    DBCC LOGINFO(‘mlsmail’) shows the following:

    FileId FileSize StartOffSet FSeqNo Status Parity Create LSN
    2 134152192 8192 42215 0 128 0
    2 134152192 134160384 42216 2 128 0 Tags:

  153. Hi,

    Which scenario we do shrinking data or log file?

    If the log file is growing large and it contains active transactions can we shrink the file

    Thanks
    koteswarrao

  154. I have a mirrored environment in 2005. Now the log file is 30 GB and my mdf file is 4 GB. Can someone suggest the way to shrink log file.

    Normal procedure is not working when i tried to shrink the log file in MIRRORED ENVIRONMENT.

    Help me out please.

  155. You really helps me lot, my log file size about 27 GB and i got worried because my server harddisk partition size was just 30 gb and there was only 400 mb remains showing, after getting help by your this post i applied to my server and your syntax is totally correct . thanks a lot.
    Thanks
    Amit Pathak

  156. I wanted to truncate a log file from a java process and whether I did it manually or used logrotate, when the process would output another line the filesize would jump back to what it was previously. I tried sending a HUP signal to the process but that would kill it altogether so not an option. In the end I found a solution which is to start the process with a double arrow redirect instead of a single one, eg:

    java file >> log_file 2>&1

    I can now use all tools to rotate the file, the process keeps writing to it and the file sizes are permanently reduced.

  157. Hi, Pinal

    I wonder am not an IT Guy. can you help me out to work on my LOG file which is growing a lot bigger day to day.

    i searched all kind of net notes and even try to read the notes.

    can i have the step by step process how to shrink the file.

    my date base is 5 GIg but now it is occupies around 50 Gig.

    hope i can get a help from you,how to do it

    were to write the code and how to work it out.

    thanking you
    suneel

  158. Thank You. It worked for my system (MS SQL Server 2000) as well where the Log Size was around 160 GB.
    It took around 8.5 minutes for the query to complete. Also, an important aspect is that I didnt have to put my DB offline or detach it either.

  159. Hi,

    I want to truncate my log from my database in sqlserver.

    Like i was using before for sqlserver 2005.

    dump tran with no_log.

    I don’t want to follow below stpes everytime to release my database.

    1) deattach databse
    2) rename log file or delete
    3) attach again
    4) check transaction log file size.

  160. In SQL 2008 use this

    USE Database_name;
    GO

    – Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE Database_name
    SET RECOVERY SIMPLE;
    GO

    – Shrink the truncated log file.
    DBCC SHRINKFILE (Database_name_log);
    GO

    – Reset the database recovery model.
    ALTER DATABASE Database_name
    SET RECOVERY FULL;
    GO

  161. 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

  162. 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.

  163. 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.??

  164. 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.

  165. 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……………..

    • Kajal

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

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

  166. 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

  167. 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

  168. 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

  169. 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

  170. 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.

    • 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…

  171. 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

  172. 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

  173. 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

  174. 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?

  175. 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!

  176. 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)

    http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html

  177. 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

  178. 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

        • 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

  179. 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

  180. 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

  181. 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

  182. 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.

  183. The following three commands worked for me and it shrunk the log file to 1mb

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

    Thank you so much
    Mathi

  184. Hi pinal

    I have database with size of 32Gb and log file size is 17GB. I want to reduce the log file size.How can i do this.

    Note :- Its a live server.Please suggest Me.

  185. Hey Guys,
    I have SQL2000 and even i face the same issue, log file increases and i have to shrink it in every 2-3 days… can you tell me if i can schedule the same through maintenance plan is there any way i can do auto shrink the log size..

    Please let me know..
    Thanks.
    Suresh Nakka.

  186. Hi Pinal,

    I will be regularly reading all your blogs and have got solutions for many of my Database problems and learned many new concepts. Thanks a lot.

    For the first time I’m posting my problem here, please help me on this.

    I have a batabase of size 3GB (Log file 500MB) and has been configured Every Saturday Full and Every 8 hours Transactional Log Backup. Problem is Log backup will be happenning very frequently every hour (even then its configured for every 8 hours). Due to this, its taking too much of my drive space, also provided drive space for backup is less only, I cant expect more drive space also. When ever Log file size becomes abnormal will shrink the Log file alone. Also transactions on this particular database is average only, not too much.

    Can you please help me out for fixing this problem. I tried searching reason for this, but dint succeed.

  187. I prefer this method , easy to script as well.

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe -E -S Servername -d “DatabaseName”
    ALTER DATABASE DATABASENAME SET RECOVERY SIMPLE;
    go
    DBCC SHRINKFILE (‘DATABASENAME_log’, 200);
    go
    ALTER DATABASE DATABASENAME SET RECOVERY FULL;
    go

  188. Hello, How can I do this on SQL SERVER 2008? If try to run this query the SQL show me:
    Msg 155, Level 15, State 1, Line 1
    ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

  189. Hi Pinal. I was following your posts since time ago, and I take a brief time to thank you. I’m grateful for all of your articles due I found them very useful for my daily activity. I’m a software developer and use to deal with databases but I’m not a db administrator and time to time I have to become a little bit into it due the maintenance problems of a database. Time to time I stop to think myself what is your motivation to write and share your experiences with all of us. Far to need an answer, I can feel your inspiring sense of generosity. Again I thank you for everything here.

  190. In our case the log backup was running for 10 hours then failing, and the transaction log was 100x the size of the data file. The generic error reported in the log was not helpful but further investigation led to an error related to the log containing transactions pending replication.

    This was strange because the db was not published for replication.

    Anyway, what worked for ua was to run sp_repldone command first,…

    msdn.microsoft.com/en-us/library/ms173775.aspx

    …and then follow the instructions in this article.

    Hope this helps anyone with the same problem

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

  192. I tried but it’s not worked.I know better option as changingn the recovery model and then shrink but ican’t do this because of Disater recovery plan.So,anyone have other option.

  193. 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

    • 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.

      • 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

  194. Hi, Am no SQL expert and i have a big concern on one of my applications sitting on SQL 2008 database. My drive size is 200Gb and the log file xxxx_log.ldf is 182GB. Please advise on how to shrink this, bearing in mind am a newbie to SQL world.

  195. Pingback: SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001 | Journey to SQL Authority with Pinal Dave

  196. 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?

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