none
transaction log full - job fails

    Question

  • Hi all ,

    one of job fails due to 'the transaction log for a db is full , see the log_reuse_wait column in sys.databases (sql state42000)(error 9002). the step failed.

    db is in simple recovery model .

    regards

    pradeep

    
    Monday, October 21, 2013 12:08 PM

All replies

  • see the log_reuse_wait column in sys.databases 

    This is a forum for Data Mining, not for SQL Server Database Engine related issues.

    And which value does log_reuse_wait return for that database?

    SELECT name, log_reuse_wait
    FROM sys.databases

    What does your job do, is it running a large transaction? Have you checked the free space on disk? If e.g. the disk is full, you get the same error message.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 21, 2013 12:13 PM
  • Hello,

    Log truncation process is automatic in Simple recovery ,it seems you job is running some long transaction which is causing log file to grow.

    Note: Simple recovery does not means log file will not grow.it will grow but after log reaches 70 % of its size truncation os log is started .This can hamper if long running transaction is there.

    You might need to stop that transaction or add more space.Below query will return you open transaction if any in concerned database

    use database_name
    go
    dbcc opentan()
    go


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

    Monday, October 21, 2013 12:20 PM
  • Hi olaf ,

    hanks for quick reply . i have executed that script and found that it returns '0' value for that db. disk also contains free space around 100 gb. that job contains info like db activity ie who logged into db date, and  time  only ......

    pradeep

    Monday, October 21, 2013 12:31 PM
  • hi ,

    no active transactions found .....................

    pradeep

    Monday, October 21, 2013 12:36 PM
  • Hi olaf ,

    hanks for quick reply . i have executed that script and found that it returns '0' value for that db. disk also contains free space around 100 gb. that job contains info like db activity ie who logged into db date, and  time  only ......

    pradeep

    Hello Pradeep,

    Have you double checked whether you have set unrestricted autogrowth of the log file?

    Thanks

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

    Monday, October 21, 2013 1:53 PM
  • Hi olaf ,

    hanks for quick reply . i have executed that script and found that it returns '0' value for that db. disk also contains free space around 100 gb. that job contains info like db activity ie who logged into db date, and  time  only ......

    pradeep

    Hello,

    Its highly unlikely that free space on log file is 100GB and you get transaction log for Db is full.Are you sure you are giving right information.Please post output of both query.Instead of db_name put the name of database which is giving error

    select recovery_model_desc,name,log_reuse_wait_desc from sys.databases where name='db_name' -- select @@version

    ---
    use db_name
    go
    select name,max_size from sys.database_files 



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


    • Edited by Shanky_621 Monday, October 21, 2013 2:13 PM
    Monday, October 21, 2013 2:01 PM