locked
Sql Server 2008 log file getting full accidentally RRS feed

  • Question

  • Hi,

    My Sqlserver installed drive has a capacity of 500 GB.  For the last two week it has been observed that for a particular day in night the transaction log is becoming full...This is happening every Sunday night at 12 AM...Resulting the Sage X3 Application will stop working...

    Kindly advice a solution to track down the issue.

    Thanks

    Robin

    Tuesday, September 8, 2015 9:59 AM

Answers

  • Hi,

    Thanks.. Can you explain to me in Detail..Like how can i increase the frequency of log backups? What steps i need to follow? This issue started from two weeks before Only..So if we are following what you have suggested will it give a Permanent Solution?

    You just need to go to job and right click on it and select properties. On page that pops up click on schedule and then click on view you would see something as below

    On occurs at every add required time. You can also refer to below link

    https://msdn.microsoft.com/en-us/library/ms191439.aspx


    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 Article

    MVP

    Tuesday, September 8, 2015 11:56 AM
  • Robin,

    Go to your log backup jobs(SQL Agent) and increase schedule like every half an hour or every one hour. Issue started because since 2 weeks transactions volume might have increased. transactions to a database will not be a constant value unless you keep it like that. If your DB is growing, then you would need to edit the log backup schedules also rather than waiting for issues to occur.

    When you increase the frequency of backups, it will make space inside log file which in turn provide space for other transactions to grow. But if you need space back to disk you would need to SHRINK the log file.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, September 8, 2015 11:59 AM

All replies

  • Robin,

    So did you found out what is running at 12 midnight? Any jobs , ET pacakages, Index operation etc

    What is output of

    select log_reuse_wait_desc from sys.databases where name='db_name'


    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 Article

    MVP


    • Edited by Shanky_621MVP Tuesday, September 8, 2015 10:04 AM
    • Proposed as answer by Ice Fan Friday, September 18, 2015 1:35 AM
    Tuesday, September 8, 2015 10:03 AM
  • Hi,

    Thanks for the quick response, after running the above query i got the O/P as log_bckup

    Robin

    Tuesday, September 8, 2015 10:17 AM
  • Hi,

    Thanks for the quick response, after running the above query i got the O/P as log_bckup

    Robin

    There you go its log backup which is holding the logs and preventing it from getting truncated and reutalized. So I would suggest you to increase the frequency of log backups so that frequent log backups truncates the logs keeps control on its size. Then IF YOU WANT TO RECOVER space you can shrink ONCE

    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 Article

    MVP

    Tuesday, September 8, 2015 10:49 AM
  • Hi,

    Thanks.. Can you explain to me in Detail..Like how can i increase the frequency of log backups? What steps i need to follow? This issue started from two weeks before Only..So if we are following what you have suggested will it give a Permanent Solution?

    Thanks

    Robin

    Tuesday, September 8, 2015 11:40 AM
  • Hi,

    Thanks.. Can you explain to me in Detail..Like how can i increase the frequency of log backups? What steps i need to follow? This issue started from two weeks before Only..So if we are following what you have suggested will it give a Permanent Solution?

    You just need to go to job and right click on it and select properties. On page that pops up click on schedule and then click on view you would see something as below

    On occurs at every add required time. You can also refer to below link

    https://msdn.microsoft.com/en-us/library/ms191439.aspx


    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 Article

    MVP

    Tuesday, September 8, 2015 11:56 AM
  • Robin,

    Go to your log backup jobs(SQL Agent) and increase schedule like every half an hour or every one hour. Issue started because since 2 weeks transactions volume might have increased. transactions to a database will not be a constant value unless you keep it like that. If your DB is growing, then you would need to edit the log backup schedules also rather than waiting for issues to occur.

    When you increase the frequency of backups, it will make space inside log file which in turn provide space for other transactions to grow. But if you need space back to disk you would need to SHRINK the log file.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, September 8, 2015 11:59 AM