Large update blows up tempdb due to begin-end transaction

I had copied a script that contained a begin - end transaction clause to an update script. In the database it has to update two indicator fields on about 250,000 records. Now I first tested it on my development system with no issues. When I dropped it into production on a new instance of SQL it would take over 5 minutes and crash. The crash was because of out of disk space. It ran out of disk space because the tempdb grew to over 5GB. Now why would the exact same script on the exact same dataset grow to 5GB on one server while on another it did not grow at all. It stayed at 15MB.
Who is Participating?

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

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.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
While the exact error that you ran into would have helped, I would take a guess that your production database uses the READ COMMITTED SNAPSHOT isolation level because of which [tempdb] ran out of what is known as the "Version Store" space. Your development instance may not have the snapshot isolation level set and hence it did not consume space on the Version Store.

Another option would be that the production instance is doing an online index rebuild (Enterprise Edition feature!) which may not be the case in development.

Here are some good resources to help you understand and monitor what's going on in your environments:
- Row Versioning Resource Usage:
- Objects producing the most versions in version store:

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
rwheeler23Author Commented:
The message was actually a timeout message. In development the update query ran in 2 seconds. In production it took over 5 1/2 minutes. This was part of the reason why I ran the query directly in SSRS. While the query was running I watched the tempdb just keep growing and growing until the disk was full. It was then that I received the out of disk space message. I will take a look a the resources you suggest.
Mark WillsTopic AdvisorCommented:
What is the recovery model on the development machine ?

It sounds like a difference in recovery model - maybe simple on dev and full in production. If life would only be that simple...

Other factors that can affect are frequency and timing of log backups, other jobs and background activity.

Arguably one of the more telling is
SELECT  [name],log_reuse_wait_desc
FROM    sys.databases

Open in new window

It starts to tell us what is happening/why  there is a wait state.

What the log file does, is constantly read/write /flush virtual logs in buffer pool. When it cant, it hast to write a new physical page to disk. And so, the log file must grow.

If you can run that very simple select - it might help reveal and of course, it may have been resolved by now - but worthwhile running and remembering if it happens again.

Post the result.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Scott PletcherSenior DBACommented:
Just curious: your prod SQL instance is limited to 5GB for tempdb?!  That seems really low.
Mark WillsTopic AdvisorCommented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is your tempdb that's growing and not the database you're working on.
Are your script handling temporary tables? If so, you must be storing a lot of data on those temporary tables. Giving more space to the tempdb should be a good idea. The timeout is mostly the time that the SQL Server engine realizes that the tempdb can't grow anymore and not exactly the time for running your script.
rwheeler23Author Commented:
I have no idea who installed sql and neither does the client. The update query was as simple as

SET F1=T2.F1,F2=T2.F2

It is something definitely on their end. When I asked about it tell me it was a test server and the real production is under construction. Which was good news because that server was grossly underpowered for anything to be done.

I will run a few of the mentioned scripts just to find out how underpowered that server is.
rwheeler23Author Commented:
SELECT  [name],log_reuse_wait_desc
FROM    sys.databases

The result of this was NOTHING on all databases. The recovery model was simple.
Mark WillsTopic AdvisorCommented:
Hmmm... With SIMPLE recovery, then wouldnt really expect a lot of LOG activity. Sure there is some activity but would start looking at some other activities in background that might impact. Especially if timing out when wanting to write to log / log full.

Do you have any specific error messages - checked the error logs ?
rwheeler23Author Commented:
Thanks. I will wait for the new server.
Mark WillsTopic AdvisorCommented:
Given the circumstances, could put it down to an isolated case. Works on your Dev server, Problems on a Test Server, Production Server under construction. It isnt exactly a stable environment.

Clearly a wait state, likely associated with disk activity or resource contention has meant log reuse was compromised. We could try to investigate further.
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
Microsoft SQL Server

From novice to tech pro — start learning today.