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.
ROLLBACK
won't affect these in the same way either. It is not clear why you have this all in a transaction anyway from the snippet shown)