Mike Rod was monitoring the database backups and noticed that disk space usage for one particular database shot up about 400% since last the last backup. This was a bit unusual, especially considering that there were no production code changes for that system in quite some time. Mike checked with the business to see if there was a sudden rush of 1,223,887,990 new customers that caused all the new data. There wasn't. He went to a developer on the system to see what was going on.

"Aw crap," the developer explained, "not this again! They're just temp tables; I'll get you a script to clean them up."

The developer's explanation didn't sit so well with Mike. Temp tables are, after all, temporary and should not be sticking around long enough to be backed up. Secondly, temp tables are created in the "temp" database and wouldn't even be a part of an application database backup. Mike decided to look into the issue a bit further.

The root of the problem was a 5,275-line stored procedure that ran on a nightly basis and updated sales, commissions, and all sorts of other data within the application. How Mike had never come across this procedure before is anyone's guess. How the procedure actually managed to function for as long as it did is even a bigger mystery.

Unlike just about every other aggregate data computing script, this procedure managed to do all of its work without a single UPDATE, INNER JOIN, OUTER JOIN, or, really, anything but a SELECT INTO statement. It did its work by created a whole slew of "temp" tables in this manner: 

INSERT INTO [__TMP_STEP05SC]
SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
  FROM [__TMP_STEP04SC]
 GROUP BY [Itm_Nm]
 ORDER BY 2

As you can see, the "temp" tables aren't actually temp tables. They're normal tables that sit right along side production tables and stick to the database like ants to the honey. Unlike "real" temp tables, these "temp" tables are not automagically cleaned up by the database engine when the procedure finishes running. Instead, the procedure cleans up for itself with a massive series of DROP TABLE statements ... 

DROP TABLE [__TMP_STEP01SC]  
DROP TABLE [__TMP_STEP02SC]  
DROP TABLE [__TMP_STEP03SC]  
DROP TABLE [__TMP_STEP04SC]  
DROP TABLE [__TMP_STEP05SC]
DROP TABLE [__TMP_STEP06SC]
... snip ...
DROP TABLE [__TMP_STEP32SC]
DROP TABLE [__TMP_STEP33SC]
DROP TABLE [__TMP_STEP34SC]
... snip ...
DROP TABLE [ResltRecrd03SC]
DROP TABLE [ResltRecrd04SC]
DROP TABLE [ResltRecrd05SC]
... snip ...
DROP TABLE [FinalResults_8]

The stored procedure was pseudo-governed by a careful balancing act: the application was disabled while ran, its code was never changed, it could never be executed twice at the same time. But the one thing that it didn't account for a NULL-value error. Or a divide-by-0 error. Or a truncation error. Or, really, any other error.

An error in the procedure meant that the procedure would quit unexpectedly. This, in turn, meant that DROP TABLE statements wouldn't be run. This led to a failure of the SELECT INTO statements because the tables already exist. And this ultimately meant that the careful adagio for the life and the death of all of the "temp" tables would never play out again.

There was little Mike could do to stop this madness, so he ran the "cleanup script" from the developer and recommended that conditional DROP TABLE statements be placed at the beginning of the procedure. "Oh, no no no," the developer replied, "we can't possibly risk altering the code for this procedure; the risk of failure is just too high."

Digital Donkey Mug Update - Due to a shipping delay from the vendor, I *just* received the mugs today. I will be mailing them out ASAP.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!