文件管理的 SQL 自动清理基础

引言

文件管理是所有企业，无论大小，最终都要处理的事情。但文件管理涵盖的范围不仅仅是企业生成的文档。每当企业在 SQL Server 中存储信息时，企业必须提供一种方法来严格遵守其文件管理程序。请注意，我说的是 **IT 必须提供方法，而不是制定规则**。有什么区别？IT 通常不拥有数据！

背景  

过去，DBA 使用各种方法从 SQL Server 中清理数据，但在许多情况下，DBA 只是为了保持数据库大小可管理。在我做自由职业者时，我经常看到一些数据非常陈旧，已经没有保存的意义了。在我为一家财富 500 强公司工作的日常工作中，这不仅仅是不可取的，而是不可接受的。数据最终对用户来说不再有意义，如果保留可能会对企业造成损害。

但是我们应该如何清理数据呢？如果您有一个小的 SQL Server，可能只有十几个数据库，按数据库创建 SQL 作业似乎是合理的。但如果数据所有者需要按表进行不同的清理呢？ 这样您就会创建大量 SQL 作业，它们会使您的环境杂乱无章，并且在数据所有者想要更改清理设置时需要大量维护。当创建新数据库或表时又该怎么办？ 通常是在编码过程中或临时进行的，并且抱着稍后返回设置清理的良好意愿。

使用代码

这是非常简单的代码，可以扩展到远远超出我目前提出的基本形式。我想保持简单，以便每个独特的环境都可以做自己想做的事情。也许您想添加一个前端网页，以便数据所有者可以更改自己的清理设置，或者添加一些额外的字段等。

第一步是创建两个新表来存储管理数据：

（请注意，我首先创建了一个新数据库“DataPurge”。我喜欢在我的服务器中进行分离）

