Yoursqldba translation (10)

Yoursqldba translation (10)

There is an easy way to peek into the maintenance log. This is also a good way to find out what the maintenance is actually doing. When the maintenance task is running
Yoursqldba provides a stored procedure named showhistory
If the stored procedure is executed without any parameters, the execution from the first job to the last job is displayed. If a job has been executed,
Showhistory displays the actual job number. The list is sorted from the first job to the last job in order of the job number.
Note that two jobs, such as log backup and complete maintenance, are running at the same time, so both of them are at the top of the list,
Therefore, there may be two jobs active simultaneously at the top of the list.

The Stored Procedure contains two results: Job header and job details. The job header displays the job number, job name, options, and job execution time.
Other parameters are included. The job details show the execution status of each instruction, from the initial instruction to the latest instruction. The job details display an equal sign to mark the job number, serial number, command start, duration, and contains additional information, SQL statements, and backup output.

Example of how to use the showhistory Stored Procedure

@ Jobno job number. By default, the latest job record is displayed. If a job number is specified, only the specified job is displayed.
@ Filtererr = 1: only the error message is displayed. By default, all content is displayed.
@ Displimit = 20 restrict the list of jobs. Only 20 jobs are listed. By default, the maximum value is 40. when you enter a maximum of 40 jobs, only 40 jobs are output.

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 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 isolation sion 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 ExecYoursqldba. Maint. showhistory2@ Jobno = 313,@ Filtererr = 14,@ Displimit = 20