好文：err的解释(err是什么意思)_热评

一.需求背景

SQL Server开源的归档工具不多，DBA一般都是通过计划任务来触发执行，执行的脚本多是SP或者是SSIS包。SSIS包的性能稍好一些，但是维护更新成本高些。所以更常见的是通过SP脚本来实现归档操作。

当数据库规模较小时，可以方便的直接在数据库上进行脚本的编写部署。但是随着数据库越来越多，管理维护成本就会越来越大，越来越不方便。现在我们实行的方式是通过中央管理器来管理众多的数据库备份（这是在拥有专门的备份程序前的一个过渡方案）。我们将归档基础配置信息、归档运行历史记录、异常报错等数据统一维护在中央数据库上。如此,可以方便统一的查看、管理和维护。

二.主要架构

三.主要关联表

2.1 归档基础配置表

表字段含义，请耐心查看字段说明。

CREATE TABLE [dbo].[DBData_ArchiveConfig](

[ID] [int] IDENTITY(1,1) NOT NULL,

[IP] [varchar](50) NULL,

[DBName] [varchar](50) NULL,

[DataTable] [varchar](50) NULL,

[TargetIP] [varchar](50) NULL,

[TargetDB] [varchar](50) NULL,

[TargetTable] [varchar](50) NULL,

[Prerequisite] [varchar](300) NULL,

[DelMaxQTY] [int] NULL,

[IsCheckOrderID] [int] NULL,

[SP_Name] [int] NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL

) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=NServer IP(数据位于中央管理器中，所以归档数据库库所在的IP要维护，可维修虚拟的IP) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NIP

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N要归档的数据库 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDBName

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N要归档的表 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDataTable

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的IP , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetIP

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的数据库 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetDB

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的表 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetTable

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N归档条件 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NPrerequisite

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N循环中一次归档删除的数据量 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDelMaxQTY

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N此为备用字段，考虑可能有些表，会和其他表关联 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NIsCheckOrderID

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N为提高并发度，一个DB对应的归档SP可能是多个，通过此列，进行分组。 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NSP_Name

EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N此为拓展字段，原计划根据开始时间、结束时间，每天可以多个时间段内执行 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NStartTime

2.2 归档运行的Log表

CREATE TABLE [dbo].[DBData_ArchiveLog](

[ID] [int] IDENTITY(1,1) NOT NULL,

[IP] [varchar](30) NULL,

[DBName] [varchar](30) NULL,

[DataTable] [varchar](80) NULL,

[BakQTY] [varchar](30) NULL,

[BakStartDate] [datetime] NULL,

[BakEndDate] [datetime] NULL

) ON [PRIMARY]

2.3 异常错误信息表

执行的过程中会外包一层 try...catch,将操作过程中的错误信息保存在表 DBData_ArchiveErrLog。表结构如下：

