Quick Server Assessment Script

Here's one for the SQL geeks. I have about 200 - 250 SQL servers I manage now, and I have to have a way to get familiar with them, super-quick. Here's how I'm doing it:

/* 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
 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

Run in ISQLW, this gives me a nice little report for each server. If I need to take any actions on it, I can. If not, chalk it up and move on.

Try it out. Let me know what you think.