MS SQL 日志记录管理

    MS
SQL的日志信息/日志记录，可能对你来说，既熟悉又陌生，熟悉是因为你可能一直都在使用，查看、关注一些日志信息/记录，例如，作业历史记录；陌生是因
为你可能从不关注日志信息/记录的管理，这里我一直用日志信息/记录这个词，而没有用日志文件这个词来阐述，是想让大家把它和事务日志文件(ldf)区分
开来，网上你用日志文件做搜索关键词，可能搜出来的都是事务日志相关的信息。其实它真的也叫日志文件，这篇文章我大概从日志记录分类、如何查看日志记录、
日志记录的位置、日志记录的设置、为什么错误日志会暴增、如何清除日志记录等方面来讲述。

日志记录分类

按日志文件查看器，习惯将错误日志归为SQL SERVER、 SQL SERVER 代理， Windows应用程序日志，数据库邮件等四类错误日志记录。如果还考虑维护计划、远程维护计划、作业历史记录日志信息，总共是7类日志信息文件。

其中Windows应用程序日志类型又分为
系统日志(System)、安全日志(Security)、应用程序日志(Application),
PatchLink日志等几种，我在服务器（Windows Server  2008 R2
Standard）上打开SSMS，居然发现又多了HardwareEvents， Internet Explorer、Windows
PowerShell等日志文件。这些都是系统的日志文件。你不必太纠结有多少种。

日志记录位置

SQL SERVER日志记录、 SQL
SERVER代理记录的位置如下所示， SQL SERVER日志记录一般存储在ERRORLOG.n(n为数字)文件里， SQL
SERVER代理日志记录位于SQLAGENT.n这类的文件里。当然这跟数据库的版本也有关系：

    SQL SERVER 2005，默认情况下，错误日志位于 Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG 和 ERRORLOG.n 文件中。其中MSSQL.n的区分为：

        MSSQL.1：SQLSERVER

        MSSQL.2：SSAS

        MSSQL.3：SQLExpress

        MSSQL.4：SSRS

所以，一般情况下，你只需要关注MSSSQL.1目录下的日志文件

那么，数据库邮件日志记录位于哪里呢？作业历史记录日志信息、Windows应用程序日志又位于哪里呢？是不是从没考虑过这些？

数据库邮件日志记录信息可以从视图msdb.dbo.sysmail_event_log查询得到，实质保存在[dbo].[sysmail_log]表里面。

sysmail_event_log

1.  SELECT log_id ,
2.         CASE event_type
3.           WHEN 0 THEN 'success'
4.           WHEN 1 THEN 'information'
5.           WHEN 2 THEN 'warning'
6.           ELSE 'error'
7.         END AS event_type ,
8.         log_date ,
9.         description ,
10.         process_id ,
11.         sl.mailitem_id ,
12.         account_id ,
13.         sl.last_mod_date ,
14.         sl.last_mod_user
15.     FROM[dbo].[sysmail_log] sl
16. WHERE   ( ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 )
17.         OR( EXISTS( SELECT    mailitem_id
18.                       FROM      [dbo].[sysmail_allitems] ai
19.                       WHERE     sl.mailitem_id = ai.mailitem_id ) )

作业历史记录日志信息都保存在msdb.dbo.sysjobhistory的表里面，其中run_status字段代表作业执行状态

0 = 失败

1 = 成功

2 = 重试

3 = 已取消

4= 正在进行

所有Windows应用程序日志其实都位于
同一位置%SystemRoot%\System32\Winevt\Log。像Application日志文件位于%SystemRoot%
\System32\Winevt\Logs\Application.evtx，如下所示，

查看日志记录

查看日志记录可以确保进程（例如，备份和还原操作、批处理命令或其他脚本和进程）成功完成。此功能可用于帮助检测任何当前或潜在的问题领域，包括自动恢复消息（尤其是在 SQL Server 实例已停止并重新启动时）、内核消息或其他服务器级错误消息。

方式1： 查看错误日志文件

对SQL SERVER、SQL
SERVER
AGENT日志记录信息，你可以直接去log目录下找到ERRORLOG、SQLAGENT日志文件，直接打开查看；而像Windows应用程序日志记
录，去到%SystemRoot%\System32\Winevt\Log目录，找到对应的日志文件，直接打开查看。

