My main background has been Oracle databases and I have built my SQL Server knowledge over the years to be of assistance in situations where both Oracle and SQL Server databases are present.
My SQL Server Health Check script has been my attempt to distill my SQL Server knowledge into something that I can use to review a new SQL Server database that I have never worked with.
Sometimes we overlook the simplest things because we take for granted that they are already present and fine. This is not always the case, obviously. The latest iteration of my script has the addition of reporting tables in user databases that have not had statistics updated in the last 30 days and have significant changes since that time.
--sql_server_health_check_v3.1.sql --v3.1 --This is a non-intrusive script written to give an overall informational view of a --SQL Server 2005-2016(CTP3) server instance for the purpose of first-time review. -- --Version 3.1 additions --Tables that have not had a stats update in more than 30 days and have significant changes /* Information gathered -Node name -License information -Product Version, level, and Edition -Instance parameters -Start time and up time -Database listing -Database uptime -Database data and log size with total -Database file size and growth settings _Database file size free space -Is this a Cluster Node? -Nodes in Cluster -Is AlwaysOn enabled (2012 and above)? -AlwaysOn status -Memory usage per database -Memory usage of in-memory OLTP tables -Last backup time per database -No log backups for FULL or BULK_LOGGED recovery model databases in last 30 days -Databases with no backups at all in the last 30 says -Backups for the previous week per database -Jobs that failed in the last 24 hours -Missing indexes -Duplicate indexes -High index fragmentation check -Tables that have not had a stats update in more than 30 days and have significant changes -Wait stats -Users and roles -Job information -Existing linked server listing -User statistics identification */ use master GO ----------------------------------------------------------Node name SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]; ----------------------------------------------------------Product Version, level, and Edition SELECT SERVERPROPERTY('productversion') AS ProductVersion, SERVERPROPERTY ('productlevel') AS ProductLevel, SERVERPROPERTY ('edition') AS Edition; ----------------------------------------------------------License information print N'License information'; SELECT SERVERPROPERTY('LicenseType') AS LICENSE_TYPE, ISNULL(SERVERPROPERTY('NumLicenses'),0) AS NUM_LICENCES; ----------------------------------------------------------Instance parameters print N'Instance Parameter'; SELECT * FROM sys.configurations ORDER BY name ; ----------------------------------------------------------Database listing print N'Database list with Status and Recovery Model'; SELECT substring(name,1,40) AS name, substring(state_desc,1,20) AS STATE, substring(recovery_model_desc,1,20) AS RECOVERY_MODEL FROM sys.databases order by name; ----------------------------------------------------------Database startup time print N'Start time'; SELECT DATEADD(ms,-sample_ms,GETDATE() )AS StartTime FROM sys.dm_io_virtual_file_stats(1,1); ----------------------------------------------------------Database start time uptime print N'Up time'; DECLARE @server_start_time DATETIME, @seconds_diff INT, @years_online INT, @days_online INT, @hours_online INT, @minutes_online INT, @seconds_online INT ; SELECT @server_start_time = login_time FROM master.sys.sysprocesses WHERE spid = 1 ; SELECT @seconds_diff = DATEDIFF(SECOND, @server_start_time, GETDATE()), @years_online = @seconds_diff / 31536000, @seconds_diff = @seconds_diff % 31536000, @days_online = @seconds_diff / 86400, @seconds_diff = @seconds_diff % 86400, @hours_online = @seconds_diff / 3600, @seconds_diff = @seconds_diff % 3600, @minutes_online = @seconds_diff / 60, @seconds_online = @seconds_diff % 60 ; SELECT @server_start_time AS server_start_time, @years_online AS years_online, @days_online AS days_online, @hours_online AS hours_online, @minutes_online AS minutes_online, @seconds_online AS seconds_online ; SELECT substring(name,1,40) AS name, substring(state_desc,1,20) AS STATE, substring(recovery_model_desc,1,20) AS RECOVERY_MODEL FROM sys.databases order by name; ----------------------------------------------------------Database data and log size with total print N'Data and Log Size with Total'; with fs as ( select database_id, type, size * 8.0 / 1024 size from sys.master_files ) select name, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) + (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) AS Total_MB from sys.databases db; --Grand total of ALL data and log files as one value select SUM(size*8.0)/1024 AS Total_MB from sys.master_files; ----------------------------------------------------------Database file size and growth settings print N'Size and Growth'; select substring(b.name,1,40) AS DB_Name, substring(a.name,1,40) AS Logical_name, substring(a.filename,1,100) AS File_Name, cast((a.size * 8.00) / 1024 as numeric(12,2)) as DB_Size_in_MB, case when a.growth > 100 then 'In MB' else 'In Percentage' end File_Growth, cast(case when a.growth > 100 then (a.growth * 8.00) / 1024 else (((a.size * a.growth) / 100) * 8.00) / 1024 end as numeric(12,2)) File_Growth_Size_in_MB, case when ( maxsize = -1 or maxsize=268435456 ) then 'AutoGrowth Not Restricted' else 'AutoGrowth Restricted' end AutoGrowth_Status from sysaltfiles a join sysdatabases b on a.dbid = b.dbid where DATABASEPROPERTYEX(b.name, 'status') = 'ONLINE' order by b.name; ----------------------------------------------------------Database file size free space SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB, round(100-((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/(size/128.0))*100,2,1) AS PCT_FREE FROM sys.database_files; ----------------------------------------------------------Is this a Cluster Node? SELECT 'Clustered', case when SERVERPROPERTY('IsClustered') = 0 then 'No' else 'Yes' end; ----------------------------------------------------------Nodes in Cluster print N'Cluster Nodes'; SELECT * FROM fn_virtualservernodes(); ----------------------------------------------------------Is AlwaysOn enabled (2012 and above)? SELECT 'AlwaysOn', case when SERVERPROPERTY('IsHadrEnabled') = 0 then 'No' when SERVERPROPERTY('IsHadrEnabled') = 1 then 'Yes' else SERVERPROPERTY('IsHadrEnabled') end; ----------------------------------------------------------AlwaysOn status declare @c int; declare @rd nvarchar(60); declare @osd nvarchar(60); declare @rhd nvarchar(60); declare @shd nvarchar(60); declare @csd nvarchar(60); select @c = COUNT(name) from sys.all_objects where name = 'dm_hadr_availability_replica_states'; if @c = 0 print N'No AlwaysOn Status'; else select @rd = role_desc, @osd= case when operational_state_desc is null then 'Replica is not local' else operational_state_desc end, @rhd = recovery_health_desc, @shd = synchronization_health_desc, @csd = connected_state_desc from sys.dm_hadr_availability_replica_states; print @rd print @osd print @rhd print @shd print @csd ----------------------------------------------------------Memory usage per database print N'Memory Usage per User Database'; SELECT substring(DB_NAME(database_id),1,40) AS [Database Name] ,COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id > 4 AND database_id <> 32767 AND db_name(database_id) <> 'SSISDB' GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE); ----------------------------------------------------------Memory usage of in-memory OLTP tables print N'In-memory OLTP table usage'; SELECT object_name(object_id) AS Name, * FROM sys.dm_db_xtp_table_memory_stats; ----------------------------------------------------------Last backup time per database SELECT substring(sdb.Name,1,40) AS DatabaseName, COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime FROM sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name where sdb.Name <> 'tempdb' GROUP BY sdb.Name; ----------------------------------------------------------No log backups for FULL or BULK_LOGGED recovery model databases in last 30 days print N'Databases with FULL or BULK_LOGGED recovery model and no log backups in last 30 days'; SELECT name AS at_risk_database FROM sys.databases where recovery_model_desc in('FULL','BULK_LOGGED') and name not in( SELECT msdb.dbo.backupset.database_name AS DBName FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30) and msdb..backupset.type = 'L' group by msdb.dbo.backupset.database_name ); ----------------------------------------------------------Databases with no backups at all in the last 30 says print N'Databases with NO backups in last 30 days'; SELECT name AS at_risk_database FROM sys.databases where name <> 'tempdb' and name not in( SELECT substring(msdb.dbo.backupset.database_name,1,40) AS DBName FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30) group by msdb.dbo.backupset.database_name ); ----------------------------------------------------------Backups for the previous week per database print N'All backups for previous week'; SELECT CONVERT(CHAR(40), SERVERPROPERTY('Servername')) AS Server, substring(msdb.dbo.backupset.database_name,1,40) AS DBName, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File' WHEN 'P' THEN 'Partial' WHEN 'I' THEN 'Differential database' WHEN 'G' THEN 'Differential file' WHEN 'Q' THEN 'Differential partial' WHEN NULL THEN msdb..backupset.type END AS backup_type, msdb.dbo.backupset.backup_size, substring(msdb.dbo.backupmediafamily.logical_device_name,1,50) AS logical_device_name, substring(msdb.dbo.backupmediafamily.physical_device_name,1,50) AS physical_device_name, substring(msdb.dbo.backupset.name,1,50) AS backupset_name, substring(msdb.dbo.backupset.description,1,50) AS description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date; ----------------------------------------------------------Jobs that failed in the last 24 hours print N'Jobs Failing in last 24 hours'; ----------------------------------------------------------Variable Declarations DECLARE @PreviousDate datetime DECLARE @Year VARCHAR(4) DECLARE @Month VARCHAR(2) DECLARE @MonthPre VARCHAR(2) DECLARE @Day VARCHAR(2) DECLARE @DayPre VARCHAR(2) DECLARE @FinalDate INT ----------------------------------------------------------Initialize Variables SET @PreviousDate = DATEADD(dd, -1, GETDATE()) --Last 1 day SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT) --Final Logic SELECT substring(j.[name],1,40) AS JOB, substring(s.step_name,1,40) AS Step, h.step_id, substring(h.step_name,1,40) AS Step, h.run_date, h.run_time, h.sql_severity, substring(h.message,1,100) AS Message, h.server FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id WHERE h.run_status = 0 --Failure AND h.run_date > @FinalDate ORDER BY h.instance_id DESC; ----------------------------------------------------------Missing indexes print N'Missing Indexes'; SELECT substring(so.name,1,40) AS Name , (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact , ddmid.equality_columns , ddmid.inequality_columns , ddmid.included_columns FROM sys.dm_db_missing_index_group_stats AS ddmigs INNER JOIN sys.dm_db_missing_index_groups AS ddmig ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS ddmid ON ddmig.index_handle = ddmid.index_handle INNER JOIN sys.objects so WITH (nolock) ON ddmid.object_id = so.object_id WHERE ddmigs.group_handle IN ( SELECT TOP (5000) group_handle FROM sys.dm_db_missing_index_group_stats WITH (nolock) ORDER BY (avg_total_user_cost * avg_user_impact)*(user_seeks+user_scans)DESC); ----------------------------------------------------------Duplicate indexes print N'Duplicate Indexes'; DECLARE @SCHEMANAME VARCHAR(30); DECLARE @TABLENAME VARCHAR(127); WITH ind_list AS( select o.schema_id, i.object_id, i.index_id, i.name, i.type_desc, i.is_unique, i.is_primary_key, STUFF( (SELECT ',' + tc.name FROM sys.index_columns ic JOIN sys.columns tc ON tc.column_id = ic.column_id AND tc.object_id = ic.object_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.index_column_id FOR XML PATH ('') ),1,1,'' ) index_columns, STUFF( (SELECT ',' + tc.name FROM sys.index_columns ic JOIN sys.columns tc ON tc.column_id = ic.column_id AND tc.object_id = ic.object_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH ('') ),1,1,'' ) include_columns FROM sys.indexes i JOIN sys.objects o ON o.object_id = i.object_id WHERE i.index_id > 0 AND i.type_desc <> 'XML' AND object_name(i.object_id) LIKE @TABLENAME AND i.is_disabled = 0 AND schema_name(o.schema_id) LIKE @SCHEMANAME ) SELECT substring(schema_name(included_indexes.schema_id),1,30) AS owner, object_name(included_indexes.object_id) table_name, (SELECT SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE st.object_id = included_indexes.object_id AND st.index_id < 2 ) num_rows, included_indexes.name included_index_name, included_indexes.index_columns included_index_columns, included_indexes.include_columns included_index_include_columns, included_indexes.type_desc included_index_type, included_indexes.is_unique included_index_uniqueness, included_indexes.is_primary_key included_index_PK, (SELECT SUM(a.total_pages) * 8 FROM sys.allocation_units a JOIN sys.partitions p ON a.container_id = p.partition_id WHERE p.object_id = included_indexes.object_id AND p.index_id = included_indexes.index_id ) included_index_size_kb, including_indexes.name including_index_name, including_indexes.index_columns including_index_columns, including_indexes.include_columns including_index_include_columns, including_indexes.type_desc including_index_type, including_indexes.is_unique including_index_uniqueness, including_indexes.is_primary_key including_index_PK, (SELECT SUM(a.total_pages) * 8 FROM sys.allocation_units a JOIN sys.partitions p ON a.container_id = p.partition_id WHERE p.object_id = including_indexes.object_id AND p.index_id = including_indexes.index_id ) including_index_size_kb FROM ind_list included_indexes JOIN ind_list including_indexes ON including_indexes.object_id = included_indexes.object_id JOIN sys.partitions ing_p ON ing_p.object_id = including_indexes.object_id AND ing_p.index_id = including_indexes.index_id JOIN sys.allocation_units ing_a ON ing_a.container_id = ing_p.partition_id WHERE including_indexes.index_id <> included_indexes.index_id AND LEN(included_indexes.index_columns) <= LEN(including_indexes.index_columns) AND included_indexes.index_columns + ',' = SUBSTRING(including_indexes.index_columns,1, LEN(included_indexes.index_columns + ',')) ORDER BY 2 DESC; ----------------------------------------------------------High index fragmentation check print N'Index with HIGH Fragmentation'; EXEC sp_MSforeachdb ' USE [?] SELECT ''?'' AS DB_NAME, QUOTENAME(sysind.name) AS [index_name], indstat.* FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ''LIMITED'') AS indstat INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND indstat.index_id = sysind.index_id where avg_fragmentation_in_percent >= 30 ORDER BY avg_fragmentation_in_percent DESC; ' use master GO -----------------Tables that have not had a stats update in more than 30 days and have significant changes EXEC sp_MSforeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' SELECT ''?'' AS DB_NAME,obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter FROM sys.objects AS obj INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE obj.type = ''U'' AND last_updated < getdate() - 30 AND modification_counter > 1000 --OR last_updated IS NULL; ' ----------------------------------------------------------Wait stats print N'Wait Stats'; SELECT * FROM sys.dm_os_wait_stats where wait_time_ms > 10000 ORDER BY wait_time_ms DESC; ----------------------------------------------------------Users and roles print N'Users and Roles'; WITH Roles_CTE(Role_Name, Username) AS ( SELECT User_Name(sm.[groupuid]) as [Role_Name], user_name(sm.[memberuid]) as [Username] FROM [sys].[sysmembers] sm ) SELECT Roles_CTE.Role_Name, [DatabaseUserName] = princ.[name], [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'A' THEN 'Application Role' WHEN 'R' THEN 'Database Role' WHEN 'C' THEN 'User mapped to a certificate' WHEN 'K' THEN 'User mapped to an asymmetric key' END FROM sys.database_principals princ JOIN Roles_CTE on Username = princ.name where princ.type in ('S', 'U', 'G', 'A', 'R', 'C', 'K') ORDER BY princ.name; ----------------------------------------------------------Job information print N'Job Information'; SELECT [JobName] = [jobs].[name] ,[Category] = [categories].[name] ,[Owner] = SUSER_SNAME([jobs].[owner_sid]) ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END ,[Description] = [jobs].[description] ,[Occurs] = CASE [schedule].[freq_type] WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPU(s) become idle' ELSE '' END ,[Occurs_detail] = CASE [schedule].[freq_type] WHEN 1 THEN 'O' WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)' WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + LEFT( CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END , LEN( CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ) - 1 ) WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' WHEN 32 THEN 'The ' + CASE [schedule].[freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + CASE [schedule].[freq_interval] WHEN 1 THEN ' Sunday' WHEN 2 THEN ' Monday' WHEN 3 THEN ' Tuesday' WHEN 4 THEN ' Wednesday' WHEN 5 THEN ' Thursday' WHEN 6 THEN ' Friday' WHEN 7 THEN ' Saturday' WHEN 8 THEN ' Day' WHEN 9 THEN ' Weekday' WHEN 10 THEN ' Weekend Day' END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' ELSE '' END ,[Frequency] = CASE [schedule].[freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 2 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 4 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 8 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') ELSE '' END ,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration]) ,[Next_Run_Date] = CASE [jobschedule].[next_run_date] WHEN 0 THEN CONVERT(DATETIME, '1900/1/1') ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':')) END FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) ON [jobs].[job_id] = [jobschedule].[job_id] LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) ON [jobschedule].[schedule_id] = [schedule].[schedule_id] INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) ON [jobs].[category_id] = [categories].[category_id] LEFT OUTER JOIN ( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + (([run_duration] % 10000) / 100 * 60) + ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id]) FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK) WHERE [step_id] = 0 GROUP BY [job_id] ) AS [jobhistory] ON [jobhistory].[job_id] = [jobs].[job_id]; ----------------------------------------------------------Existing linked server listing print N'Linked Server Information'; declare @x int; select @x = COUNT(name) from sys.all_objects where name = 'Servers'; if @x <> 0 SELECT * --c.name, provider, data_source, is_remote_login_enabled, b.modify_date FROM sys.Servers a LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id where a.server_id <> 0; else exec sp_linkedservers; ----------------------------------------------------------User statistics identification print N'User statistics'; select s.name as STATS_NAME, SCHEMA_NAME(ob.Schema_id) AS SCHEMA_NAME, OBJECT_NAME(s.object_id) AS TABLE_NAME FROM sys.stats s INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys' AND Auto_Created = 0 AND User_Created = 1;