USE [DataPurge]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DataPurgeHistory](
	[DBName] [varchar](50) NOT NULL,
	[TableName] [varchar](50) NOT NULL,
	[DateStamp] [datetime] NULL,
	[PurgeNeeded] [bit] NOT NULL,
	[NewTable] [bit] NOT NULL,
	[YearsToKeep] [int] NULL,
	[PurgeField] [varchar](50) NULL,
 CONSTRAINT [PK_DataPurgeHistory] PRIMARY KEY CLUSTERED 
(
	[DBName] ASC,
	[TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[DataPurgeHistory] ADD CONSTRAINT [DF_DataPurgeHistory_PurgeNeeded]  DEFAULT ((0)) FOR [PurgeNeeded]
GO

ALTER TABLE [dbo].[DataPurgeHistory] ADD CONSTRAINT [DF_DataPurgeHistory_NewTable]  DEFAULT ((1)) FOR [NewTable]
GO  

这个表将用于实际存储清理数据。日期戳仅用于让我们知道最后一次清理该表的时间。PurgeNeeded 可以设置为 0，这样作业就不会清理选定的表。NewTable 用于告知我们需要为哪些表设置清理选择。YearsToKeep 是一个非常简单的年份偏移量，用于根据 PurgeField 删除数据。

USE [DataPurge]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DBTables](
	[DBName] [varchar](50) NULL,
	[TableName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 

这个表只是保留了此服务器内所有表的列表。

在实际作业之前，我们还必须在创建的两个表所在的数据库中设置一个视图，它将用于帮助我们向清理列表中添加新表。

USE [DataPurge]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwNewDBTable]
AS
SELECT     TOP (100) PERCENT dbo.DBTables.DBName, dbo.DBTables.TableName, dbo.DataPurgeHistory.DBName AS DBName2, 
                      dbo.DataPurgeHistory.TableName AS TableName2
FROM         dbo.DBTables LEFT OUTER JOIN
                      dbo.DataPurgeHistory ON dbo.DataPurgeHistory.DBName = dbo.DBTables.DBName 
                      AND dbo.DBTables.TableName = dbo.DataPurgeHistory.TableName
WHERE     (dbo.DataPurgeHistory.DBName IS NULL)
ORDER BY dbo.DBTables.DBName, dbo.DBTables.TableName

GO

现在我们已经设置好了一切，我们需要创建两个作业。第一个作业将检查已创建的任何新数据库和/或表。我将其设置为每天运行一次，但您可以根据需要创建任何适合您的计划。这是一个简单的作业，只有一个 T-SQL 步骤。

SET NOCOUNT ON 

Delete from DataPurge.dbo.DBTables

DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname) 
DECLARE 
     @SearchDb nvarchar(200) 
    ,@SearchSchema nvarchar(200) 
    ,@SearchTable nvarchar(200) 
    ,@SQL nvarchar(4000) 
SET @SearchDb=''%'' 
SET @SearchSchema=''%'' 
SET @SearchTable=''%Account%'' 
SET @SQL=''select ''''?'''' as DbName, t.name as TableName from [?].sys.tables t inner 
    join sys.schemas s on t.schema_id=s.schema_id WHERE ''''?'''' not in 
    (''''master'''',''''tempdb'''',''''model'''',''''msdb'''') AND s.name LIKE ''''''+@SearchSchema+''''''''
 
INSERT INTO DataPurge.dbo.DBTables (DbName, TableName) 
    EXEC sp_msforeachdb @SQL 
    
Insert into DataPurge.dbo.DataPurgeHistory (DBName, TableName)
	Select DBNAme,TableName from DataPurge.dbo.vwNewDBTable

下一个作业将根据我们之前创建的 DataPurgeHistory 表中的设置实际执行清理。同样，我将其设置为每天运行一次，但根据您的环境，您可以根据有意义的任何计划运行它。这个作业也只有一个步骤。

SET NOCOUNT ON 

DECLARE 
     @DbName nvarchar(200) 
    ,@Schema nvarchar(200) 
    ,@TableName nvarchar(200) 
    ,@PurgeField nvarchar(200) 
    ,@YearsToKeep nvarchar(200) 
    ,@SQL nvarchar(4000) 

Declare c Cursor For Select DBName,TableName,PurgeField,YearsToKeep from 
   DataPurge.dbo.DataPurgeHistory where PurgeNeeded = 1 and YearsToKeep is not null and PurgeField is not null
Open c 
 
Fetch next From c into @DBName,@TableName,@PurgeField,@YearsToKeep;
 Set @Schema=''dbo'';
 
While @@Fetch_Status=0 Begin 
      
SET @SQL=''Delete From '' + @DbName + ''.'' + @Schema + ''.'' + @TableName + '' where '' + 
          @PurgeField + '' < dateadd(year, -'' + @YearsToKeep + '', getdate());
			Update DataPurge.dbo.DataPurgeHistory set DateStamp = getdate() where 
			DBName = '''''' + @DBNAme + '''''' and TableName ='''''' + @TableName +'''''';''
 
     EXEC (@SQL )
   
   Fetch next From c into @DBName,@TableName,@PurgeField,@YearsToKeep; 
End 
 
Close c 
Deallocate c

是的，您可以将这些作业设置为一个包含两个步骤的作业，但我将它们分开，以便最终我们可以让它们在不同的计划中运行。

 那么它是如何工作的？  首先，第一个作业运行并获取所有新的数据库和表。这样您就可以为该数据库和表分配一个清理字段。接下来，您输入要保留的年数。最后，您需要将 PurgeNeeded 更改为 1，将 NewTable 更改为 0。这个作业的工作方式是，它只会清理包含所有必需信息且 PurgeNeeded 设置为 1 的行。

 这样，如果需要，您可以出于法律行动等原因暂停某些信息。您还可以简单地不清理包含静态数据的表（例如网页下拉菜单的信息）。

 当您的作业运行时，它将逐行浏览 DataPurgeHistory 表并根据请求清理数据。

兴趣点 

这段代码并不复杂，也易于重现/更改。根据您的环境，您或您的公司应该更改许多内容。一些例子：

• 我们的一些表不默认为 dbo，对于包含这些表的服务器，我们将 Schema 添加到了 Table Checker 的一部分。
• 我们添加了一些描述字段，以便数据所有者可以进行注释。
• 我们有前端网页，允许数据所有者根据预定的文件管理指南选择他们的清理设置。
• 网页包含一个下拉菜单，其中列出了所有可能的 datetime 字段，这些字段可用作所选表内的清理字段。
• 我们的一些表需要清理，而不是基于年数，因此我们添加了一个字段来告诉我们“ToKeep”列是基于什么（例如天、周、月或年）。

有很多方法可以扩展这个非常基础的清理框架。