locked
Log file almost full! RRS feed

  • Question

  • Gurus,

    I have a transaction long and the log file is almost full! (95%). The DB is in simple recovery. I tried to shrink the log file and it does not get down. Any ideas apart from adding more disk space?

    thanks


    ebro

    Tuesday, June 17, 2014 3:40 PM

Answers

  • There is one more option if possible you can add log file to another drive and allow log to grow to complete the Transaction then you can shrink it. If you don't want to add space this can be a viable option.

    A sleeping transaction is one which has done its task and left all resources you may kill it. But again killing will not help as hardly I think it will rollback anything because sleeping transaction has nothing associated with it.

    Let me tell you when you kill a transaction it will rollback and rollback requires space in log to write information so again log file will be utilized and as you said you don't have space on that drive it will again be a issue. So consider adding log file to other drive

    If all above options are not possible for you there is no other option but to increase disk space

    Finally you should also see why such a long transaction is running can this be run in batches so that it does not bloats the log file.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by ebrolove Tuesday, June 17, 2014 7:21 PM
    Tuesday, June 17, 2014 5:58 PM

All replies

  • Hello Ebrolove,

    You will want to find out what is preventing the log from being reused.

    SELECT[log_reuse_wait_desc]
    
     
        FROM [master].[sys].[databases]
        WHERE [name] = N'DatabaseName';
    GO


    Tuesday, June 17, 2014 3:50 PM
  • You might have got a long running transaction which is holding off the logfile from reusing. As long as the long running transaction is not completed, the log file cant be shrinked. You have no other option other than to increase the size of the file.

    If its not a long running transaction check this and see what is holding the log file from reuse.

    select

    name, log_reuse_wait_desc from sys.databases


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, June 17, 2014 3:50 PM
  • The first step in this process is understanding as much as possible about the reason SQL Server won't reuse the space in the log file.  The easiest way I can think of to begin gaining that knowledge is by querying sys.databases and examining the value in the log_reuse_wait column.  For example:

    select d.log_reuse_wait, d.log_reuse_wait_desc from sys.databases as d where d.name = 'mydatabase'

    Once you have that information check the Microsoft documentation on this problem and see if there's anything obvious.  The documentation is here.

    If you need further assistance then post back here with the additional details you discovered.

    Tuesday, June 17, 2014 3:52 PM
  • Active Transaction is the reason why it is not truncating the log file.

    ebro

    Tuesday, June 17, 2014 3:56 PM
  • If there is active transaction, unless the transaction commit or rollback , the log will not be reused. You also wont be able to shrink the file. The only option is to increase more space for the transaction to complete.

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, June 17, 2014 3:58 PM
  • Also, I see the longest running SPID and the status for that spid is "sleeping". Does that mean I can kill that session?

    ebro

    Tuesday, June 17, 2014 4:00 PM
  • Can you see if there is any open transactions?

    dbcc

    opentran


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, June 17, 2014 4:15 PM
  • You need to try to find out what that process is or was doing.  To do that you can use DBCC INPUTBUFFER.  For example...

    DBCC INPUTBUFFER (999)

    Check out the Eventinfo column and see if you can determine whether or not it's safe to kill the process.

    Tuesday, June 17, 2014 4:18 PM
  • If you absolutely cannot expand the volume, you may have to kill sessions.   It's not necessarily the oldest spid that is causing your problem.  

    If you can find the offending query, you can see if it is not committing frequently enough.

    If you don't find the offending query you will likely run into this problem again.

    Restarting the SQL service will also rollback or forward all the transactions.   And may be able to help you reclaim space in emergency.

    Tuesday, June 17, 2014 4:32 PM
  • Assuming that you are on a recent SQL Server version (always post the version and the edition your are using along with your question) you can use the following to identify the statement and then use the KILL <Session_id> command for aborting the transaction, thus rolling back.

    Select * from sys.dm_tran_session_transactions

    You might also want to take a look at the following way to get more information about the current sessions running:

    http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Tuesday, June 17, 2014 5:09 PM
  • There is one more option if possible you can add log file to another drive and allow log to grow to complete the Transaction then you can shrink it. If you don't want to add space this can be a viable option.

    A sleeping transaction is one which has done its task and left all resources you may kill it. But again killing will not help as hardly I think it will rollback anything because sleeping transaction has nothing associated with it.

    Let me tell you when you kill a transaction it will rollback and rollback requires space in log to write information so again log file will be utilized and as you said you don't have space on that drive it will again be a issue. So consider adding log file to other drive

    If all above options are not possible for you there is no other option but to increase disk space

    Finally you should also see why such a long transaction is running can this be run in batches so that it does not bloats the log file.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by ebrolove Tuesday, June 17, 2014 7:21 PM
    Tuesday, June 17, 2014 5:58 PM
  • Also, I see the longest running SPID and the status for that spid is "sleeping". Does that mean I can kill that session?

    No one here can answer that question for you. We don't know if this is a your server on your development machine, or if this is a production environment. Or something else. Nor do we do know what this process was up to. If you kill it you may lose an uncommitted data update.

    As others have pointed out, you can use DBCC INPUTBUFFER ot see what what command the processes most recently submitted. You can also use sp_who2 to see from which machine this session originated and what program it may be coming from.

    But it is up to you if you want to kill it, or find it and try to commit. (Which would be possible if it is an orphaned query window).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 17, 2014 9:37 PM