CREATE TABLE [dbo].[DBData_ArchiveErrLog](

[ID] [int] IDENTITY(1,1) NOT NULL,

[IP] [varchar](30) NULL,

[DBName] [varchar](60) NULL,

[DataTable] [varchar](80) NULL,

[TargetIP] [varchar](30) NULL,

[TargetDB] [varchar](60) NULL,

[TargetTable] [varchar](80) NULL,

[Errormsg] [nvarchar](max) NULL,

[TransDateTime] [varchar](30) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

四. 存储过程相应的主要代码

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[SP_XXXXX_DataArchive]

SET NOCOUNT ON;

DECLARE @sql1 VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

DECLARE @sql2 VARCHAR(MAX)

DECLARE @IP VARCHAR(MAX)

DECLARE @DBName VARCHAR(MAX)

DECLARE @DataTable VARCHAR(MAX)

DECLARE @TargetIP VARCHAR(MAX)

DECLARE @TargetDB VARCHAR(MAX)

DECLARE @TargetTable VARCHAR(MAX)

DECLARE @Prerequisite VARCHAR(MAX)

DECLARE @DelMaxQTY INT

DECLARE @StartTime DATETIME

DECLARE @EndTime DATETIME

DECLARE @qty INT

DECLARE @ISCHECKORDERID INT

----Carson 2018-12-17 备份数据的时间往往比删除的时间长3倍,因此,如果考虑将备份的操作转移到辅助库,将会对线上的操作影响降至更低

DECLARE @BakDateIP VARCHAR(30)

set @BakDateIP=[XXX.XXX.XXX.XXX].-------后面一定要有一个点

--------------------------------------------------归档操作---------------------------------

DECLARE DBName CURSOR

FOR

SELECT IP ,

DBName ,

DataTable ,

TargetIP ,

TargetDB ,

TargetTable ,

Prerequisite ,

DelMaxQTY ,

ISCHECKORDERID ,

StartTime ,

EndTime

FROM [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]

WHERE DataTable <>

AND TargetTable <>

AND DBNAME = XXXXXXXXX and SP_Name=?????

OPEN DBName

FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP, @TargetDB,

@TargetTable, @Prerequisite, @DelMaxQTY, @ISCHECKORDERID,

@StartTime, @EndTime

WHILE ( @@fetch_status = 0 )

BEGIN

DECLARE @datetime DATETIME

IF @ISCHECKORDERID <> 1 AND @DataTable <>

BEGIN

SET @datetime = CONVERT(VARCHAR(10), GETDATE() - 30, 120)

SET @sql = Insert into [ + @TargetIP + ].

+ @TargetDB + . + dbo. + @TargetTable +

select * FROM + @BakDateIP + @DBName + . + dbo. + @DataTable +

with(nolock) where + @Prerequisite +

SET @sql1 = DECLARE @icount INTEGER

SELECT @icount = COUNT(1)

FROM + @BakDateIP + @DBName + . + dbo. + @DataTable +

where + @Prerequisite +

insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)

select + @IP + , + @DBName + , + @DataTable

+ ,@icount,getdate(),null

WHILE @icount > 0

BEGIN

DELETE TOP ( + CAST(@DelMaxQTY AS VARCHAR(10)) + )

FROM + @DBName + . + dbo. + @DataTable +

where + @Prerequisite +

SET @icount = @icount -(

+ CAST(@DelMaxQTY AS VARCHAR(10)) + )

WAITFOR DELAY 00:00:01

END

BEGIN TRY

EXEC (@sql)

EXEC (@sql1)

END TRY

BEGIN CATCH

DECLARE @Errmsg AS nvarchar(MAX)

SELECT @Errmsg=ERROR_MESSAGE()

------0001 BEGIN SAVE ERR LOG IN TABLE

INSERT INTO [中央管理器].[中央管理数据库].[dbo].DBData_ArchiveErrLog ([IP] ,[DBName],[DataTable],[TargetIP],[TargetDB],[TargetTable],[Errormsg] ,[TransDateTime])

VALUES(@IP, @DBName, @DataTable, @TargetIP, @TargetDB, @TargetTable,@Errmsg,convert(VARCHAR(25),GETDATE(), 120))

------0001 END

-------------0002 BEGIN SEND EMAIL MESSAGE----------------

DECLARE @Subject AS nvarchar(200)

DECLARE @Body AS nvarchar(MAX)

DECLARE @SPName AS nvarchar(MAX)

SET @Subject = 数据库归档异常 -重要!;ServerIP: + @IP + DB: + @DBName

SET @SPName =

SET @Body = Dear All, ServerIP: +@IP + ; DataBase: + @DBName+ 上的Table归档异常,请及时检查!!!

You can get detail information from the table.

SET @Body = @Body+

SELECT @SPName = @SPName +

SET @Body = @Body + @SPName + ServerIPDBNameTableNameTargetIPTargetDBErrmsgTransDateTime+ CAST(@IP AS NVARCHAR(50))+ + CAST(@DBName AS NVARCHAR(50)) + +CAST(@DataTable AS NVARCHAR(50))+

