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
SQL SERVER 2008	Program Files\Microsoft SQL Server\MSSQL10.实例名\MSSQL\LOG
SQL SERVER 2008 R2	Program Files\Microsoft SQL Server\MSSQL10_50.实例名\MSSQL\LOG

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

 

 

对于邮件日志记录，存储过程sysmail_delete_log_sp提供从数据库邮件日志中删除事件。删除日志中的所有事件或删除符合某一日期或类型条件的那些事件

sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ]

[, [ @event_type = ] 'event_type' ]

 

 删除参数指定的所有 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

参考资料：

http://msdn.microsoft.com/zh-cn/library/ms187885(v=sql.105).aspx

http://www.jb51.net/article/26988.htm

http://groundsel.itpub.net/post/1284/494264

http://www.canway.net/Lists/CanwayOriginalArticels/DispForm.aspx?ID=291

http://technet.microsoft.com/zh-cn/library/ms191202(v=SQL.105).aspx

http://support.microsoft.com/kb/157804/zh-cn

http://support.microsoft.com/kb/115519/zh-cn

http://blog.csdn.net/smithliu328/article/details/7843724

http://blog.csdn.net/claro/article/details/5660524

http://www.cnblogs.com/lyhabc/archive/2013/02/12/2910623.html