方式2：通过SSMS来查看日志记录

查看与常规 SQL Server 活动相关的日志

1. 在对象资源管理器中，依次展开“管理”和“SQL Server 日志”，再双击“当前<日期/时间>”，将显示 SQL Server、“SQL 代理”和“Windows 事件”日志。

查看与作业相关的日志

1. 在对象资源管理器中，展开“SQL Server 代理”，右键单击“作业”，再单击“查看历史记录”，此时将显示“作业历史记录”和“SQL 代理”日志。

查看与维护计划相关的日志

1. 在对象资源管理器中，展开“管理”，右键单击“维护计划”，再单击“查看历史记录”，此时将显示“维护计划”、“作业历史记录”和“SQL 代理”日志。

方式3：用脚本查看

3.1 对于SQL SERVER日志文件，可以通过下面脚本查看：

   --查看日志文件的存档号

    EXEC master.dbo.sp_enumerrorlogs

   用这个命令可以查看日志文件的大小，这个非常有用，你可以把大小异常的文件给排查出来。

   --根据存档号查看该档日志内容

   EXEC master.dbo.xp_readerrorlog 1

   --根据job_id查看SQL SERVER日志记录

  SELECT * FROM  msdb.dbo.sysjobhistory WHERE job_id='36E9232B-CD5B-4646-9BED-B8242090FFF9'

3.2 对于作业历史记录日志信息，你既可以通过下面存储过程查看，也可以直接查询对应的表。

例如，我要查看作业“ServerDiskCapacityCheck”的历史记录

Code Snippet

1.  
2.  
3. USE msdb ;
4.  
5. GO
6.  
7. EXEC dbo.sp_help_jobhistory
8.  
9. @job_name = N'ServerDiskCapacityCheck' ;
10.  
11. GO
12.  
13. »ò
14.  
15.   SELECT      j.name AS  [JOB_NAME] ,
16.  
17.             h.step_id AS  [Step] ,  
18.  
19.             h.step_name AS  [STEP_NAM] ,  
20.  
21.             h.MESSAGE AS  [Message] ,  
22.  
23.             [Status]    = CASE WHEN h.run_status = 0 THEN 'Failed'  
24.  
25.             WHEN h.run_status = 1 THEN 'Succeeded'  
26.  
27.             WHEN h.run_status = 2 THEN 'Retry'  
28.  
29.             WHEN h.run_status = 3 THEN 'Canceled'  
30.  
31.             END,  
32.  
33.             h.run_date AS  [RunDate] ,  
34.  
35.             h.run_time AS  [RunTime] ,  
36.  
37.             h.run_duration  AS  [RunDuration]
38.  
39.   FROM        sysjobs j
40.  
41.     INNER JOIN  sysjobhistory h ON h.job_id = j.job_id
42.  
43. WHERE h.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND h.run_status<>1   
44.  
45.   /* WHERE    j.name = 'Job_Name' */
46.  
47.     ORDER BYh.run_date, h.run_time

3.3数据库邮件记录查看

SELECT * FROM  msdb.dbo.sysmail_event_log;

日志记录管理

设置最大错误日志文件数

    1：在对象资源管理器中，连接到 SQL Server 数据库引擎实例，再展开该实例。

    2：在”管理“选项，选择”SQL
SERVER日志”，单击右键选择配置。 顺便说一下,很多网上资料说，SQL SERVER默认保留前6个日志文件，但是我查看了SQL
SERVER 2005和SQL SERVER 2008，都是默认保留30个，有时候需要自己去验证、实验，不要人人亦云。估计这个说法是SQL
SERVER 2000时的配置了。

当然，你也可以用命令设置:

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

GO

设置错误日志文件存放目录

1：在对象资源管理器中，连接到 SQL Server 数据库引擎实例，再展开该实例。

2：展开“SQL SERVER”代理。

3：右键单击”错误日志“，然后选择配置选项，如下所示：

4：在”错误日志文件“选项中，输入新的路径和文件名，或使用浏览(...)按钮进行查找。重新启动SQL SERVER代理服务后，SQL SERVER代理才写入到新的日志文件。

设置作业历史记录日志

