none
how to shrink transaction logs

    Question

  • i got a db that is 300mb but its transcation log is over 220gb. how do I shrink this file? 
    Sunday, June 16, 2013 12:06 AM

Answers

  • i changed the recovery model fro full to simple and did a shrink on he logs and now its a lot smaller. down to 2MB. what happened?

    When u changed rec model from full to Simple immediate checkpoint started and truncation of log started and VLF were marked truncated..so log was made free for Truncation..

    DID U TOOK FULL BACKUP AFTER CHANGING FULL TO SIMPLE PLEASE MAKE IT FULL AGAIN AND TAKE FULL BACKUP

    start taking frequent transaction log backups to avoid this issue


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

    Monday, June 17, 2013 6:00 AM

All replies

  • It depends

    1.If ur sql server recovery model is Simple Checkpoint happens and logs are truncated automatically.If ur DB is in simple rec try running DBCC SHRINKFILE for log and it will shrink no issues

    2. If your DB is in full rev mode which I assume is ur case ONLY TRN BACKUP CAN TRUNCATE LOGS and after that u can shrink.

    now Run DBCC loginfo(DB_NAME) it will produce result having col status. If last value if status is ZERO and u try to shrink log file it will be successful it its TWO even if u try to shrink it will not AS loginfo gives amount of VLF'S in ur log file and ZERO means vlf is marked truncated and TWO means active so to make VLF from ZERO to TWO u have to take trn bak..after taking trn backup run dbcc loginfo command again look for status col and shrink accordingly

    If u want to shrink immdediately(not recomended in prod)

    change rev model of ur DB from Full/bul logged to simple

    or run this command..backup log [db_name] to disk='NULL'

    now try shrinking it

    After shrinking make it to Full again and take full backup immediately


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


    • Edited by Shanky_621 Sunday, June 16, 2013 3:49 AM
    Sunday, June 16, 2013 3:46 AM
  • You can refer below link for shrinking log file:

    http://msdn.microsoft.com/en-us//library/ms189493.aspx

    Incase still facing any issueo,  share some more details like how much log file is being used and current active section.


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, June 17, 2013 4:43 AM
  • i changed the recovery model fro full to simple and did a shrink on he logs and now its a lot smaller. down to 2MB. what happened?
    Monday, June 17, 2013 4:50 AM
  • i changed the recovery model fro full to simple and did a shrink on he logs and now its a lot smaller. down to 2MB. what happened?

    When u changed rec model from full to Simple immediate checkpoint started and truncation of log started and VLF were marked truncated..so log was made free for Truncation..

    DID U TOOK FULL BACKUP AFTER CHANGING FULL TO SIMPLE PLEASE MAKE IT FULL AGAIN AND TAKE FULL BACKUP

    start taking frequent transaction log backups to avoid this issue


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

    Monday, June 17, 2013 6:00 AM