hx_dbMaintancePlans

,

Displays Maintance plan history if any. I used distinct to filter out and return only what is relevent to what I need. You may not wish this for your purposes.

Feel free to make changes

drop PROCEDURE hx_dbMaintancePlans
go

/* 	Robert Vallee 08/01/2001
	rvallee@hybridx.com
	input:	None
	output:	Table format
	Desc:	Displays Maintance plan history if any. I used distinct to filter out and return 
		only what is relevent to what I need. You may not wish this for your purposes. 
		Feel free to make changes. See Warning.
	Warnings: Be patient. Depending on the number of rows in the sysdbmaintplan_history table this could take time.
*/

CREATE PROCEDURE hx_dbMaintancePlans AS

set nocount on
SELECT DISTINCT 
  substring(msdb..sysdbmaintplan_history.plan_name,1,40) AS 'Plan name', 
  substring(msdb..sysdbmaintplan_databases.database_name,1,50) as 'Database name',
  substring(msdb..sysdbmaintplans.owner,1,15) as Owner, 
  msdb..sysdbmaintplans.date_created, 
  substring(msdb..sysdbmaintplan_history.server_name,1,25) as 'Server name', 
  substring(msdb..sysdbmaintplan_history.activity,1,35) as Activity, 
	'Succeeded'=case
	WHEN msdb..sysdbmaintplan_history.succeeded = 0 THEN 'No'
	WHEN msdb..sysdbmaintplan_history.succeeded = 1 THEN 'Yes'
	end,
  msdb..sysdbmaintplan_history.start_time, 
  msdb..sysdbmaintplan_history.end_time, 
  msdb..sysdbmaintplan_history.message, 
  msdb..sysdbmaintplan_history.error_number
FROM msdb..sysdbmaintplan_history INNER JOIN
  msdb..sysdbmaintplan_databases ON 
  msdb..sysdbmaintplan_history.plan_id = msdb..sysdbmaintplan_databases.plan_id
  INNER JOIN
  msdb..sysdbmaintplans ON 
  msdb..sysdbmaintplan_history.plan_id = msdb..sysdbmaintplans.plan_id

Rate

Share

Share

Rate