1. 在对象资源管理器中，连接到 SQL Server 数据库引擎 实例，再展开该实例。
2. 右键单击“SQL Server 代理”，再单击“属性”。
3. 在“SQL Server 代理属性”对话框中，选择“历史记录”页。
4. 从下列选项中选择：

• 选中“限制作业历史记录日志的大小”，然后键入作业历史记录日志的最大行数和每个作业的最大行数。
• 选中“自动删除代理历史记录”，然后指定时间段。这样，早于此时间段的历史记录将从日志中清除。
• 

为什么错误日志文件暴增？

这里我说的不仅仅指某个错误日志记录文件暴增，也指目录Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG所占空间暴增，如果你平时都不关注这些错误日志，也从不维护错误日志记录文件，那么很有可能它所占的空间非常大，大到让你吃惊的地步。几十G的我也见过，那么具体原因可能有以下种（如果大家还碰到过其它情况，欢迎补充）：

1：SQL 内部错误的时候会产生非常多的DUMP文件，如下所示

2：高可用的数据库服务器可能很少停机，而你又没有定期清理、清空这些错误日志信息，那么ERRORLOG.n/SQLAGENT.n文件增长会非常大。这样对于DBA使用错误日志查找信息就会比较困难，而且日志大了加载、写入以后性能也会受到影响。

3：其实还有一个情况，如果你数据库IP地址曝露在外网时，会遭到大量尝试登录sa的攻击，也会产生大量的审核失败日志信息。

4：就是一些SQL SERVER PROFILE文件没有删除，当然，这个本质跟日志文件暴增没啥关系，但是跟LOG文件夹的大小有些关系。

如何清理错误日志：

对于SQL SERVER日志、SQL
SERVER AGENT日志记录，微软提供了一个存储过程sp_cycle_errorlog可以实现日志的循环。
这个存储过程的作用是关闭当前的错误日志文件，并循环错误日志扩展编号（就像重新启动服务器）。每次启动SQL Server
时，都会将当前错误日志重命名为errorlog.1；errorlog.1 变为errorlog.2，errorlog.2 变为errorlog.3，依次类推。最后一个errorlog.n将会被删除。sp_cycle_errorlog 可使您循环访问错误日志文件，而不必停止和启动服务器。

另外：日志过大说明你没有截断错误日志，错误日志是可以截断的，进入你的数据库输入DBCC ERRORLOG

每执行一次，当前的错误日志退出，让后建立
新的错误日志，你只能删除 ERRORLOGn的错误日志没有号码的是正在使用的日志，删除会报错，如果它比较大，就DBCC
ERRORLOG，而后他会变成ERRORLOG+编号，你就可以删除了，另外建议你把这些ERRORLOG 放到其他盘符，比较好管理。

对于 Windows应用程序日志，一般都有默认的大小设置，以及按需要覆盖。这些配置一般也是最优的配置。所以这块除非你有特殊需求，否则不用你操心。

 删除参数指定的所有 SQL Server 代理作业步骤日志。使用此存储过程可维护 msdb 数据库中的 sysjobstepslogs 表。

sp_delete_jobsteplog { [ @job_id = ] 'job_id' | [ @job_name = ] 'job_name' }

[ , [ @step_id = ] step_id | [ @step_name = ] 'step_name' ]

[ , [ @older_than = ] 'date' ]

[ , [ @larger_than = ] 'size_in_bytes' ]

删除作业的历史记录

删除特定作业YourSQLDba_LogBackups的历史记录。

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory

@job_name = N'YourSQLDba_LogBackups' ;

GO

以下示例将不带参数执行此过程以删除所有的历史记录。

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory ;

GO

删除参数指定的所有 SQL Server 代理作业步骤日志。使用此存储过程可维护 msdb 数据库中的 sysjobstepslogs 表。如果你想好好维护日志记录，那么你可以整合上面的思想方法到一个存储过程，然后配置一个作业来定期清理日志记录，接下来我们看看YourSQLDba的方法吧

Code Snippet

