Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify and Delete SQL Server Archive Tables for Rollback


By:   |   Read Comments   |   Related Tips: More > DBA Best Practices

Problem
In an recent tip (Backing up SQL Server Data for Rollback Purposes), the second option in the tip outlined steps to backup data prior to making a mass data changes.  This is a critical process in order to rollback if an issue arises.  Unfortunately, one missing component in that tip is identifying and deleting unneeded archive tables.  Depending on the deployment schedule, the amount of archived data can quickly add up, which expands backup\restore windows, increases tape\storage costs and may lead to a situation where unneeded data lingers for months or years.  Although I want to have a solid rollback plan, I also need to be able identify and delete the archive tables once they are 30 days old.  Any suggestions on how to do so?

Solution
Being able to identify and delete archived tables can be accomplished as long as you have a consistent set of rules.  The first rule is a consistent naming convention i.e. 'zBck_%'.  The second rule is the retention decision which identifies how long the table should be retained.  One way to identify the table is by a string in the table name i.e. 'zBck_30Days_%'or a consistent retention period such as 30 days for all tables.  Based on these two decisions, scripts can be built to meet these needs.  Let's jump into these three scripts:

Identifying Old Archive Tables - Fixed Number of Days

USE AdventureWorks;
GO

DECLARE @RetentionPeriod int
SET
@RetentionPeriod = 30

SELECT [Name], Principal_Id, Schema_Id, Type_Desc, Create_Date, Modify_Date
FROM sys.all_objects
WHERE [Name] LIKE 'zBck_30Days_%'
AND Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, Modify_Date, GETDATE()) > @RetentionPeriod;
GO
 

Dropping Old Archive Tables - Generated Scripts

USE AdventureWorks;
GO

SET NOCOUNT ON

DECLARE @RetentionPeriod int
SET
@RetentionPeriod = 30

SELECT 'DROP TABLE ' + '[' + S.[Name] + ']' + '.' + '[' + O.[Name] + ']' +
char(13) + 'GO' + char(13)
FROM sys.all_objects O
INNER JOIN sys.schemas S
ON O.Schema_Id = S.schema_id
WHERE O.[Name] LIKE 'z%'
AND O.Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, O.Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, O.Modify_Date, GETDATE()) > @RetentionPeriod;

SET NOCOUNT OFF

GO
 

Dropping Old Archive Tables - Automated Script

USE AdventureWorks;
GO

SET NOCOUNT ON

-- Declaration statements for all variables
DECLARE @SchemaName nvarchar(128)
DECLARE @TableName nvarchar(128)
DECLARE @RetentionPeriod int
DECLARE
@CMD1 varchar(5000)

-- Initialize the values
SET @RetentionPeriod = 30

-- Populate the cursor
DECLARE ObjectNameCursor CURSOR FOR

SELECT S.[Name], O.[Name]
FROM sys.all_objects O
INNER JOIN sys.schemas S
ON O.Schema_Id = S.schema_id
WHERE O.[Name] LIKE 'z%'
AND O.Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, O.Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, O.Modify_Date, GETDATE()) > @RetentionPeriod;

OPEN ObjectNameCursor
FETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

-- Drop table command
SELECT @CMD1 = 'DROP TABLE '+'['+@SchemaName+']'+'.'+'['+@TableName+']'+char(13)
-- SELECT @CMD1
EXEC (@CMD1)

FETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableName

END

CLOSE ObjectNameCursor
DEALLOCATE ObjectNameCursor

SET NOCOUNT OFF
GO
 

 

Additional Considerations

Another consideration for managing the archived tables are to store them in a separate database i.e. ArchiveDatabase.  This will help to keep the production databases free of archived objects and may help to minimize the tape/storage costs.  Nevertheless, the archive database should still be backed up based on the addition of new objects in case the data is potentially needed for a 30 day period.

Next Steps

  • When setting up an archive process, be sure to incorporate a deletion plan in order to maintain a reasonable set of objects so they do not linger for months or years.  One table can have a long term tangible cost, so not managing archive tables can be costly. 
    • On a related note, once team members turn over, the tables may never be deleted because the new team is concerned about ever deleting any data because it may be needed.  So build a set of rules and follow it.
  • Be sure to have a reasonable set of rules so that the deletion process can be managed and automated so that databases do not get bloated and the process is not manually intensive.
  • If you have an existing archive process be sure to incorporate a deletion process and consider the scripts from this tip.
  • Check out these related tips:

 



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools