none
sql server 2012 - how to shrink the database transaction log

    Question

  • I am using Sql server 2012

    My data files is 103 mb but the transaction log file 37 gb.

    1     I want to shrink transaction log file. What precaution should I take before shrinking?

    2     Is the backup and restore of database are same as usual after shrinking?

    3     In management studio right click database name -> Tasks -> Shrink -> Database Or Files

           Which option should I choose ?


    h2007

    Monday, July 22, 2013 3:21 AM

Answers

  • I am using Sql server 2012

    My data files is 103 mb but the transaction log file 37 gb.

    1     I want to shrink transaction log file. What precaution should I take before shrinking?

    2     Is the backup and restore of database are same as usual after shrinking?

    3     In management studio right click database name -> Tasks -> Shrink -> Database Or Files

           Which option should I choose ?


    h2007

    From Above it is sure you are not taking transaction log backup.If point in time recovery is not required you can make it in simple recovery ,in this automatic checkpoint happens and logs are cleared after checkpoint.

    If you wan to keep full recovery plzz take frequent trn log backups this will not allow log to grow so question of shrinking will not appear.Shrinking is bad operation and may cause performance issues

    1Shrinking i bad operation ,i generally dont recommend it but if required gravely u can go ahead,it causes fragmentation so u have to check for fragentation after index rebuild.

    2.Shrinking of trnlog files will not reduce your backup file size.Backup includes data and some transaction log

    3.You should choose files and then choose trn log files ,please dont (never) choose data file.Above all proper backup policy which includes frequent trn backups will prevent u from hassle of shrinking.

    PS:Only transaction log backup can shrink Trn logs


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 22, 2013 4:31 AM

All replies

  • Can you say what recovery model you are using ?  If full recovery, frequent T log backup would avoid this much log growth unless a really large run away transaction going on.  After shrink, a full backup should be performed and index rebuild should be done. 

    http://technet.microsoft.com/en-us/library/ms190757.aspx

    Shrink will result some unwanted issue in database. You will not want this one more time. Precaution should be a frequent t log backup in full/bulk logged and continuous monitoring over log growth.


    Srinivasan

    Monday, July 22, 2013 4:06 AM
  • I am using Sql server 2012

    My data files is 103 mb but the transaction log file 37 gb.

    1     I want to shrink transaction log file. What precaution should I take before shrinking?

    2     Is the backup and restore of database are same as usual after shrinking?

    3     In management studio right click database name -> Tasks -> Shrink -> Database Or Files

           Which option should I choose ?


    h2007

    From Above it is sure you are not taking transaction log backup.If point in time recovery is not required you can make it in simple recovery ,in this automatic checkpoint happens and logs are cleared after checkpoint.

    If you wan to keep full recovery plzz take frequent trn log backups this will not allow log to grow so question of shrinking will not appear.Shrinking is bad operation and may cause performance issues

    1Shrinking i bad operation ,i generally dont recommend it but if required gravely u can go ahead,it causes fragmentation so u have to check for fragentation after index rebuild.

    2.Shrinking of trnlog files will not reduce your backup file size.Backup includes data and some transaction log

    3.You should choose files and then choose trn log files ,please dont (never) choose data file.Above all proper backup policy which includes frequent trn backups will prevent u from hassle of shrinking.

    PS:Only transaction log backup can shrink Trn logs


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 22, 2013 4:31 AM
  • Hi,

       Hope it will help you,

     

    USE AdventureWorks2008R2;
    GO
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    -- Reset the database recovery model.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO
    • Edited by vijayalaxmi_atyam Monday, July 22, 2013 9:50 AM I gave have another solution
    Monday, July 22, 2013 9:44 AM
  • Apart from what has been already suggested, please make sure you use the correct recovery model for your database. The following link should help:

    http://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx


    - Sanbhaumik ---------------------------------------------------- Please Mark as Answered if you find my post helpful.

    Monday, July 22, 2013 9:56 AM
  • Hi,

       Hope it will help you,

     

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

    What happens if the database is log shipped or transaction replication is in active.. changing recovery mode is not a simple decision. once must understand why the database is set up for FULL recovery model and identify the  features that use transaction log in operation ore not.. 

    Not good to give blind answers like this with out understanding the end user system

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 22, 2013 10:02 AM
  • Hi Srinivasan,

    I am writing to follow up with you on this post. If you are satisfied with our solution, I'd like to mark this issue as"Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have?

    Thanks
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Sunday, July 28, 2013 1:43 PM
    Moderator
  • Sunday, July 28, 2013 3:16 PM
    Moderator