有一个容易的方法去窥视维护日志。这也是好的方法去找出维护到底实际在干什么 ,当维护任务在运行的时候
YourSQLDba 提供一个存储过程叫做 ShowHistory
不带任何参数地执行这个存储过程,会显示从第一个作业到最后一个作业的执行情况。如果一个作业已经执行,
ShowHistory 会显示实际作业号码,列表会按照作业号的顺序从第一个作业一直按顺序排列到最后一个作业
要注意的是,这时候有两个作业像日志备份和完整维护会同时正在运行,所以他们两个都会在列表的最上面,
所以列表的顶部有可能会有两个同时活动的作业
存储过程给出的结果包含两个结果,作业头和作业详细信息。作业头显示作业号,作业名称,选项和作业执行时间
还包括了其他一些参数。作业详细信息显示了每个指令的执行状况,从最开始的指令到最近的指令。作业详细信息显示了一个等于号去标注作业号,序列号,命令开始,持续时间,和包含了额外信息和sql语句还有备份的输出
ShowHistory 存储过程的用法例子
@jobNo 作业号,默认显示最新的作业记录,如果指定了作业号,则只会显示到你指定的那个作业
@filterErr = 1 只显示错误信息,默认所有内容都显示
@dispLimit = 20 限制列出作业的范围,只列出20个作业,默认,最大数值只能输入40,而当你无论输入多大数值也只会输出40个作业
History maintenance reporting
Updated : 2009-02-25
Having a easy way to peek into maintenance logs is essential to diagnosis and maintenance follow-up. Also it is a good mean to find out what is actually going on in the maintenance, while it is running.
YourSQLDba provide a stored procedure call ShowHistory. It is very easy to use.
Executed with no parameters it shows jobs from the most recent (or actually executing) to the the oldest. If a job already executes, ShowHistory shows the actual Job number may be provided, in that case the list start with this job first and then continue in order from the most recent to the oldest. Be careful that 2 jobs like log backups and full maintenance may be running at the same time, so both of them are at the top of the list, so being at the top of the list doesn't necessarily being the only one active.
This procedure produce multiple results sets alternating in Job header row, followed by job details rows. Job header row show job number, job name, options and total duration (job start, job end) and Inclusion and exclusion parameters for database if any. Job details show every instruction executed from the latest to the earliest. Job detail rows show an equal sign to signal the first one, a job number and sequence number, the command start time, its duration in seconds, and a field that contains informational messages, sql query and for backup its output, or error messages if any.
Example of ShowHistory use:
@jobNo is the job number. By default it starts with the latest job recorded followed by the older in order of age.
@filterErr = 1 displays only errors, by default all content is listed.
@dispLimit = 20 limits the number of job listed to 20. By default, 40 is the maximum number of job listed, and this limit can be exceed whatever the parameter value is.
EXEC YourSQLDba.Maint.ShowHistory
@JobNo = 31
, @FilterErr = 1
, @DispLimit = 20
1 EXEC YourSQLDba.Maint.ShowHistory
2 @JobNo = 31
3 , @FilterErr = 1
4 , @DispLimit = 20