Sql-server – TempDB will not shrink. No Open Transactions

You've got two different questions in here:

Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.

Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.

Q: (DBCC SHRINKFILE) seems to not work very often in 2008

It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.

There is no direct way to copy temp table behavior into non user databases. This starts with the easy collision preventions and continues to several advantages of temp tables (like reduced logging) that just can't be reproduced.

To create data sets in user databases that avoid collisions you have basically two options:

1. put all the data in one table and deal with the separation using some kind of session id in the table

The most obvious session id you could use for the single table approach is the @@SPID. But you could use other "random" values too. To achieve greater physical separation, you could partition the table on that column. The obvious disadvantage is that all sessions have to use the same table layout.

2. create a random table name and use dynamic sql from then on.

The random-table-name option is going to make your code very hard to read. But as SQL Server does not allow statements to access objects based on names stored in variables, dynamic SQL is your only option.

If you go this way I recommend to include the current session_id and request_start_time (from sys.dm_exec_requests) as well as a guid in the name. That way it is easy to write some kind of garbage collector that goes around and deletes tables that got left behind. Depending on your object life time expectations you might have to use a different time stamp like the connect_time in sys.dm_exec_connections but the idea is the same. Once the request with that start time is gone, the object cannot be of use anymore because the variable holding its name went out of scope too.