Job定时自动执行SQL日志记录脚本

数据库类型：

SQL Server

用途：

用于自动记录SQL当天运行的SQL语句及相关事务，对于DB及业务系统维护人员来说还是很有用的

可解决相关问题：

1、当业务数据表中一条重要数据误删之后，要找回该条数据的插入记录用于恢复数据

2、查询数据表中数据被谁修改、删除

3、用于数据库遇到查询耗时、事务堵塞、CPU内存消耗等问题分析

 相关函数的解释：

   sp_trace_setevent (Transact-SQL) - SQL Server | Microsoft Learn

   sp_trace_setstatus (Transact-SQL) - SQL Server | Microsoft Learn

以下是存储过程sql内容：

  注：存储过程创建完成之后，配置JOB定时执行就行，当天的记录会归在同一个日志文件中

  产生的日志文件示例：duration202408260000.trc，用SQL Server Profiler工具打开

--exec dbo.TraceEis          
          
CREATE proc TraceEis              
/*目的：自动开启追踪日志*/              
as              
begin              
 declare @rc int              
 declare @TraceID int              
 declare @maxfilesize bigint              
 declare @DateTime datetime              
              
 set @DateTime = DATEADD(MI,-1,dateadd(day,1,getdate()))--定义追踪文件结束的记录日期            
 set @maxfilesize = 4096  --定义跟踪文件的大小,单位是MB        
               
 declare @path nvarchar(50)              
 set @path=N'D:\SQLLog\duration'+'20'+convert(varchar(10),getdate(),12)+replace(convert(varchar(5),getdate(),108),':','')          
              
 exec @rc = sp_trace_create @TraceID output, 0, @path, @maxfilesize, @DateTime               
 if (@rc != 0) goto error              
          
/*          
0 :没有错误。          
1 :未知错误。          
10:无效选项。 指定的选项不兼容时返回此代码。          
12:文件未创建。          
13:内存不足。 在没有足够内存执行指定的操作时返回此代码。          
14:无效停止时间。 在指定的停止时间已发生时返回此代码。          
15:参数无效。 在用户已提供不兼容的参数时返回此代码。          
*/          
              
 declare @on bit              
 set @on = 1              
           
 --在完成了远程过程调用 (RPC) 时发生          
 exec sp_trace_setevent @TraceID, 10, 1, @on    --TextData          
 exec sp_trace_setevent @TraceID, 10, 8, @on    --HostName          
 exec sp_trace_setevent @TraceID, 10, 10, @on   --ApplicationName          
 exec sp_trace_setevent @TraceID, 10, 11, @on   --LoginName          
 exec sp_trace_setevent @TraceID, 10, 12, @on   --SPID           
 exec sp_trace_setevent @TraceID, 10, 13, @on   --Duration           
 exec sp_trace_setevent @TraceID, 10, 14, @on   --StartTime           
 exec sp_trace_setevent @TraceID, 10, 16, @on --read          
 exec sp_trace_setevent @TraceID, 10, 17, @on --write          
 exec sp_trace_setevent @TraceID, 10, 18, @on --CPU          
          
 --在完成了 Transact-SQL 批处理时发生           
 exec sp_trace_setevent @TraceID, 12, 1, @on              
 exec sp_trace_setevent @TraceID, 12, 8, @on             
 exec sp_trace_setevent @TraceID, 12, 10, @on          
 exec sp_trace_setevent @TraceID, 12, 14, @on              
 exec sp_trace_setevent @TraceID, 12, 11, @on            
 exec sp_trace_setevent @TraceID, 12, 12, @on              
 exec sp_trace_setevent @TraceID, 12, 13, @on              
 exec sp_trace_setevent @TraceID, 12, 16, @on          
 exec sp_trace_setevent @TraceID, 12, 17, @on          
 exec sp_trace_setevent @TraceID, 12, 18, @on           
              
 -- Set the Filters              
 declare @intfilter int              
 declare @bigintfilter bigint              
              
 -- Set the trace status to start              
 exec sp_trace_setstatus @TraceID, 1              
              
 -- display trace id for future references              
 select TraceID=@TraceID              
 goto finish              
              
 error:               
 select ErrorCode=@rc              
              
 finish:               
               
end