微软著名数据库软件sql server提供强大详细的日志功能，但大量的日志占用大量空间，对于很多用户来讲会变成一个美丽的负担。那么怎样清理各类sql server数据库的系统日志呢？这里，大家要把数据库系统运行日志和用户数据库日志文件区分开来。用户数据库日志又叫事务日志，一般是ldf后缀的日志文件。这篇文章将从日志记录分类、如何查看日志记录、日志记录的位置、日志记录的设置、为什么错误日志会暴增、如何清除日志记录等方面来为大家讲解。
快速答案：如果你需要一键清空MSSQL系统运行日志，那么只需打开这个目录，C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log，然后全选删除所有文件即可，不会对系统运行产生任何影响。
如果你希望采购云数据库，免自己维护，那么可以考虑阿里云RDS云数据库，速度快，不宕机，数据实时备份，通过万维景盛采购价格还可以折上折。欢迎垂询：QQ:442956988 微信：timesisp 手机：18610695105
日志记录分类
按日志文件查看器的分类，一般人们习惯将错误日志归为SQL SERVER、 SQL SERVER 代理，、Windows应用程序日志、数据库邮件等四类错误日志记录。如果还算上维护计划、远程维护计划、作业历史记录日志的话，总共是七类日志信息文件。
其中Windows应用程序日志类型又分为系统日志(System Logs)、安全日志(Security logs)、应用程序日志(Application logs), 补丁链接日志（PatchLink Logs）等几种，我在服务器（Windows Server 2008 R2 Standard）上打开SSMS，居然发现又多了HardwareEvents， Internet Explorer、Windows PowerShell等日志文件。以上都属于windows系统日志。
日志记录位置路径
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
SELECT log_id ,
CASE event_type
WHEN 0 THEN 'success'
WHEN 1 THEN 'information'
WHEN 2 THEN 'warning'
ELSE 'error'
END AS event_type ,
log_date ,
description ,
process_id ,
sl.mailitem_id ,
account_id ,
sl.last_mod_date ,
sl.last_mod_user
FROM[dbo].[sysmail_log] sl
WHERE ( ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 )
OR( EXISTS( SELECT mailitem_id
FROM [dbo].[sysmail_allitems] ai
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 活动相关的日志
在对象资源管理器中，依次展开“管理”和“SQL Server 日志”，再双击“当前<日期/时间>”，将显示 SQL Server、“SQL 代理”和“Windows 事件”日志。
查看与作业相关的日志
在对象资源管理器中，展开“SQL Server 代理”，右键单击“作业”，再单击“查看历史记录”，此时将显示“作业历史记录”和“SQL 代理”日志。
查看与维护计划相关的日志
在对象资源管理器中，展开“管理”，右键单击“维护计划”，再单击“查看历史记录”，此时将显示“维护计划”、“作业历史记录”和“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”的历史记录
USE msdb ; GO EXEC dbo.sp_help_jobhistory @job_name = N'ServerDiskCapacityCheck' ; GO »ò SELECT j.name AS [JOB_NAME] , h.step_id AS [Step] , h.step_name AS [STEP_NAM] , h.MESSAGE AS [Message] , [Status] = CASE WHEN h.run_status = 0 THEN 'Failed' WHEN h.run_status = 1 THEN 'Succeeded' WHEN h.run_status = 2 THEN 'Retry' WHEN h.run_status = 3 THEN 'Canceled' END, h.run_date AS [RunDate] , h.run_time AS [RunTime] , h.run_duration AS [RunDuration] FROM sysjobs j INNER JOIN sysjobhistory h ON h.job_id = j.job_id WHERE h.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND h.run_status<>1 /* WHERE j.name = 'Job_Name' */ 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代理才写入到新的日志文件。
设置作业历史记录日志
在对象资源管理器中，连接到 SQL Server 数据库引擎 实例，再展开该实例。
右键单击“SQL Server 代理”，再单击“属性”。
在“SQL Server 代理属性”对话框中，选择“历史记录”页。
从下列选项中选择：
选中“限制作业历史记录日志的大小”，然后键入作业历史记录日志的最大行数和每个作业的最大行数。
选中“自动删除代理历史记录”，然后指定时间段。这样，早于此时间段的历史记录将从日志中清除。
为什么sql server数据库错误日志文件会短时间内容量暴增？
这里我说的不仅仅指某个错误日志记录文件暴增，也指目录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的方法吧
USE [YourSQLDba] GO /****** Object: StoredProcedure [yMaint].[LogCleanup] Script Date: 05/28/2013 18:36:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ----------------------------------------------------------------------------- -- yMaint.LogCleanup (for entries older than 30 days) -- Mail logs -- Backup history logs -- Job history -- Cycle SQL Server error log ----------------------------------------------------------------------------- create proc [yMaint].[LogCleanup] @jobNo Int as Begin declare @d nvarchar(8) declare @lockResult int declare @sql nvarchar(max) Begin try exec yMaint.LockMaintDb@jobNo=@jobNo, @lockType='C', @DbName = 'LogCleanUpStep', @Result = @lockResult output If @lockResult > 0 Return; Set @sql = 'Exec msdb.dbo.sysmail_delete_log_sp @logged_before = "<d>";' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Cleanup log entries older than 30 days, begins with mail' , @sql = @sql , @JobNo = @JobNo Set @sql = 'EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = "<d>";' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Cleanup log entries older than 30 days, for mailitems' , @sql = @sql , @JobNo = @JobNo -- clean backup history Set @sql = 'exec Msdb.dbo.sp_delete_backuphistory @oldest_date = "<d>" ' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Cleanup log entries older than 30 days, for backup history' , @sql = @sql , @JobNo = @JobNo -- clean sql agent job history Set @sql = 'EXECUTE Msdb.dbo.sp_purge_jobhistory @oldest_date = "<d>"' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Cleanup log entries older than 30 days, for job history' , @sql = @sql , @JobNo = @JobNo -- clean job maintenance job history (SQL Server own maintenance) Set @sql = 'EXECUTE Msdb.dbo.sp_maintplan_delete_log null,null,"<d>"' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Cleanup log entries older than 30 days, for SQL Server job maintenace plans' , @sql = @sql , @JobNo = @JobNo -- archive current log, and start a new one Set @sql = 'Execute sp_cycle_errorlog' Set @sql = replace(@sql, '<d>', convert(nvarchar(8), dateadd(dd, -30, getdate()), 112)) Set @sql = replace(@sql, '"', '''') Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @info = 'Recycle Sql Server error log, start a new one' , @sql = @sql , @JobNo = @JobNo Delete H From ( Select distinct JobNo -- From Maint.JobHistory Where JobStart < dateadd(dd, -30, getdate()) ) as T join Maint.JobHistory H On H.JobNo = T.JobNo End try Begin catch exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep' Exec yExecNLog.LogAndOrExec @context = 'yMaint.LogCleanup' , @Info = 'Error caught in proc' , @err = '?' , @JobNo = @JobNo End Catch exec yMaint.UnLockMaintDb@jobNo=@jobNo, @DbName = 'LogCleanUpStep' End -- yMaint.LogCleanup 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.htmlx?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
442956988