Sql Server 定期将日志表（大表）三个月以前的数据进行转移

Sql Server 定期将日志表（大表）三个月以前的数据进行转移

背景描述

公司数据库使用Sql Server 2008R2，应用日志直接写入sql server 数据库，由于数据较大日志表有几千万掉数据查询速度较慢，经确认日志表保留三个月数据即可，旧的数据移动到其他库！

具体操作

由于项目比较多都使用一台数据库服务器，多个库多个日志表记录日志数据，如果手动转移数据可能难免会出错！
这里使用存储过程进行大表数据迁移，大概思路如下：
①、使用Applogs数据库存放老的日志数据。
②、在Applogs库下创建存储过程。
③、存储过程先创建表，表名=原表名+当前日志
④、从原表查询三个月以上的数据插入到Applogs下新表中。
⑤、Delete原表中三个月以上的数据！

存储过程如下：

USE [Applogs]
GO
/****** Object:  StoredProcedure [dbo].[dbLog]    Script Date: 04/01/2021 16:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[oldLog]

AS
BEGIN
-- 创建表
	SET NOCOUNT ON;
	SET ANSI_PADDING ON
	declare @date VARCHAR(100)
    SET @date = CONVERT(varchar(100), GETDATE(), 112)--按当前日期创建表
	Declare @TableDay VARCHAR(100) = 'dbLog_'+@date--表名+当前日期进行拼接
	Declare @PK VARCHAR(50) = 'PK_dbLog_'+@date--表中的主键
 	declare @sql_create varchar(8000)	

    set @sql_create='
CREATE TABLE [dbo].[' + @TableDay + '](
	[LogId] [bigint] IDENTITY(1,1) NOT NULL,
	[PartnerId] [varchar](50) NULL,
	[OrderId] [bigint] NULL,
	[PartnerOrderStatus] [nvarchar](50) NULL,
	[RequestUrl] [nvarchar](200) NULL,
	[RequestData] [nvarchar](max) NULL,
	[ResponseData] [nvarchar](max) NULL,
	[CreateAt] [datetime] NULL,
	[busType] [nvarchar](20) NULL,
 CONSTRAINT [' + @PK + '] PRIMARY KEY CLUSTERED 
(
	[LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
'
 exec (@sql_create)-- 创建表
 --向表里插入数据
 
 	Declare @sql_inset varchar(8000)	
    SET @sql_inset='	
	Declare @date date
    SET @date = cast(getdate() as date)  --获取当前日期
    SET IDENTITY_INSERT dbo.[' + @TableDay + '] ON
	insert  into dbo.[' + @TableDay + ']
        (  [LogId]
      ,[PartnerId]
      ,[OrderId]
      ,[PartnerOrderStatus]
      ,[RequestUrl]
      ,[RequestData]
      ,[ResponseData]
      ,[CreateAt]
      ,[busType]
        )
select  [LogId]
      ,[PartnerId]
      ,[OrderId]
      ,[PartnerOrderStatus]
      ,[RequestUrl]
      ,[RequestData]
      ,[ResponseData]
      ,[CreateAt]
      ,[busType]
from    dba.dbo.RequestLog with ( nolock )
where   CreateAt <= dateadd(month, -3, @date)
SET IDENTITY_INSERT dbo.[' + @TableDay + '] OFF
'
exec (@sql_inset)

	--删除三个月以前的旧日志数据
	Declare @sql_delete varchar(8000)	
    SET @sql_delete='
	Declare @date date
    SET @date = cast(getdate() as date)
	delete dba.dbo.RequestLog where CreateAt <= dateadd(month, -3, @date) and CreateAt <= @date
'
exec (@sql_delete)
 END

存储过程建好了，可以创建作业定期自动执行，这里就不做演示了！