/* Initial server status and configuration check script
	D Maxwell, June 2007
*/

-- Get the product version and revision level.
-- If the Edition is "Desktop Engine", upgrade.
SELECT SERVERPROPERTY('ServerName') Server_Name,
		 SERVERPROPERTY('productversion') Revision, 
		 SERVERPROPERTY('productlevel') Service_Pack, 
		 SERVERPROPERTY('edition') Edition
go

-- Make sure the correct DBs are there.
select name Databases
	from master..sysdatabases
	where name not in('master', 'model', 'msdb', 'tempdb')
go

-- See what jobs are scheduled.
select name JobName
	from msdb..sysjobs
	where name like '%maintenance plan%'  --These need to GO...
	and enabled = 1  --The jobs actually run... right?
	order by JobName
go


-- Check for job failures, getting frequency and most recent failure.  
select count(*) Fail_Count, j.name Job_Name, max(jh.run_date) Run_Date, max(jh.run_time ) Run_Time
	from msdb..sysjobhistory jh inner join msdb..sysjobs j 
		on jh.job_id = j.job_id
	where jh.message like '%the job failed%'
	group by j.name
	order by j.name
go

-- Get the length of the job history.
select min(run_date) History_Starts, max(run_date) Current_History from msdb..sysjobhistory
go

-- Before we get the config, make sure we get the WHOLE config.
sp_configure 'show advanced options', 1
reconfigure
go

-- Get the configuration.
sp_configure