Cleaning up TempDB

In many significantly large production environments, the TempDB can regularly grow with little or no thought towards maintaining some control over the lifespan of tables. In most situations, TempDB is not used heavily enough to be a problem, but occasionally due to non-ideal code, many tables are created and left with no regular cleanup.

While I am a proponent of using Table Variables over Temp Tables, the fact remains that many developers, and a significant number of DBAs use Temp Tables, and it is left in the hands of the DBA to deal with any complications caused by this.

The most obvious flaw in using Temp Tables in a 24/7 Productions Environment, is that if your SQL Server box never reboots, your TempDB never gets cleaned out (unless it’s by the code that created the Temp Tables, and that never happens. To deal with this issue, I have created a SP that runs once an hour, deleting Temp Tables that are more than 12 hours old (unless they reside in a Table (TempTableToKeep) that I use to store the names of tables I want to keep, and the date/time to finally delete them.

The code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TempTableToKeep]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = N’sp_DropTempTables’ AND type = ‘P’)
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @SQL AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = @Name AND type = ‘U’)
BEGIN
SET @SQL = ‘DROP TABLE tempdb..’ + @Name
–PRINT @SQL
EXECUTE sp_executesql @SQL
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO

Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

40 Responses to Cleaning up TempDB

  1. Pingback: Recommendable SQL Server Posts « DBA Weblog

  2. Luis says:

    THanks a lot… you are great!!!

  3. Rodibidably says:

    Thanks for the comment. I hope this code helped you out. 🙂

    At a past company, we had a ridiculous amount of code that created temporary tables and did not clean up after itself. This was my solution to keeping TempDB under control.
    At my current position I’ve written most of the code myself, so TempDB is used much less frequently, and usually cleaned out when it is used, so it’s much less of an issue, but still a job that runs regularly, just to keep the server cleaned up.

  4. sdj says:

    I can’t get this to work? And I have some strange behavior on the tempdb.

    I have been doing some testing and set to tempdb to start with 8MB and ‘By 10 percent, unrestricted growth’

    I then create a temporary table and my tempdb (file) growth to aprox 1GB. If I then select shrink files in the task menu Currently allocated space is still 8MB and Available free spare is negative.

    If I manually try to drop a the temporaty table I get this error: “Database name ‘tempdb’ ignored, referencing object in tempdb.
    Msg 3701, Level 11, State 5, Line 1
    Cannot drop the table ‘#temptabel2_________________________________________________________________________________________________________000000000008’, because it does not exist or you do not have permission.

    How do I get permission to do this? So it will work?

  5. Rodibidably says:

    If you don’t have rights to delete a table in tempDB manually this code won’t allow you to get around that. You’d need to make sure you have delete rights (DDLAdmin) in tempDB before testing this code.

    Once you can delete a table manually, then try running this code, and make sure it deletes the older tables.

    As it is designed, it deletes tables older than 12 hours, DATEADD(hh, -12, GETDATE()), which can be modified by changing the number of hours to something more appropriate for your organization. I’m make sure you don’t delete files under 30 minutes old, as they could still be in use by an application, but depending on your system, you may need to keep files older than that as well.

  6. Jessie says:

    When I try to delete anything from the tempdb i get:
    Msg 3701, Level 11, State 5, Line 1
    Cannot drop the table ‘#IRD________________________________________________________________________________________________________________00000000132C’, because it does not exist or you do not have permission.

  7. Rodibidably says:

    Jessie,

    If you don’t have rights to delete a table in tempDB manually this code won’t allow you to get around that. You’d need to make sure you have delete rights (DDLAdmin) in tempDB before testing this code.

    Once you can delete a table manually, then try running this code, and make sure it deletes the older tables.

  8. Marty says:

    I am logged onto the server via sql management studio as ‘sa’. I am still not able to drop any of the temp tables (manually). Correction…I am able to drop the temp tables I created using the same query. I’m just not able to remove the temp tables left behind by other sp, etc… I suspect those tables may have been create using ‘sa’. Would that matter?

  9. Rodibidably says:

    Marty,
    When you attempt to drop the tables, what message do you get? Do you not have perms, or is the table locked, or …?

    One issue could be that the table is still being used, and thus locked, which is why the code above includes:
    tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE()
    This ensures that only tables created at least 12 hours ago are deleted, as they are likely no longer in use (although you could raise or lower that time depending on your needs).

  10. Marty says:

    I get the same sort of message as Jessie did.

    Cannot drop the table…Because it does not exist or you do not have permission.

    I’m pretty sure these tables were created while I was developing a stored procedure. I had to reboot my PC for a non-related reason and forgot to drop the tables for these sessions. I would have thought just using an ‘sa’ login would let me do this.

  11. Rodibidably says:

    If you’ve got sysadmin rights are are still unable to drop the temp table (even manually using Management Studio) my guess would be that the table really does not exist;
    To test this, do a SELECT on the temp table and see if it comes back with results
    To fix this, either close Management Studio and see if it still shows up in TempDB after opening it back up; or restart the SQL Service on the server and then create a new temp table and try to delete that one

    Another possibility is that the temp table is being locked by the SP or some other method, in which case you’d want to look at all your running processes, and see if any of them are holding a lock on that temp table.

  12. majeed says:

    Mdf file size in temp db is growing unto 25 GB.
    Approximately 140 temporary are present which are not realized. Please give any suggestion except restarting the server.There is no opentransaction.

  13. Rodibidably says:

    majeed,

    There are no open processes, and yet you still can not delete the Temp Tables manually when logged in with SysAdmin rights?

    If you can delete them manually you should also be able ot delete them using the code as described above in the main article.

    Once the files are deleted, it’s a simple command to shrink the size of the database without having to restart the service.

  14. Pingback: A brief look at Rodibidably… « Rodibidably

  15. Pingback: A brief look at Rodibidably… « Rodibidably

  16. Shelley says:

    I know this is an old article, but I have a silly question. Would you create the table in the temp db and the procedure in master? Please advise.

    • Jeff Randall says:

      Shelly,
      You would not want to put this table in TempDB, because any time your SQL Server Service restarts it would remove this DB (unless you also created the table in the ModelDB as well.

      Also, I typically try to avoid creating too many objects in the MasterDB.
      Generally I create a DB for my administrative purposes and create the tables and SPs that are for administration in there.
      This has the advantage that as you build up your own admin tools over time, you can easily port them form box to box without worrying about disturbing anything in the MasterDB on a new box.

      This particular SP can then be called from that admin DB to clean out your TempDB as you require.

  17. Ashish says:

    nice article,
    but one question ..
    SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
    FROM TempTableToKeep
    you passing two value in cursor, how can it get two values if you have * in one table?

    • Jeff Randall says:

      Thank you

      You could easily change it to specify all field names (I am probably should have, since that is good practice to specify field names), such as:
      SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.object

      But since that table only has two fields, doing the * will return only those two fields.

  18. Jeff says:

    Jeff,

    This is not working for me, first of all couldnt create this SP because 2 values to cursor doesnt work (am using SQL Server 2008). Also how you can drop a temptable which has been created by another user context is it possible in SQL Server?

    Rgds
    Balaji

  19. Jeff Randall says:

    Balaji,
    Re not being able to create the SP:
    I know that sometimes WordPress changes the formatting of text, so please replace all quotes manually in the SQL Editor and it should create just fine…
    If it still fails to create, let me know the exact error msg and line where the error occurs.

    Re perms to drop temp tables:
    This SP assumes the user account that creates/runs it has the correct permissions to drop the temp table. If you’re able to manually drop the temp table, then this SP should be able to automatically drop it for you.

  20. aa says:

    A little confused on how this could possibly work for a # / temp table as the table accessible only to the session creating it. If the table does not get dropped at the end of the session it is visible from it’s entry in tempdbo.sysobjects but it cannot be dropped by the statement ‘drop table’ and passing in the name of the table as a variable. That only works for a persistent / non-tempDB table. You will always get the error ‘table does not exists or you do not have permissions’

    • Jeff Randall says:

      aa,
      What version of SQL Server are you running? On SQL 2005 and 2008 this code should work, since it looks at the actual names of the temp tables in SQL and not what they were named (i.e. it knows #temptable gets renamed internally to #temptable_________internalstringgoeshere).

  21. aa says:

    2005. Open a session in SSMS, create a #test table, insert a row. Open a second session in SSMS and search for the objects #test% in the tempDB and you will see its full name, just as in your code. Now run your code. After it finishes running, check for the object again and you will find it is still there. Switch back to the original session and select * from #test, it will return the row you inserted. The object will not disappear until this session which created it has been closed or you drop the table explicitly from within this session. The object is available to the session scope only. You can access the bale from another session if it is a global ## table not a local # table.

  22. Jeff Randall says:

    These days I try to use only Table Variables, and in the few cases where I must use temp Tables, I clean them up in the code that creates them. I typically only use this in environments where many people are running processes directly in SQL or where processes could fail, leaving temp tables behind. In these cases the original process has ended, but the table still exists because the process ended abnormally, and the table is free to be killed by another process (aka this process).

  23. aa says:

    If a process exists leaving a temp table behind the cleanup is done when SQL is restarted. I have not come across a situation where the #table can be deleted manually (well, not in SQL 2005). If you find you are in a situation where code regularly leaves #tables behind, someone needs to spend time addressing the code not trying to clear up #tables. As for table variables, the larger the dataset the slower the performance. In my experience they give better performance than # tables when the number of rows is less than 3,000

  24. JS says:

    So what happens then if you completely restart your server and the # temp tables in TempDB don’t disappear?

    • Anonymous says:

      JS – If you truly restart your server then the Temp Tables won’t be there because TempDB is recreated every time SQL Server is restarted.

  25. Pingback: SQL: Cleaning up TempDB | mytechconnect

  26. fm2xm says:

    We are using SQL2008R2 and run an ERP system. Taking a downtime outage to clear tempDB is an impossibility. I tried copy and pasting this script in SMSS, I’m getting syntax error. Can any place this script as an attachment somewhere where I can download from?

    I would be most helpful.

    Thank you

  27. twiggy says:

    Table variables can and will use the tempdb. If you have a temp variable with 100s to 1000s of records it will get “swapped” into the tempdb. Also the tempdb is used by lots of large queries internally. Many people are moving towards plopping the tempdb on a SSD drive or even RAM disk. Tempdb is the thing I wish MS would focus on. It seems silly that you can’t isolate memory for it directly. My generally grows to about 3 gigs….my server has 64 gigs of memory…why is it going to disk?

  28. Sean Sowell says:

    Trying to run the above script in sql 2008 r2 and receiving the following errors.

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘’’.
    Msg 2714, Level 16, State 6, Line 1
    There is already an object named ‘TempTableToKeep’ in the database.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘’’.
    Msg 102, Level 15, State 1, Procedure sp_DropTempTables, Line 12
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Procedure sp_DropTempTables, Line 21
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Procedure sp_DropTempTables, Line 23
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Procedure sp_DropTempTables, Line 23
    Incorrect syntax near ‘’’.

    Anyone else having the same problems.

    Thanks

  29. We absolutely love your blog and find almost all of
    your post’s to be what precisely I’m looking for.
    Would you offer guest writers to write content for you personally?
    I wouldn’t mind composing a post or elaborating on some of the subjects you write regarding here. Again, awesome site!

  30. Ayub says:

    Error when I execute this procedure : sp_DropTempTables
    Error:
    Msg 16924, Level 16, State 1, Procedure sp_DropTempTables, Line 15
    Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

  31. Narayan says:

    I got this error, when I ran in 2008R2.
    Msg 468, Level 16, State 9, Procedure sp_DropTempTables, Line 11
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

  32. Babji says:

    Hi Jeff,

    Script doesnt work on SQL Server 2014,

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TempTableToKeep]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
    DROP TABLE [dbo].[TempTableToKeep]
    GO
    CREATE TABLE [dbo].[TempTableToKeep] (
    [TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DateToDelete] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    IF EXISTS (SELECT name FROM sysobjects WHERE name = N’sp_DropTempTables’ AND type = ‘P’)
    DROP PROCEDURE sp_DropTempTables
    GO
    CREATE PROCEDURE sp_DropTempTables
    AS
    DECLARE @Cursor AS CURSOR
    DECLARE @Name AS VARCHAR(100)
    DECLARE @TableName AS SYSNAME
    DECLARE @Owner AS VARCHAR(100)
    DECLARE @SQL AS NVARCHAR(200)
    SET @Cursor = CURSOR SCROLL FOR
    SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
    FROM TempTableToKeep
    RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
    WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete < GETDATE())) OR
    ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete IS NULL))
    OPEN @Cursor
    FETCH FIRST FROM @Cursor
    INTO @Name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    If (@@FETCH_STATUS = 0)
    BEGIN
    IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = @Name AND type = 'U')
    BEGIN
    SET @SQL = 'DROP TABLE tempdb..' + @Name
    PRINT @SQL
    EXECUTE sp_executesql @SQL
    END
    FETCH NEXT FROM @Cursor
    INTO @Name
    END
    END
    CLOSE @Cursor
    DEALLOCATE @Cursor
    GO

  33. Anonymous says:

    This Query will not work properly in SQL Server 2012

  34. Alex_93 says:

    Hi guys, in the query there are some errore and variable not used.
    The temporary table is not needed since it is never populated
    The cursor is wrong.
    I try the query on SQL 2012 and it works.
    You only need to create a procedure and schedule it:

    CREATE PROCEDURE sp_DropTempTables
    AS
    DECLARE @Cursor AS CURSOR
    DECLARE @Name AS VARCHAR(100)
    DECLARE @TableName AS SYSNAME
    DECLARE @SQL AS NVARCHAR(200)
    SET @Cursor = CURSOR SCROLL FOR
    SELECT tempdb.dbo.sysobjects.name
    FROM tempdb.dbo.sysobjects
    WHERE (tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U')
    OPEN @Cursor
    FETCH FIRST FROM @Cursor INTO @Name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SET @SQL = 'DROP TABLE tempdb..' + @Name
    EXECUTE sp_executesql @SQL
    FETCH NEXT FROM @Cursor INTO @Name
    END
    CLOSE @Cursor
    DEALLOCATE @Cursor
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s