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
存储过程建好了,可以创建作业定期自动执行,这里就不做演示了!