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
2 Comments »
Leave a reply to gaptheguru Cancel reply
-
Archives
- June 2013 (2)
- December 2012 (4)
- November 2012 (1)
- August 2012 (8)
- July 2012 (1)
- June 2012 (3)
- May 2012 (14)
- April 2012 (8)
- March 2012 (7)
- February 2012 (4)
- January 2012 (9)
- December 2011 (6)
-
Categories
-
RSS
Entries RSS
Comments RSS
gaptheguru – Thank you for posting this useful code. I am a dba in training, just inherited the duties three weeks ago. We are running SQL 2008 R2.
I tried copying and pasting your code in SSMS query window and it is not liking syntax in several areas – just wondering, are you aware of any issues of this query running under SQL 2008 R2?
In particular, it complains for the following in SQL 2008 R2:
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
Regards,
fm2xm
Comment by fm2xm | 06/24/2012 |
this works on sql server 2005 and sql server 2008. I will try this in my sql 2008 r2 test environment and get back to you.
Thanks for your feedback.
Comment by gaptheguru | 07/10/2012 |