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?
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:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
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.