By: Jeremy Kadlec | Comments | Related: 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 intSET @RetentionPeriod = 30 SELECT [Name], Principal_Id, Schema_Id, Type_Desc, Create_Date, Modify_DateFROM 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 ONDECLARE @RetentionPeriod intSET @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 OFFGO |
Dropping Old Archive Tables - Automated Script |
USE AdventureWorks; GO SET NOCOUNT ON-- Declaration statements for all variables DECLARE @TableName nvarchar(128) DECLARE @RetentionPeriod int DECLARE @CMD1 varchar(5000) -- Initialize the values -- Populate the cursor 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 ObjectNameCursorFETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableName WHILE @@FETCH_STATUS = 0 BEGIN -- Drop table command -- SELECT @CMD1 EXEC (@CMD1) FETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableNameEND CLOSE ObjectNameCursor SET NOCOUNT OFFGO |
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:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips