SQL: Cleaning up TempDB

Cleaning up TempDB

Posted on December 31, 2007by Jeff Randall

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

References

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/

C#: Except Extension Method Example

Enumerable.Except(Of TSource) Method (IEnumerable(Of TSource), IEnumerable(Of TSource), IEqualityComparer(Of TSource))

The following example shows how to implement an equality comparer that can be used in the Except method.

public class Product

{

public string Name { get; set; }

public int Code { get; set; }

}

// Custom comparer for the Product class

class ProductComparer : IEqualityComparer<Product>

{

// Products are equal if their names and product numbers are equal.

public bool Equals(Product x, Product y)

{

//Check whether the compared objects reference the same data.

if (Object.ReferenceEquals(x, y)) return true;

//Check whether any of the compared objects is null.

if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))

return false;

//Check whether the products’ properties are equal.

return x.Code == y.Code && x.Name == y.Name;

}

// If Equals() returns true for a pair of objects

// then GetHashCode() must return the same value for these objects.

public int GetHashCode(Product product)

{

//Check whether the object is null

if (Object.ReferenceEquals(product, null)) return 0;

//Get hash code for the Name field if it is not null.

int hashProductName = product.Name == null ? 0 : product.Name.GetHashCode();

//Get hash code for the Code field.

int hashProductCode = product.Code.GetHashCode();

//Calculate the hash code for the product.

return hashProductName ^ hashProductCode;

}

}

After you implement this comparer, you can use sequences of Product objects in the Except method, as shown in the following example.

Product[] fruits1 = { new Product { Name = "apple", Code = 9 },

new Product { Name = "orange", Code = 4 },

new Product { Name = "lemon", Code = 12 } };

Product[] fruits2 = { new Product { Name = "apple", Code = 9 } };

//Get all the elements from the first array

//except for the elements from the second array.

IEnumerable<Product> except =

fruits1.Except(fruits2, new ProductComparer());

foreach (var product in except)

Console.WriteLine(product.Name + " " + product.Code);

/*

This code produces the following output:

orange 4

lemon 12

*/

Reference:

http://msdn.microsoft.com/en-us/library/bb336390.aspx

Virtual, Override and new Keyword in C#

Virtual, Override and new Keyword in C#

Consider following class Hierarchy:

Multilevel Inheritance

In this example, we will consider three classes which are TestA, TestB and TestC.

namespace OOPS_Concept

{

class TestA

{

public void display() { Console.WriteLine("TestA – display()"); }

}

class TestB : TestA

{

public void display() { Console.WriteLine("TestB – display()"); }

}

class Test

{

static void Main(string[] args)

{

TestA a;

TestB b;

a = new TestA();

b = new TestB();

a.display(); // TestA – display()

b.display(); // TestB – display()

a = new TestB();

a.display(); // TestA – display()

Console.Read();

}

}

}

output :

TestA – display()
TestB – display()
TestA – display()

Program will compile and run successfully but

  • The Problem of above code is that, third output should be “TestB –display()” because the variable a have the object of B.
  • …Test.cs(15,21): warning CS0108: ‘OOPS_Concept.TestB.display()’ hides inherited member ‘OOPS_Concept.TestA.display()’. Use the new keyword if hiding was intended.

Lets resolve the problem 1.