Error Occured in :Loln_spodcamtcalculationMessage : The transaction log for database 'SHRICITYREPORT' is full due to 'LOG_BACKUP'. Rollback Succesfully

Hi Experts iam using temp db in my sp after completing my work at the end of my sp am deleting that temp table.

Now i got a problem while running my sp for some 5 lakhs records at the end am getting the error log msg as

Error Occured in :Loln_spodcamtcalculationMessage : The transaction log for database 'SHRICITYREPORT' is full due to 'LOG_BACKUP'. Rollback Succesfully

how can i avoid the error how to handle in my sp. am attaching my sp
09.Loln_spOdcAmtCalculation.sql
kowsika deviAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
There isn't much you can do inside of your SP but to reduce the amount of changes in the main DB.
The error is a result of a combination of fixed transaction log size (so it cannot grow beyond a certain limit), full recovery model (requiring log backups) and missing log backups (which free up space in the log file, without changing its size). A simple solution is to run the SP after a log backup has been run, or to raise the maximum log file size.
0
 
kowsika deviAuthor Commented:
can any one pl check my execution plan as well as my sp for temp table deletion .
Execution-plan-March.sqlplan
09.Loln_spOdcAmtCalculation.sql
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Why? As said, it is an external constraint. Further, temp tables are part of TEMPDB, and that never receives a transaction log backup, so cannot be the issue. The error message clearly tells you that your issues are with the main DB.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Theres a heck of a lot of transactions within the BEGIN TRANSACTION / COMMIT TRANSACTION block.

For example, you update the same table several times.  Each time has two log entries. Times, however many rows you are cycling through. It does seem that there can be some tidy up.

When does it run ? How Often ? How long ?

There are other things you can do, like regular log backups, and housekeeping.

I urge you to read :  https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
What you need is to provide more disk space for your database transaction log file to grow.
Also check if the database transaction log file has any grow limitation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.