June 17, 2009 at 1:15 pm
Does anyone know if it is a good or bad idea to do regular (weekly?) maintenance on TempDB, and if so, which tasks would be recommended?
June 17, 2009 at 1:50 pm
What kind of maintenance would that be? Let's think about this...
Update Statistics? Nope, no tables there that we would want to update.
Rebuild/Reorganize Indexes? Again, nothing there to rebuild or reorganize.
Backup? Can't...system won't allow it.
Shrink? Shouldn't do that regularly anyways.
Not sure what else there would be.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2009 at 3:30 pm
I have encountered a few cases where a tempdb clean-up is needed especially when connection pooling is used, resulting in a connection being open for weeks. Please read "Working with tempdb in SQL Server 2005" at
http://technet.microsoft.com/en-us/library/cc966545.aspx
Some causes of un-used tempdb objects are:
1) Transactions left uncommitted and not rolled back can leave objects orphaned in tempdb.
2) An ODBC DSN with the option 'create temporary stored procedures' on, can leave objects as long as the connection exists.
3) When a SQL batch creates a temporary table via "create table #?" or a "select into #?" and the connection does not drop the temporary table, objects can exists.
4) Some applications create permanent or temporary tables in tempb to support scrolling and then do not remove the table.
Solutions:
For permanent objects, you could drop them when they have existed for some length of time.
For temporary objects, the only method of dropping the objects is to terminate the connection. This system view reports tempdb usage by session_id ( an alias for SPID )
select *
fromsys.dm_db_session_space_usage
wheresession_id > 50
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply