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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Qlemo"Batchelor", 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 WillsTopic 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ãoMSSQL 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.