1. USE [YourSQLDba]
2. GO
3.  
4. /****** Object:  StoredProcedure [yMaint].[LogCleanup]    Script Date: 05/28/2013 18:36:21 ******/
5. SET ANSI_NULLS ON
6. GO
7.  
8. SET QUOTED_IDENTIFIER ON
9. GO
10.  
11. -----------------------------------------------------------------------------
12. -- yMaint.LogCleanup (for entries older than 30 days)
13. -- Mail logs
14. -- Backup history logs
15. -- Job history
16. -- Cycle SQL Server error log
17. -----------------------------------------------------------------------------
18. create proc [yMaint].[LogCleanup]
19.   @jobNo Int
20. as
21. Begin
22.   declare @d nvarchar(8)
23.   declare @lockResult int
24.   declare @sql nvarchar(max)
25.  
26.   Begin try
27.  
28.   exec yMaint.LockMaintDb@jobNo=@jobNo, @lockType='C', @DbName = 'LogCleanUpStep', @Result = @lockResult output
29.   If @lockResult > 0
30.     Return;
31.  
32.   Set @sql = 'Exec msdb.dbo.sysmail_delete_log_sp @logged_before = "<d>";'
33.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
34.   Set @sql = replace(@sql, '"', '''')
35.   Exec yExecNLog.LogAndOrExec
36.     @context = 'yMaint.LogCleanup'
37.   , @info = 'Cleanup log entries older than 30 days, begins with mail'
38.   , @sql = @sql
39.   , @JobNo = @JobNo
40.   
41.   Set @sql = 'EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = "<d>";'
42.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
43.   Set @sql = replace(@sql, '"', '''')
44.   Exec yExecNLog.LogAndOrExec
45.     @context = 'yMaint.LogCleanup'
46.   , @info = 'Cleanup log entries older than 30 days, for mailitems'
47.   , @sql = @sql
48.   , @JobNo = @JobNo
49.  
50.   -- clean backup history
51.   Set @sql = 'exec  Msdb.dbo.sp_delete_backuphistory   @oldest_date = "<d>" '
52.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
53.   Set @sql = replace(@sql, '"', '''')
54.   Exec yExecNLog.LogAndOrExec
55.     @context = 'yMaint.LogCleanup'
56.   , @info = 'Cleanup log entries older than 30 days, for backup history'
57.   , @sql = @sql
58.   , @JobNo = @JobNo
59.   
60.   -- clean sql agent job history
61.   Set @sql = 'EXECUTE  Msdb.dbo.sp_purge_jobhistory  @oldest_date = "<d>"'
62.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
63.   Set @sql = replace(@sql, '"', '''')
64.   Exec yExecNLog.LogAndOrExec
65.     @context = 'yMaint.LogCleanup'
66.   , @info = 'Cleanup log entries older than 30 days, for job history'
67.   , @sql = @sql
68.   , @JobNo = @JobNo
69.   
70.   -- clean job maintenance job history (SQL Server own maintenance)
71.   Set @sql = 'EXECUTE  Msdb.dbo.sp_maintplan_delete_log null,null,"<d>"'
72.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
73.   Set @sql = replace(@sql, '"', '''')
74.   Exec yExecNLog.LogAndOrExec
75.     @context = 'yMaint.LogCleanup'
76.   , @info = 'Cleanup log entries older than 30 days, for SQL Server job maintenace plans'
77.   , @sql = @sql
78.   , @JobNo = @JobNo
79.   
80.   -- archive current log, and start a new one
81.   Set @sql = 'Execute sp_cycle_errorlog'
82.   Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112))
83.   Set @sql = replace(@sql, '"', '''')
84.   Exec yExecNLog.LogAndOrExec
85.     @context = 'yMaint.LogCleanup'
86.   , @info = 'Recycle Sql Server error log, start a new one'
87.   , @sql = @sql
88.   , @JobNo = @JobNo
89.  
90.   Delete H
91.   From
92.     (
93.     Select distinct JobNo --
94.     From  Maint.JobHistory
95.     Where JobStart < dateadd(dd, -30, getdate())
96.     ) as T
97.     join
98.     Maint.JobHistory H  
99.     On H.JobNo = T.JobNo
100.  
101.   End try
102.   Begin catch
103.     exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep'
104.     Exec yExecNLog.LogAndOrExec
105.         @context = 'yMaint.LogCleanup'
106.       , @Info = 'Error caught in proc'  
107.       , @err = '?'
108.       , @JobNo = @JobNo
109.   End Catch
110.  
111.   exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep'
112.  
113. End -- yMaint.LogCleanup
114.  
115. GO