June 13, 2012 at 10:23 am
I have a client that ran out of tempdb space. They had an enormous amount of temp tables that were several days old. It seems these tables were linked to spids that have long since closed. Also our code drops the temp tables when done even on error. Certainly we could have some place in the code that it doesn't do the drop, but the odd thing is that when i run the script below, all the table names are #StringOfNumbers. All our code would generate temp tables with a meaningful name such as #PK_IDList, or #myTable or something that makes sense. We do not have temp table creation with just numbers as the name.
So what would leave a bunch of temp tables around in tempdb that (at least it seems) that the SPID's had long since closed\ended and that have only numbers in the name. The script below indicates our app login, that nothing else uses, created the tables.
Index rebuilds come to mind. I am researching that now. Any help would be much appreciated.
Script:
SELECT DISTINCT te.name, t.Name, t.create_date, SPID, SessionLoginName
FROM ::fn_trace_gettable(( SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc'
FROM sys.traces -- read all five trace files
WHERE is_default = 1
), DEFAULT) trace
INNER JOIN sys.trace_events te on trace.EventClass = te.trace_event_id
INNER JOIN TempDB.sys.tables AS t ON trace.ObjectID = t.OBJECT_ID
WHERE trace.DatabaseName = 'TempDB'
AND t.Name LIKE '#%'
AND te.name = 'Object:Created'
AND DATEPART(dy,t.create_date)= DATEPART(Dy,trace.StartTime)
AND ABS(DATEDIFF(Ms,t.create_date,trace.StartTime))<50 --sometimes slightly out
ORDER BY t.create_date
Jimmy
"I'm still learning the things i thought i knew!"June 13, 2012 at 10:28 am
If you mean things like #78F2A3, they're one of two things
* Table Variables
* Cached shells of temp tables that SQL has noticed are often used. Done to reduce contention and optimise the creation of temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2012 at 10:36 am
Yes that's exactly what i mean. Can you point me in the direction as to how to figure out which one they are? If they are chached shells, how long should they stay around? Is the caching a feature that came in 2008 or 2008 R2? Also the size of these are 0, so cached shells is probably it. The data file is 24GB but all space is free and available practically but there are hundreds of these tables. The whole reason I am looking into this is that the temp log filled, but that's because they share drive with temp data file. Now i am getting the client to fix all that and isolate and size properly. So maybe I am chasing a red hearing as they say with the cached shells, since really its just the temp data file grew faster than the temp log and the log had no space when it needed to grow (temp db data grew to 24.8 GB on a 25GB drive). FUN FUN.
GilaMonster (6/13/2012)
If you mean things like #78F2A3, they're one of two things* Table Variables
* Cached shells of temp tables that SQL has noticed are often used. Done to reduce contention and optimise the creation of temp tables.
Jimmy
"I'm still learning the things i thought i knew!"June 13, 2012 at 10:45 am
imSQrLy (6/13/2012)
Yes that's exactly what i mean. Can you point me in the direction as to how to figure out which one they are?
Not sure there is a way.
If they are chached shells, how long should they stay around? Is the caching a feature that came in 2008 or 2008 R2? Also the size of these are 0, so cached shells is probably it.
Not sure how long, there's probably some ageing mechanism built in. It's from 2005 onwards.
If they're tiny and stay around a while, most likely the cached shells (they'll be 16 kb iirc, one IAM page, one data page)
The data file is 24GB but all space is free and available practically but there are hundreds of these tables. The whole reason I am looking into this is that the temp log filled, but that's because they share drive with temp data file.
The caching wouldn't have caused the log to fill. All the cached shells show is that there has been temp table usage. Basically when you say DROP TABLE #myTable (or let it go out of scope) SQL instead clears it and puts it onto a list of 'ready for reallocation'. It's to reduce contention on the allocation pages (which was a major problem on SQL 2000)
Now, what uses the temp tables could have caused the log to fill, but for that you'll need to look elsewhere. Probably starting with these on a regular basis
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2012 at 10:57 am
Thanks. You are always so helpful!
Jimmy
"I'm still learning the things i thought i knew!"Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply