GAPTHEGURU

Geek with special skills

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

02/16/2012 - Posted by | SQL Scripting, Sql Server, T-SQL | , , ,

2 Comments »

  1. 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 | Reply

    • 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 | Reply


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

%d bloggers like this: