Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb?
Using tempdb is fine in most cases, but some of the operations I've been seeing generate a lot of IO and in some cases end up saturating the templog file.
So here is a typical situation
-- create the table create table #some_table(t int) -- Do some work declare @rownum int = 1 begin transaction while 1=1 begin insert into #some_table values(@rownum) if @rownum > 5000000 break set @rownum = @rownum + 1 end
Work continues on until @rownum reaches 5000000 and because its within a transaction, tempdb can't shrink. In the mean time, alerts start showing up and the LOGS disk fills up. Doing a SHRINKFILE is useless because the transaction is left open.
The work has to be done in tempdb because the process that created that temporary table could be run multiple times or there could be multiple processes that use the same table name.
-- show the contents of #some_table set statistics io on select * from #some_table set statistics io off -- results (5000001 row(s) affected) Table '#some_table_________________________________________________________________________________________________________00000000001F'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
So until I find a way to simulate that temporary table per session ability in tempdb, we have to continue tracking down offending queries and basically play air traffic control with tempdb.
Here is the situation I'm currently dealing with
tempdb's log is currently at 40GB+ and climbing fast.