+ CAST(@TargetIP AS NVARCHAR(20))+ + CAST(@TargetDB AS NVARCHAR(50))+ + SUBSTRING(@Errmsg,1, 100)+ + CONVERT(varchar(100), GETDATE(), 21)+

SET @BODY=REPLACE(@BODY,,)

IF REPLACE(@BODY, ,)<>

BEGIN

DECLARE @AllEmailToAddress varchar(3000)=

DECLARE @AllEmailCcAddress varchar(3000)=

DECLARE @Allprofile_name varchar(100)=

SELECT @AllEmailToAddress=

SELECT @AllEmailCcAddress=

SELECT TOP 1 @Allprofile_name=NAME FROM msdb.dbo.sysmail_profile

ORDER BY profile_id

EXEC msdb..sp_send_dbmail @profile_name = @Allprofile_name -- profile 名称

,@recipients = @AllEmailToAddress -- 收件人邮箱

,@copy_recipients=@AllEmailCcAddress

,@subject = @Subject -- 邮件标题

,@body = @BODY -- 邮件内容

,@body_format = HTML -- 邮件格式

,@file_attachments=

,@Importance = High

END

------------- 0002 end ------------

END CATCH

END

FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP,

@TargetDB, @TargetTable, @Prerequisite, @DelMaxQTY,

@ISCHECKORDERID, @StartTime, @EndTime

END

CLOSE DBName

DEALLOCATE DBName

DECLARE DELETETABLE CURSOR

FOR

SELECT IP ,

DBName ,

DataTable ,

TargetTable ,

Prerequisite ,

DelMaxQTY

FROM [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]

WHERE DataTable <>

AND TargetTable =

AND DBNAME = XXXXXXXXX and SP_Name=????

OPEN DELETETABLE

FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,

@TargetTable, @Prerequisite, @DelMaxQTY

WHILE ( @@fetch_status = 0 )

BEGIN

SET @sql1 = DECLARE @icount INTEGER

SELECT @icount = COUNT(1)

FROM + @DBName + . + dbo. + @DataTable +

where + @Prerequisite +

WHILE @icount > 0

BEGIN

DELETE TOP ( + CAST(@DelMaxQTY AS VARCHAR(10)) + )

FROM + @DBName + . + dbo. + @DataTable +

where + @Prerequisite +

SET @icount = @icount -(

+ CAST(@DelMaxQTY AS VARCHAR(10)) + )

WAITFOR DELAY 00:00:01

END

PRINT @sql1

EXEC (@sql1)

FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,@TargetTable, @Prerequisite, @DelMaxQTY

END

CLOSE DELETETABLE

DEALLOCATE DELETETABLE

五.补充数据

1.数据库归档，一般都是先将当前库的历史数据归档到历史库，再将当前库的历史数据删除。这两个阶段，一般是前者耗时较多（一般都在2:1以上），虽然可以在select 过程加上nolock，但是或者I/O或者网络等原因，其实这个阶段对应用程序的影响还是比较大的。所以，建议将这两个阶段物理分开，即如果有配置AlwaysOn，请将第一个阶段在辅助数据库中执行。上面的SP示例，就是通过参数 @BakDateIP 来实现了这一作用。

2.存储过程中包含了try...catch，所以运行此sp就会很少报错，某一个表的异常不会相互影响。例如，我们常见的当前库、历史库由于表结构变更而导致的不一致，此情况出现后，try..catch可以捕捉到异常，将异常记录在档，并将此信息以邮件的形式发送给指定人，但整个SP不会执行失败。并且还会跳过这一个异常，继续执行下一个备份归档表的归档。

本文版权归作者所有

好文要顶关注我收藏该文

作者：东山絮柳仔

原文：https://www.cnblogs.com/xuliuzai/p/10168858.html

聚热点 juredian

好文：err的解释(err是什么意思)

你不可不知的植物神经紊乱危害！！...

你认为章子怡到底是个什么样的人？

青海省企业职工55到85岁退休是...

最新