locked
Transaction log Shrinking RRS feed

  • Question

  • Hi

    My ldf file has grown to 56gb, I am trying to shrink the ldf file using the below command :

    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    DBCC FREESYSTEMCACHE ('ALL')
    DBCC FREESESSIONCACHE
    DBCC SHRINKFILE (xxxx_log,1024)

    but the file is not shrinking.

    The file size increased just yesterday due to some transact operations.

    Last night the full backup was taken but still the log file shows this huge size.

    Please let me know how can I reduce the file log file size.

    Also this database is a Principal server in a Mirror setup, hence this has affected the mirror server as well and the log file has increased there as well.



    Tuesday, January 21, 2014 6:57 AM

Answers

  • Any open transaction observed on the database?

    As per below article

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

    Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.


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

    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 7:22 AM
  • In addition, taking the log backup does not guarantee that the virtual log files (VLF) will be marked inactive and shrinking does not guarantee you get all of the free space back after truncating the log. So, even if your database mirroring session is healthy and you regularly run your log backups and you shrink your log, the size may not go below a certain size. The log file requires an active VLF at any given point in time. So, if this VLF is near the end of the log file, you will not be able to shrink below that. To validate, run DBCC LOGINFO for that database and look at the Status column. A value of 0 means the VLF is inactive and a value of 2 means the VLF is active. The question is, "where is the location of the active VLF in reference to the entire log file?" If it is near the end of the log file, you will not be able to shrink below that. To resolve this, you need to introduce dummy transactions to force the log file to wrap around (running log backups correspondingly to truncate the log) and go back to the beginning of the log file.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 5:00 PM

  • I got the following info for the database

    xxxx                        DATABASE_MIRRORING


    Hello,

    This points out to fact that DB mirroring is configured for database and either Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).This can also be due to Bandwith connecting principal to mirror is not able to cope up with logs being generated.Can you also check load faced by this link ,take help of network team

    Assuming you have synchronous mirroring which involves two phase commit ,i mean commit will happen on principal after it has been done on mirror this behavior can cause log growth.See factors that can delay log truncation

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

    If this issue goes on and you cannot see log growth declining ultimately  you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

    I would like you to see redo queue.Also check how much logs is being sent to mirror through that link.

    Hope this helps


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

    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 7:24 AM

All replies

  • DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    DBCC FREESYSTEMCACHE ('ALL')
    DBCC FREESESSIONCACHE
    DBCC SHRINKFILE (xxxx_log,1024)

    Last night the full backup was taken but still the log file shows this huge size.

    Hello Sudhakar,

    Please stop using DBCC FREEPROCCACHE and other commands you mentioned for shrinking log file.This could lead to performance degradation.This will clear your cache ,take plan cache, now when new query will come it has to create altogether new plan which would not have been required if FREEPROCACHE had not been used.Consider many such queries coming after running freeproccache this can bring down performance of your server and extra resource would be required to create new plan

    Now to log file.Full backup does not truncates log file only transaction log backup does.Are you taking it ( assume your DB is in full recovery).Below query will point out what is holding your log.

    select name,log_reuse_wait_desc from sys.databases
    Sometimes a long running transaction will hold the logs and will not allow it to truncate .You can truncate logs after this transaction finishes by taking transaction log backup.


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


    Tuesday, January 21, 2014 7:08 AM
  • Any open tran in the database?

    Check from

    use database_name
    go
    dbcc opentran()

    Or check where transaction log space is currently waiting on
    select name,log_reuse_wait_desc from sys.databases

    Like, if the reason is LOG_BACKUP, than log backup is needed. If checkpoint than fire a checkpoint to release the space

    Refer below links

    http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
    http://technet.microsoft.com/en-us/library/ms365418.aspx


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

    Tuesday, January 21, 2014 7:12 AM
  • I am not sure why you cleared the procedure cache and buffers. This will yield to a potential performance issues. To shrink the log file, the thumb rule is 1. If the database is in FULL recovery model, take a log backup. 2. Once the log backup is completed the run DBCC shrinkfile. In your case since mirroring is configured, the logs should be applied to the mirror server as well. Check in database mirroring monitor how much logs need to be applied and how long it will take. Allow until it is applied to the mirror server and then run the log backup and shrink the log file.
    Tuesday, January 21, 2014 7:13 AM
  • My Transaction log backup as schedule every 20 min (I am taking the Tlog backup).

    when I ran the script:

    select name,log_reuse_wait_desc from sys.databases

    I got the following info for the database

    xxxx                        DATABASE_MIRRORING

    As database mirroring is on.

    what can I do now

    Tuesday, January 21, 2014 7:15 AM
  • Any open transaction observed on the database?

    As per below article

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

    Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.


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

    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 7:22 AM

  • I got the following info for the database

    xxxx                        DATABASE_MIRRORING


    Hello,

    This points out to fact that DB mirroring is configured for database and either Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).This can also be due to Bandwith connecting principal to mirror is not able to cope up with logs being generated.Can you also check load faced by this link ,take help of network team

    Assuming you have synchronous mirroring which involves two phase commit ,i mean commit will happen on principal after it has been done on mirror this behavior can cause log growth.See factors that can delay log truncation

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

    If this issue goes on and you cannot see log growth declining ultimately  you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

    I would like you to see redo queue.Also check how much logs is being sent to mirror through that link.

    Hope this helps


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

    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 7:24 AM
  • In addition, taking the log backup does not guarantee that the virtual log files (VLF) will be marked inactive and shrinking does not guarantee you get all of the free space back after truncating the log. So, even if your database mirroring session is healthy and you regularly run your log backups and you shrink your log, the size may not go below a certain size. The log file requires an active VLF at any given point in time. So, if this VLF is near the end of the log file, you will not be able to shrink below that. To validate, run DBCC LOGINFO for that database and look at the Status column. A value of 0 means the VLF is inactive and a value of 2 means the VLF is active. The question is, "where is the location of the active VLF in reference to the entire log file?" If it is near the end of the log file, you will not be able to shrink below that. To resolve this, you need to introduce dummy transactions to force the log file to wrap around (running log backups correspondingly to truncate the log) and go back to the beginning of the log file.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    • Proposed as answer by Saurabh Sinha DBA Wednesday, January 22, 2014 12:25 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 3:10 AM
    Tuesday, January 21, 2014 5:00 PM
  • And a good way to "to introduce dummy transactions to force the log file to wrap around" is just to rebuild a small table.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 21, 2014 5:35 PM