QueryVers MSSql90[] = { {"generic_query", "select getdate()"}, {"database_size", "sp_MSforeachdb ' " "declare " " @dbsize bigint," " @logsize bigint," " @reservedpages bigint," " @usedpages bigint," " @pages bigint" " select @dbsize = sum(convert(bigint,case when type <> 1 then size else 0 end)) " " , @logsize = sum(convert(bigint,case when type = 1 then size else 0 end)) " " from [?].sys.database_files " " select @reservedpages = sum(a.total_pages), " " @usedpages = sum(a.used_pages), " " @pages = sum( " " CASE " " When it.internal_type IN (202,204) Then 0 " " When a.type <> 1 Then a.used_pages " " When p.index_id < 2 Then a.data_pages " " Else 0 " " END " " ) " " from [?].sys.partitions p join [?].sys.allocation_units a on p.partition_id = a.container_id " " left join sys.internal_tables it on p.object_id = it.object_id " " select " " object = ''?'', " " database_size = ((convert(dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192), " " free_space = case when @dbsize >= @reservedpages then " " ((convert(dec (15,2),@dbsize) - convert(dec (15,2),@reservedpages)) * 8192) " " else 0 " " end, " " reserved = (@reservedpages * 8192), " " data = (@pages * 8192), " " index_size = ((@usedpages - @pages) * 8192), " " unused = ((@reservedpages - @usedpages) * 8192), " " log_size = (@logsize * 8192), " " used = (@usedpages * 8192)' "}, {"buf_cachehit_ratio", "select a.cntr_value as page_lookups, b.cntr_value as page_reads, c.cntr_value as page_writes from master.sys.dm_os_performance_counters a, master.sys.dm_os_performance_counters b, master.sys.dm_os_performance_counters c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec' " " and LTRIM(RTRIM(a.object_name)) LIKE '%:Buffer Manager' and LTRIM(RTRIM(b.object_name)) LIKE '%:Buffer Manager' and LTRIM(RTRIM(c.object_name)) LIKE '%:Buffer Manager'"}, {"lock_requests", "select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time " "from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Requests/sec'"}, {"database_state", "select rtrim(name) object, state, rtrim(state_desc) state_desc from master.sys.databases"}, {"log_cachehit_ratio", "select RTRIM(a.instance_name) as object,a.cntr_value cache_hits,b.cntr_value cache_base from master.sys.dm_os_performance_counters a, master.sys.dm_os_performance_counters b " "WHERE LTRIM(RTRIM(a.object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(b.object_name)) LIKE '%:Databases' AND a.instance_name = b.instance_name and " "replace(lower(a.counter_name),char(32),'') LIKE 'logcachehitratio' and replace(lower(b.counter_name),char(32),'') LIKE 'logcachehitratiobase'"}, {"login_count", "select cntr_value connections from master.sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:General Statistics' AND LTRIM(RTRIM(counter_name)) = 'User Connections'"}, {"active_users", "select rtrim(instance_name) object ,cntr_value active_connections from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Active Transactions'"}, {"deadlocks", "select rtrim(instance_name) object ,cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Number of Deadlocks/sec'"}, {"lock_timeouts", "select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Timeouts/sec'"}, {"lock_waits", "select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Waits/sec'"}, {"transactions", "select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Transactions/sec' order by 1"}, {"log_flush_waits", "select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters a " "WHERE LTRIM(RTRIM(a.object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(a.counter_name)) = 'Log Flush Waits/sec'"}, {"page_reads", "select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Buffer Manager' AND LTRIM(RTRIM(counter_name)) = 'Page reads/sec'"}, {"page_writes", "select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Buffer Manager' AND LTRIM(RTRIM(counter_name)) = 'Page writes/sec'"}, {"latch_waits", "select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Latches' AND LTRIM(RTRIM(counter_name)) = 'Latch Waits/sec'"}, {"full_scans", "select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Access Methods' AND LTRIM(RTRIM(counter_name)) = 'Full Scans/sec'"}, {"database_count", "select count(*) count from master.sys.sysdatabases"}, {"server_startup", "select datediff(dd, crdate, getdate()) count, crdate date from master.sys.sysdatabases where name ='tempdb'"}, {"log_file_growths", "select rtrim(instance_name) object, cntr_value count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Log Growths'"}, {"log_file_shrinks", "select rtrim(instance_name) object, cntr_value count from master.dbo.sysperfinfo where counter_name = 'Log Shrinks'"}, {"lock_memory", "select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Memory (KB)'"}, {"connection_memory", "select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Connection Memory (KB)'"}, {"optimizer_memory", "select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Optimizer Memory (KB)'"}, {"sqlcache_memory", "select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'SQL Cache Memory (KB)'"}, {"total_memory", "select (select (cntr_value*1024) from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Total Server Memory (KB)') count, " "(select (cntr_value*1024) from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Target Server Memory (KB)') target"}, {"locks_used", "select (select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Blocks') lock_blocks, " "(select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Owner Blocks') owner_blocks, " " convert(dec(15),maximum) maximum, convert(dec(15),value_in_use) value_in_use from sys.configurations where configuration_id = 106"}, {"workspace_memory", "select (select (cntr_value*1024) from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Granted Workspace Memory (KB)') granted," "(select (cntr_value*1024) value from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Maximum Workspace Memory (KB)') maximum"}, {"average_waittime", "select rtrim(a.instance_name) object, a.cntr_value lock_waits, b.cntr_value wait_time from sys.dm_os_performance_counters a, sys.dm_os_performance_counters b WHERE " "LTRIM(RTRIM(a.object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(a.counter_name)) = 'Lock Waits/sec' and LTRIM(RTRIM(b.object_name)) LIKE '%:Locks' AND " "LTRIM(RTRIM(b.counter_name)) = 'Lock Wait Time (ms)' and a.instance_name = b.instance_name"}, {"server_cpu", "select @@CPU_BUSY cpu_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time ,(select cpu_count from sys.dm_os_sys_info) cpu_count"}, {"server_io", "select @@IO_BUSY io_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time"}, {"free_connections", "select (select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:General Statistics' AND LTRIM(RTRIM(counter_name)) = 'User Connections') connections, " "(select convert(dec(15),value_in_use) from sys.configurations where configuration_id = 103) config_connections, convert(dec(12,0),@@MAX_CONNECTIONS) max_connections"}, {"user_cpu", "select convert(varchar(10),session_id) spid, cpu_time cpu_busy, rtrim(login_name) loginname, rtrim(host_process_id) hostid, (ISNULL(rtrim(program_name),' ')) programname, " "datediff(ss, '1970-01-01 00:00:00', getdate()) time, (select cpu_count from sys.dm_os_sys_info) cpu_count from sys.dm_exec_sessions where host_name <> '' and program_name <> '' order by 2 desc"}, {"user_waits", "SELECT @@ROWCOUNT as bcount,db_name(dtra.database_id) db_name, (convert(dec(20,3),wait_duration_ms))/1000 waittime, task.blocking_session_id sid_blocker, isNull(bsess.host_process_id,-1) hid_blocker, " "isNull(bsess.login_name,'n/a') uid_blocker, task.session_id sid_blocked, sess.host_process_id hid_blocked, sess.login_name uid_blocked, convert(varchar(255),isNull(SUBSTRING (sql.text,1,255),'n/a')) as sql_blocked " "FROM sys.dm_os_waiting_tasks AS task, sys.dm_tran_database_transactions as dtra, sys.dm_exec_sessions as sess, sys.dm_exec_sessions as bsess, sys.dm_exec_requests as t2 CROSS APPLY sys.dm_exec_sql_text(t2.sql_handle) AS sql " "where t2.session_id = task.session_id and sess.session_id = task.session_id and bsess.session_id = task.blocking_session_id and dtra.transaction_id = t2.transaction_id and IsNull(task.blocking_session_id,0) <> 0"}, {"locked_users", "select count(*) count FROM sys.dm_os_waiting_tasks where blocking_session_id <> 0"}, {"blocked_users", "select count(*) count from sys.sysprocesses a, sys.sysprocesses b where a.blocked != 0 and a.blocked = b.spid and a.status != 'suspended'"}, {"av_fragmentation", "sp_MSforeachdb 'SELECT distinct ''?'' db_name, OBJECT_NAME(i.object_id) AS tablename, isNull(i.name,'''') AS indexname, phystat.avg_fragmentation_in_percent as fragmentation, alloc_unit_type_desc as alloc_unit " "FROM sys.dm_db_index_physical_stats(db_id(''?''), NULL, NULL, NULL, ''LIMITED'') phystat JOIN [?].sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id where OBJECT_NAME(i.object_id) is not null'"}, {"logic_fragment", "select 'dummy' dummy"}, {"free_space", "sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated, " "ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where ((f.type <> 1) and f.growth > 0 and size > 0 ) having ((select count(*) from [?].sys.database_files where max_size = -1 and type <> 1 and growth > 0) = 0)),-1) max, " "ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files where type <> 1 and growth = 0 and size > 0),-1) max2, " "ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a),-1) used, " "(select count(*) from [?].sys.database_files cg where max_size = -1 and type <> 1 and growth > 0) unlimited' "}, {"fg_free_space", "sp_MSforeachdb 'select ''?'' db_name, g.name fg_name, ISNULL((select convert(bigint,sum(size))* 8192 from [?].sys.database_files f where f.data_space_id = g.data_space_id),-1) allocated, " "ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where (f.growth > 0 and f.data_space_id = g.data_space_id and type <> 1) having ((select count(*) from [?].sys.database_files f where max_size = -1 and growth > 0 and f.data_space_id = g.data_space_id and type <> 1) = 0 )),-1) max, " "ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files f where growth = 0 and f.data_space_id = g.data_space_id and type <> 1),-1) max2, " "ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a where a.data_space_id = g.data_space_id),0) used, " "(select count(*) from [?].sys.database_files f where max_size = -1 and growth > 0 and f.data_space_id = g.data_space_id and type <> 1) unlimited from [?].sys.filegroups g' "}, {"table_space", "sp_MSforeachdb 'use [?] select ''?'' db_name, SCHEMA_NAME(o.schema_id) schema_name, o.name table_name, " "((SELECT IsNULL(SUM(reserved_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id) + " "(SELECT IsNULL(SUM(reserved_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats p, [?].sys.internal_tables it WHERE " "it.parent_id = o.object_id AND it.internal_type IN (202,204) AND p.object_id = it.object_id)) table_size, " "(((SELECT IsNULL(SUM(used_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id ) + " "(SELECT IsNULL(SUM(used_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats p, [?].sys.internal_tables it WHERE " "it.parent_id = o.object_id AND it.internal_type IN (202,204) AND p.object_id = it.object_id)) - (SELECT SUM(CASE WHEN (index_id < 2) " "THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)*8192 ELSE (lob_used_page_count + " "row_overflow_used_page_count)*8192 END) FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id)) index_size, (SELECT " "SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)*8192 ELSE " "(lob_used_page_count + row_overflow_used_page_count)*8192 END) FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id) " "data_size from [?].sys.objects o where type=''U'' use master'"}, {"logfile_usage", "sp_MSforeachdb 'select ''?'' db_name, (select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1) allocated, ISNULL((select sum(convert(bigint,size))*8 from [?].sys.database_files f where f.state_desc != ''RESTORING'' and (f.type = 1) and f.growth = 0),0) max, " "ISNULL((select sum(convert(bigint,max_size))*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1 and growth > 0),0) max2, (select count(*) from [?].sys.database_files cg where state_desc != ''RESTORING'' and max_size = -1 and type = 1 and growth > 0) unlimited, " "(select CASE WHEN (a.cntr_value) > 0 THEN convert(dec(15,3),b.cntr_value)/convert(dec(15,3),a.cntr_value)*100 ELSE 0 END from sys.dm_os_performance_counters a,sys.dm_os_performance_counters b " "where a.counter_name = ''Log File(s) Size (KB)'' and b.counter_name = ''Log File(s) Used Size (KB)'' AND LTRIM(RTRIM(a.object_name)) LIKE ''%:Databases'' AND LTRIM(RTRIM(b.object_name)) LIKE ''%:Databases'' AND a.instance_name = b.instance_name and a.instance_name = ''?'') used, " "(select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1)-8 logsize '"}, {"backup_status", " SELECT B.name as db_name,ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes ,ISNULL(ABS(DATEDIFF(day, GetDate(), MAX(backup_finish_date))), -1) as days_sincebackup, " "ISNULL(ABS(DATEDIFF(day, GetDate(), MAX(create_date))), -1) as days_sincecreation,MAX(B.state) as db_state,ISNULL(Convert(char(10), MAX(backup_finish_date), 101),'NEVER') as last_backup,ISNULL(MAX(B.source_database_id),0) as is_snapshot " " FROM master.sys.databases B LEFT OUTER JOIN msdb.dbo.backupset A ON (A.database_name = B.name AND A.type = 'D') GROUP BY B.name ORDER BY B.name "}, {"transaction_backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM ( SELECT * from master.sys.databases C where C.recovery_model != 3 ) B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'L' GROUP BY B.name ORDER BY B.name"}, {"differential_backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.sys.databases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'I' GROUP BY B.name ORDER BY B.name"}, {"long_queries", "SELECT r.session_id spid, s.host_process_id hostid, s.host_name, s.login_name loginname, s.program_name, (convert(dec(15,3),r.cpu_time))/1000 cpu_time, (convert(dec(15,3),r.total_elapsed_time))/1000 elapsed_time, " "convert(varchar(3000),isNull(SUBSTRING (st.text,1,3000),'n/a')) as sql_text FROM sys.dm_exec_sessions s, sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st " "where s.session_id = r.session_id and r.status = 'running' and s.session_id <> @@SPID"}, {"long_jobs", "select sp.session_id spid, sjs.step_id, sjs.step_name, sj.name jobname, datediff(s,dms.last_request_start_time,getdate()) elapsed_time,sce.name as category_name, " "sp.host_name host_name, sp.login_name loginname, dms.last_request_start_time start_time, sql_details.text sql_text from sys.dm_exec_sessions dms, " "msdb.dbo.sysjobs sj, msdb.dbo.sysjobsteps sjs,msdb.dbo.syscategories sce, " "sys.dm_exec_sessions sp, sys.dm_exec_requests rq outer apply sys.dm_exec_sql_text(rq.sql_handle) sql_details where sp.session_id <> @@SPID and " "dms.session_id = sp.session_id and sp.session_id = rq.session_id " "and STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') = RIGHT(sj.job_id, 17) " "and sp.program_name LIKE 'SQLAgent - TSQL JobStep %' and SUBSTRING(REVERSE(RTRIM(sp.program_name)),2,1) = sjs.step_id " "and sj.job_id = sjs.job_id and sj.category_id = sce.category_id "}, {"active_connection_ratio", "select (select CASE value WHEN 0 THEN 32767 ELSE value END from sys.sysconfigures where comment like '%user connections%') as total," " (select count(dbid) from sys.sysprocesses where hostname <> '' and dbid > 0) as active"}, {"logfile_size", "sp_MSforeachdb 'select ''?'' db_name, (select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1) allocated, ISNULL((select sum(max_size)*8 from [?].sys.database_files f where f.state_desc != ''RESTORING'' and (f.type = 1) and f.growth > 0 and max_size = -1),-1) max, " "ISNULL((select sum(convert(bigint,max_size))*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1 and growth > 0),-1) max2, (select count(*) from [?].sys.database_files cg where state_desc != ''RESTORING'' and max_size = -1 and type = 1 and growth > 0) unlimited, " "(select CASE WHEN (a.cntr_value) > 0 THEN convert(dec(15,3),b.cntr_value)/convert(dec(15,3),a.cntr_value)*100 ELSE 0 END from sys.dm_os_performance_counters a,sys.dm_os_performance_counters b where a.counter_name = ''Log File(s) Size (KB)'' and b.counter_name = ''Log File(s) Used Size (KB)'' AND LTRIM(RTRIM(a.object_name)) LIKE ''%:Databases'' AND LTRIM(RTRIM(b.object_name)) LIKE ''%:Databases'' AND a.instance_name = b.instance_name and a.instance_name = ''?'') used, " "(select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1)-8 logsize '"}, {"mirror_state", "SELECT rtrim(DB_NAME(database_id)) AS 'object', mirroring_state AS state, rtrim(mirroring_state_desc) AS mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL"}, {"mirror_witness_server","SELECT rtrim(DB_NAME(database_id)) AS 'object', rtrim(mirroring_witness_name) AS mirroring_witness_name, mirroring_witness_state AS state, rtrim(mirroring_witness_state_desc) AS mirroring_witness_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL"}, {"mirror_sqlinstance","SELECT rtrim(DB_NAME(database_id)) AS 'object', rtrim(mirroring_partner_instance) AS mirroring_partner_instance FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_role = 1"}, {"suspect_pages","SELECT count (A.database_id) pdcount, B.name as object FROM msdb.dbo.suspect_pages A RIGHT OUTER JOIN master.sys.databases B ON A.database_id = B.database_id AND (event_type = 1 OR event_type = 2 OR event_type = 3) GROUP BY B.name"}, {"agent_job_failure", "select h.job_id as job_id, j.name as job_name, c.name category_name," "dateadd(hh,run_time/10000,dateadd(mi,run_time%10000/100,dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) as rundate, " "datediff(minute, dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))), getdate()) as elapsed_time " "from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id " "inner join msdb.dbo.syscategories c on j.category_id = c.category_id " "where c.category_class = 1 and h.run_status <> 1 and h.step_id = 0 and " "dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) > dateadd(ss,-60*60*36,getdate()) " "order by elapsed_time"}, {"log_shipping_primary_status", "select primary_server, primary_database, " "case when (datediff(minute, last_backup_date_utc, getutcdate()) > backup_threshold) then 0 else 1 end as primary_status " "from msdb.dbo.log_shipping_monitor_primary with (nolock) order by primary_server, primary_database"}, {"log_shipping_secondary_status", "select secondary_server, secondary_database, primary_server, primary_database, " "case when ((datediff(minute, last_restored_date_utc, getutcdate()) > restore_threshold) " "or (last_restored_latency > restore_threshold)) then 0 else 1 end as secondary_status " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_primary_time_since_last_backup","select primary_server, primary_database, last_backup_file, " "datediff(minute, last_backup_date_utc, getutcdate()) as time_since_last_backup " "from msdb.dbo.log_shipping_monitor_primary with (nolock) order by primary_server, primary_database"}, {"log_shipping_secondary_time_since_last_copy","select secondary_server, secondary_database, primary_server, primary_database, last_copied_file, " "datediff(minute, last_copied_date_utc, getutcdate()) as time_since_last_copy " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_secondary_time_since_last_restore","select secondary_server, secondary_database, primary_server, primary_database, last_restored_file, " "datediff(minute, last_restored_date_utc, getutcdate()) as time_since_last_restore " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_secondary_last_restored_latency", "select secondary_server, secondary_database, primary_server, primary_database, last_restored_latency " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"fg_freeSpace_with_avail_disk", " SET NOCOUNT ON " " DECLARE @database_id int " " DECLARE @db_name sysname " " DECLARE @sql_string nvarchar(2000) " " DECLARE @adriveletter nchar(2),@agroupname sysname,@adbname nvarchar(256),@adbid smallint,@agroupid smallint,@aspace_available int,@aspace_used int, " " @file_size int " " DECLARE @maxavailable bigint,@old_spaceused bigint,@new_spaceused bigint,@old_spaceav bigint,@new_spaceav bigint, @old_file_size bigint,@new_file_size bigint " " DECLARE @autogrow int,@maxsize int,@space_left int,@fileid int,@groupid int,@driveletter nvarchar(1),@mbfree int,@spaceused int,@spaceavialable int, " " @dbid sysname,@canttest smallint " " DECLARE @ver nvarchar(128) " " CREATE TABLE [#temptab]([driveletter] nvarchar(2), " " [groupname] [sysname] NULL, " " [dbname] [nvarchar](128) NULL, " " [dbid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [space_used] [int] NULL, " " [space_available] [int] NULL, " " [file_size] [int] NULL) ON [PRIMARY] " " create table #freespace( Drive nvarchar(1),MB_Free int) " " insert into #freespace exec [master].[dbo].[xp_fixeddrives] " " CREATE TABLE [#space_tab]([groupname] [sysname] NULL,[dbname] [nvarchar] (128)NULL, " " [dbid] [smallint] NULL, " " [fileid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [filetype] [varchar](9) NOT NULL, " " [name] [sysname] NOT NULL, " " [filename] [nvarchar](260) NOT NULL, " " [file_size] [int] NULL, " " [space_used] [int] NULL, " " [space_left] [int] NULL, " " [autogrow] [int] NOT NULL, " " [maxsizeinmb] [int] NULL, " " [maxsize] [int] NOT NULL, " " [space_available] [int] NOT NULL) ON [PRIMARY] " " SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar) " " SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > 0 " " group by dbid,name " " ORDER BY dbid " " end " " else select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state when 0 then 0 else 1 end " " from master.sys.databases " " WHERE database_id > 0 " " group by database_id,name,state " " ORDER BY database_id " " WHILE @db_name IS NOT NULL " " BEGIN " " if @canttest = 0 " " begin " " SET @sql_string = 'USE ' + QUOTENAME(@db_name) + CHAR(10) " " SET @sql_string = @sql_string + 'Select isnull(g.groupname,''TRAN LOG''),DB_NAME(),db_id() as dbid,fileid,s.groupid,case when s.groupid = 0 " " then ''log file'' else ''data file'' end as filetype, name ,filename, " " [file_size] = convert(int,round((s.size*1.000)/128.000,0)), " " [space_used] = convert(int,round(fileproperty(s.name,''SpaceUsed'')/128.000,0)), " " [space_left] = convert(int,round((s.size-fileproperty(s.name,''SpaceUsed''))/128.000,0)), " " case when growth > 0 then 1 else 0 end as autogrow, " " case when maxsize = -1 then -1 else convert(decimal,maxsize)*8/1024 end as maxsizeinmb" " ,maxsize,0 as space_available from dbo.sysfiles s " " left outer join dbo.sysfilegroups g " " on s.groupid = g.groupid' " " INSERT INTO [#space_tab] " " EXEC sp_executesql @sql_string " " end " " SET @db_name = NULL " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > @database_id " " group by dbid,name " " ORDER BY dbid " " end " " else " " select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state " " when 0 then 0 else 1 end " " from master.sys.databases d " " WHERE database_id > @database_id " " and database_id not in (select database_id from sys.master_files where is_sparse = 1) " " group by database_id,name,state " " ORDER BY database_id " " END " " DECLARE freespace_cur Cursor " " FOR select autogrow,maxsizeinmb,space_left,fileid,groupid,substring(filename,1,1),space_used,dbid from #space_tab " " Open freespace_cur " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if @autogrow = 1 " " begin " " select @mbfree = (select MB_Free from #freespace where Drive = @driveletter) " " if @maxsize <> -1 " " begin " " if @maxsize > @mbfree " " select @maxsize = @mbfree " " else " " select @maxsize = @maxsize - @spaceused " " end " " else " " begin " " select @maxsize = @mbfree + @space_left " " end " " update [#space_tab] " " set space_available = @maxsize " " where fileid = @fileid and groupid = @groupid and dbid = @dbid " " end " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " END " " CLOSE freespace_cur " " DEALLOCATE freespace_cur " " drop table #freespace " " declare autogrow_cur cursor for " " select substring(filename,1,1),groupname,dbname,dbid,groupid,space_available,space_used,file_size auto_ from [#space_tab] " " where autogrow = 1 " " order by dbid,groupid,1 " " Open autogrow_cur " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if not exists (select * from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " begin " " insert into #temptab values(@adriveletter,@agroupname,@adbname,@adbid,@agroupid,@aspace_used,@aspace_available,@file_size) " " end " " else " " begin " " select @old_spaceused = (select space_used from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceused = @old_spaceused + @aspace_used " " select @old_spaceav = (select space_available from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceav = @old_spaceav+ @aspace_available " " select @old_file_size = (select file_size from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_file_size = @old_file_size + @file_size " " update #temptab set space_used = @new_spaceused ,space_available= @new_spaceav, file_size=@new_file_size " " where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid " " end " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " end " " close autogrow_cur " " deallocate autogrow_cur " " select groupname,dbname,dbid,groupid,sum(space_used) as space_used,sum(space_available) as space_available,sum(file_size) as file_size " " into #autogrowfiles from #temptab " " group by groupname,dbname,groupid,dbid " " order by dbid,groupid " " drop table #temptab " " select groupname,dbname,dbid,groupid,sum(space_left) as space_left,SUM(space_used) as space_used,sum(file_size) as file_size " " into #fixedfiles " " from [#space_tab] " " where autogrow = 0 " " group by dbid,groupid,dbname,groupname " " select isnull(a.dbname,b.dbname) as db_name,isnull(a.groupname,b.groupname) as fg_name,convert(decimal,(isnull(a.space_available,0)+isnull(b.space_left,0)))*1024*1024 as total_space_available, " " convert(decimal,(isnull(a.space_used,0)+isnull(b.space_used,0)))*1024*1024 as used, " " convert(decimal,(isnull(a.file_size,0)+isnull(b.file_size,0)))*1024*1024 as allocated, " " convert(decimal,isnull(a.space_available,0))*1024*1024 as autogrowavailable,convert(decimal,isnull(b.space_left,0))*1024*1024 as fixedfileavailable into #results_tab from " " #autogrowfiles a " " full outer join " " #fixedfiles b " " on a.dbid = b.dbid and a.groupid = b.groupid " " order by 1,2 " " select db_name,fg_name,used,allocated, " " 100-(convert (decimal,used)+0.001) / ((convert (decimal,used)+0.001)+(convert (decimal,total_space_available)+0.001))*100 " " as 'free_pct' " " from #results_tab " " drop table #results_tab " " drop table #space_tab " " drop table #autogrowfiles " " drop table #fixedfiles "}, {"aag_cluster_quorum_state", "select rtrim(cluster_name) cluster_name, quorum_state, rtrim(quorum_state_desc) quorum_state_desc from sys.dm_hadr_cluster"}, {"aag_cluster_members_state", "select rtrim(member_name) object, member_state, rtrim(member_state_desc) member_state_desc from sys.dm_hadr_cluster_members"}, {"aag_synchronization_health", "select a.name, rtrim(b.primary_replica) primary_replica, b.synchronization_health, " "rtrim(b.synchronization_health_desc) synchronization_health_desc " "from sys.availability_groups a, sys.dm_hadr_availability_group_states b where a.group_id = b.group_id"}, {"aag_replica_synchronization_health", "select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.synchronization_health, c.synchronization_health_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id"}, {"aag_replica_connected_state", "select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.connected_state, c.connected_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id"}, {"aag_replica_recovery_health", "select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.recovery_health, c.recovery_health_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.is_local != 0"}, {"aag_replica_operational_state", "select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.operational_state, c.operational_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.is_local != 0"}, {"aag_db_replica_synchronization_state", "select a.name, rtrim(b.replica_server_name) object, rtrim(d.name) database_name, c.synchronization_state, " "c.synchronization_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_database_replica_states c,master.sys.databases d " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.database_id = d.database_id"}, {"aag_db_page_status", "select a.name, rtrim(b.replica_server_name) object, rtrim(d.name) database_name,c.page_status " "from sys.availability_groups a, sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_auto_page_repair c,master.sys.databases d, " "sys.dm_hadr_database_replica_states e " "where a.group_id = b.group_id and b.group_id = e.group_id and b.replica_id = e.replica_id and c.database_id = d.database_id and c.database_id = e.database_id"}, {"aag_listener_state", "SELECT rtrim(AGC.name) name, rtrim(RCS.replica_server_name)object, ARS.role, ARS.role_desc, rtrim(AGL.dns_name) dns_name, LIP.state_desc, LIP.state FROM " "sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN " "sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL " "ON AGL.group_id = ARS.group_id INNER JOIN sys.availability_group_listener_ip_addresses AS LIP ON AGL.listener_id = LIP.listener_id ORDER BY AGL.dns_name,RCS.replica_server_name,LIP.state DESC, ARS.role_desc"}, {"wait_stats_count", "SELECT [wait_type],[waiting_tasks_count] AS [wait_count] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN (" " N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', " " N'CHKPT', N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', " " N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', " " N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',N'ONDEMAND_TASK_QUEUE',N'PREEMPTIVE_XE_GETTARGETSTATE',N'PWAIT_ALL_COMPONENTS_INITIALIZED', " " N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',N'QDS_SHUTDOWN_QUEUE', " " N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', " " N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', " " N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_RECOVERY',N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', " " N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ORDER BY waiting_tasks_count DESC"}, {"wait_stats_time", "SELECT [wait_type],[wait_time_ms] AS [wait_time] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN (" " N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', " " N'CHKPT', N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', " " N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', " " N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',N'ONDEMAND_TASK_QUEUE',N'PREEMPTIVE_XE_GETTARGETSTATE',N'PWAIT_ALL_COMPONENTS_INITIALIZED', " " N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',N'QDS_SHUTDOWN_QUEUE', " " N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', " " N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', " " N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_RECOVERY',N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', " " N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [wait_time_ms] > 0 ORDER BY wait_time_ms DESC"}, {NULL,NULL} }; QueryVers MSSql80[] = { {"generic_query", "select getdate()"}, {"database_size", "sp_MSforeachdb ' " "declare @dbsize_pg bigint " "declare @logsize_pg bigint " "declare @reserved_pg bigint " "declare @used_pg bigint " "declare @data_pg bigint " "select @dbsize_pg = sum(size) from [?].dbo.sysfiles where (status & 64 = 0) " "select @logsize_pg = sum(size) from [?].dbo.sysfiles where (status & 64 <> 0) " "select @reserved_pg = sum(reserved) from [?].dbo.sysindexes where indid in (0, 1, 255) " "select @used_pg = sum(used) from [?].dbo.sysindexes where indid in (0, 1, 255) " "select @data_pg = sum(dpages) from [?].dbo.sysindexes where indid < 2 " "select @data_pg = @data_pg + isnull(sum(used),0) from [?].dbo.sysindexes where indid = 255 " "select object = ''?'', " " database_size = ((convert(dec(15,2),@dbsize_pg) + convert(dec(15,2),@logsize_pg)) * 8192), " " free_space = ((convert(dec(15,2),(@dbsize_pg+@logsize_pg)) - convert(dec(15,2),@reserved_pg)) * 8192), " " reserved = (@reserved_pg * 8192), " " data = (@data_pg * 8192), " " index_size = ((@used_pg - @data_pg) * 8192), " " unused = ((@reserved_pg - @used_pg) * 8192)'"}, {"buf_cachehit_ratio", "select convert(dec(12,0),a.cntr_value) as page_lookups, convert(dec(12,0),b.cntr_value) as page_reads, convert(dec(12,0),c.cntr_value) as page_writes from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b, master.dbo.sysperfinfo c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec'"}, {"lock_requests", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Requests/sec'"}, {"database_state", "select rtrim(name) object, state = " " CASE" " when status & 32 = 32 then 6" " when status & 64 = 64 then 3" " when status & 262144 = 262144 then 3" " when status & 128 = 128 then 2" " when status & 256 = 256 then 4" " when status & 512 = 512 then 6" " when status & 2048 = 2048 then 5" " when status & 4096 = 4096 then 5" " when status & 32768 = 32768 then 5" " else 0" " END , state_desc =" " CASE" " when status & 32 = 32 then 'OFFLINE'" " when status & 64 = 64 then 'RECOVERY_PENDING'" " when status & 262144 = 262144 then 'RECOVERY_PENDING'" " when status & 128 = 128 then 'RECOVERING'" " when status & 256 = 256 then 'SUSPECT'" " when status & 512 = 512 then 'OFFLINE'" " when status & 2048 = 2048 then 'EMERGENCY'" " when status & 4096 = 4096 then 'EMERGENCY'" " when status & 32768 = 32768 then 'EMERGENCY'" " else 'ONLINE'" " END " "from master..sysdatabases"}, {"log_cachehit_ratio", "select rtrim(a.instance_name) as object, convert(dec(12,0),a.cntr_value) as cache_hits, convert(dec(12,0),b.cntr_value) as cache_base from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b " "where a.counter_name = 'Log Cache Hit Ratio' and b.counter_name = 'Log Cache Hit Ratio Base' and a.instance_name = b.instance_name"}, {"login_count", "select convert(dec(12,0),cntr_value) connections from master.dbo.sysperfinfo where counter_name ='User Connections'"}, {"active_users", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) active_connections from master.dbo.sysperfinfo where counter_name ='Active Transactions'"}, {"deadlocks", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Number of Deadlocks/sec'"}, {"lock_timeouts", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Timeouts/sec'"}, {"lock_waits", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Waits/sec'"}, {"transactions", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Transactions/sec' order by 1"}, {"log_flush_waits", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Log Flush Waits/sec'"}, {"page_reads", "select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"}, {"page_writes", "select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"}, {"latch_waits", "select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Latch Waits/sec'"}, {"full_scans", "select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Full Scans/sec'"}, {"database_count", "select count(*) count from master.dbo.sysdatabases"}, {"server_startup", "select datediff(dd, crdate, getdate()) count, crdate date from master.dbo.sysdatabases where name ='tempdb'"}, {"log_file_growths", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count from master.dbo.sysperfinfo where counter_name = 'Log Growths'"}, {"log_file_shrinks", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count from master.dbo.sysperfinfo where counter_name = 'Log Shrinks'"}, {"lock_memory", "select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Lock Memory (KB)'"}, {"connection_memory", "select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Connection Memory (KB)'"}, {"optimizer_memory", "select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Optimizer Memory (KB)'"}, {"sqlcache_memory", "select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'SQL Cache Memory (KB)'"}, {"total_memory", "select (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Total Server Memory (KB)') count, (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Target Server Memory(KB)') target"}, {"locks_used", "select (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name = 'Lock Blocks') lock_blocks, (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name = 'Lock Owner Blocks') owner_blocks, " "(select convert(dec(12,0),high) from master.dbo.spt_values where name = 'locks') maximum, ISNULL((select convert(dec(12,0),value) from master.dbo.syscurconfigs where config=106),0) value_in_use"}, {"workspace_memory", "select (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Granted Workspace Memory (KB)') granted, (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Maximum Workspace Memory (KB)') maximum"}, {"average_waittime", "select rtrim(a.instance_name) object, convert(dec(12,0),a.cntr_value) lock_waits, convert(dec(12,0),b.cntr_value) wait_time from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b where a.counter_name = 'Lock Waits/sec' and b.counter_name = 'Lock Wait Time (ms)' and a.instance_name = b.instance_name"}, {"server_cpu", "exec sqlsrv_cpu"}, {"server_io", "select @@IO_BUSY io_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time"}, {"free_connections", "select (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name ='User Connections') connections, (select convert(dec(12,0),value) from master.dbo.syscurconfigs where config=103) config_connections, convert(dec(12,0),@@MAX_CONNECTIONS) max_connections"}, {"user_cpu", "exec sqlusr_cpu"}, {"user_waits", "SET NOCOUNT ON declare @waittime int " "declare @blockerid smallint " "declare @blockerhid nchar(8) " "declare @blockeruid varchar(128) " "declare @blockedid smallint " "declare @blockedhid nchar(8) " "declare @blockeduid varchar(128) " "declare @dbname sysname " "declare @sql_text varchar(255) " "create table #sqltext (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255)) " "create table #blocker (waittime int, sid_blocker smallint, hid_blocker nchar(8), uid_blocker nchar(128), sid_blocked smallint, hid_blocked nchar(8), uid_blocked nchar(128), dbname sysname, sqltext varchar(255)) " "declare user_spid cursor for select a.waittime, a.blocked, b.hostprocess, rtrim(b.loginame), a.spid, a.hostprocess, rtrim(a.loginame), db_name(a.dbid) from master.dbo.sysprocesses a, master.dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid " "open user_spid " "fetch next from user_spid into @waittime,@blockerid, @blockerhid, @blockeruid, @blockedid, @blockedhid, @blockeduid, @dbname WHILE @@FETCH_STATUS = 0 " "BEGIN " "insert into #sqltext EXEC ('dbcc inputbuffer('+@blockedid+') with tableresults, NO_INFOMSGS') " "select @sql_text = EventInfo from #sqltext " "insert into #blocker values (@waittime,@blockerid,@blockerhid, @blockeruid, @blockedid,@blockedhid, @blockeduid, @dbname, @sql_text) " "delete from #sqltext " "fetch next from user_spid into @waittime, @blockerid, @blockerhid, @blockeruid, @blockedid, @blockedhid, @blockeduid, @dbname " "END " "close user_spid " "deallocate user_spid " "select convert(dec(20,3),waittime)/1000 waittime, convert(varchar(5),sid_blocker) sid_blocker, hid_blocker, rtrim(uid_blocker) uid_blocker," "convert(varchar(5),sid_blocked) sid_blocked, hid_blocked, rtrim(uid_blocked) uid_blocked, rtrim(dbname) db_name, rtrim(ltrim(sqltext)) sql_blocked from #blocker " "drop table #sqltext " "drop table #blocker"}, {"locked_users", "select count(*) count from master.dbo.sysprocesses a, master.dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid"}, {"blocked_users", "select count(*) count from dbo.sysprocesses a, dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid and a.status != 'suspended'"}, {"av_fragmentation", "select 'dummy' dummy"}, {"logic_fragment", "SET NOCOUNT ON declare @tablename VARCHAR (128) declare @dbname sysname declare @stmnt VARCHAR (512) declare @stmnt2 VARCHAR (512) select @stmnt2 = '''DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS''' " "CREATE TABLE #fraglist (objectname CHAR (255), objectid INT, indexname CHAR (255), indexid INT, lvl INT NULL, countpages INT NULL, countrows INT NULL, minrecsize INT NULL, maxrecsize INT NULL, avgrecsize INT NULL, forreccount INT NULL, extents INT NULL, extentswitches INT NULL, avgfreebytes INT NULL, avgpagedensity INT NULL, scandensity DECIMAL, bestcount INT, actualcount INT, logicalfrag DECIMAL, extentfrag DECIMAL NULL) " "SET LOCK_TIMEOUT %ld DECLARE dbases CURSOR FOR SELECT name from master..sysdatabases OPEN dbases FETCH NEXT from dbases into @dbname WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt = 'use ['+ @dbname +'] INSERT INTO #fraglist EXEC ('+ @stmnt2 +')' EXEC (@stmnt) select @dbname db_name, rtrim(objectname) object, rtrim(indexname) indexname, scandensity, logicalfrag, indid from #fraglist f, sysindexes i WHERE f.objectid = i.id and (i.indid = 0 or (i.indid = 1 and i.name = f.indexname) or (i.indid = 255 and i.name = f.indexname)) " "delete from #fraglist FETCH NEXT from dbases into @dbname END CLOSE dbases DEALLOCATE dbases DROP TABLE #fraglist"}, {"free_space", "sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))*8192 from [?].dbo.sysfiles where (status & 64 = 0)) allocated, " "ISNULL((select convert(bigint,sum(maxsize))*8192 from [?].dbo.sysfiles where ((status & 64 = 0) and growth > 0) having (select count(*) from [?].dbo.sysfiles where maxsize = -1 and status & 64 = 0 and growth > 0) = 0),-1) max, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),size)))*8192 from [?].. sysfiles where (status & 64 = 0) and growth = 0),0) max2, " "(select convert(bigint,sum(convert(dec(18,0),used)))* 8192 from [?].. sysindexes where indid in (0, 1, 255)) used, " "(select count(*) from [?].. sysfiles where maxsize = -1 and status & 64 = 0 and growth > 0) unlimited ' "}, {"fg_free_space", "sp_MSforeachdb 'select ''?'' db_name, groupname fg_name, (select convert(bigint,sum(size))*8192 from [?].dbo.sysfiles f where f.groupid = g.groupid and (status & 64 = 0)) allocated, " "ISNULL((select convert(bigint,sum(maxsize))*8192 from [?].dbo.sysfiles f where (growth > 0 and f.groupid = g.groupid and (status & 64 = 0)) having ((select count(*) from [?].. sysfiles f where maxsize = -1 and growth > 0 and (status & 64 = 0) and f.groupid = g.groupid) = 0)),-1) max, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),size)))*8192 from [?].. sysfiles f where growth = 0 and f.groupid = g.groupid and (status & 64 = 0)),0) max2, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),used)))* 8192 from [?].. sysindexes f where indid in (0, 1, 255) and f.groupid = g.groupid),0) used, " "(select count(*) from [?].. sysfiles f where maxsize = -1 and growth > 0 and (status & 64 = 0) and f.groupid = g.groupid) unlimited from [?].dbo.sysfilegroups g' "}, {"table_space", "sp_MSforeachdb 'use [?] select ''?'' db_name, USER_NAME(o.uid) schema_name, o.name table_name, ((select IsNULL(sum(dpages),0)*8192 from " "[?].dbo.sysindexes i where indid < 2 and i.id=o.id) + (select IsNULL(sum(used),0)*8192 from [?].dbo.sysindexes i where indid = 255 and " "i.id=o.id)) data_size, ((select IsNULL(sum(used),0)*8192 from [?].dbo.sysindexes i where indid in (0, 1, 255) and i.id=o.id) - " "((select IsNULL(sum(dpages),0)*8192 from [?].dbo.sysindexes i where indid < 2 and i.id=o.id) + (select IsNULL(sum(used),0)*8192 from " "[?].dbo.sysindexes i where indid = 255 and i.id=o.id))) index_size, (select IsNULL(sum(reserved),0)*8192 from [?].dbo.sysindexes i " "where indid in (0, 1, 255) and i.id=o.id) table_size from [?].dbo.sysobjects o where o.type = ''U'' use master'"}, {"logfile_usage", "SET NOCOUNT ON declare @db sysname declare @stmnt1 VARCHAR (1500) create table #logsize ( dbase varchar(255), logsize float, logused float, status bigint ) " "insert #logsize exec( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS' ) DECLARE dbases CURSOR FOR SELECT name from (select [dbid],name,[status] " "from master..sysdatabases where (status & 32 != 32) AND (status & 64 != 64) AND (status & 128 != 128)) tbl OPEN dbases FETCH NEXT from dbases into @db " "WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt1 = 'select dbase db_name, +'+ " "'+(select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0)) allocated,+'+ " "'+ISNULL((select CONVERT(bigint,sum(maxsize))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth > 0 having (select count(*) from ['+ @db+'].dbo.sysfiles where maxsize = -1 and (status & 64 <> 0) and growth > 0) = 0),-1) max,+'+ " "'+ISNULL((select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth = 0),0) max2,+'+ " "'+(select count(*) from ['+@db+'].dbo.sysfiles where maxsize = -1 and status & 64 <> 0 and growth > 0) unlimited,+'+ " "'+CONVERT(bigint,logsize)*1024 logsize, logused as used from #logsize where dbase ='''+ @db +''' ' " "EXEC (@stmnt1) FETCH NEXT from dbases into @db END CLOSE dbases DEALLOCATE dbases drop table #logsize"}, {"backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D' GROUP BY B.name ORDER BY B.name"}, {"transaction_backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM ( SELECT * from master.dbo.sysdatabases C where DATABASEPROPERTYEX(C.name, 'Recovery') != 'SIMPLE' ) B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'L' GROUP BY B.name ORDER BY B.name"}, {"differential_backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'I' GROUP BY B.name ORDER BY B.name"}, {"long_queries", "select 'dummy' dummy"}, {"long_jobs", "select 'dummy' dummy"}, {"active_connection_ratio", "select (select CASE value WHEN 0 THEN 32767 ELSE value END from dbo.sysconfigures where comment like '%user connections%') as total, " " (select count(dbid) from dbo.sysprocesses where hostname <> '' and dbid > 0) as active"}, {"logfile_size", "SET NOCOUNT ON declare @db sysname declare @stmnt1 VARCHAR (2000) create table #logsize ( dbase varchar(255), logsize float, logused float, status bigint ) " "insert #logsize exec( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS' ) DECLARE dbases CURSOR FOR SELECT name from (select [dbid],name,[status] " "from master..sysdatabases where (status & 32 != 32) AND (status & 64 != 64) AND (status & 128 != 128)) tbl OPEN dbases FETCH NEXT from dbases into @db " "WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt1 = 'select dbase db_name, +'+ " "'+(select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0)) allocated,+'+ " "'+ISNULL((select CONVERT(bigint,sum(maxsize))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth > 0 having (select count(*) from ['+ @db+'].dbo.sysfiles where maxsize = -1 and (status & 64 <> 0) and growth > 0) = 0),-1) max,+'+ " "'+ISNULL((select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth = 0),0) max2,+'+ " "'+(select count(*) from ['+@db+'].dbo.sysfiles where maxsize = -1 and status & 64 <> 0 and growth > 0) unlimited,+'+ " "'+CONVERT(bigint,logsize)*1024 logsize, logused as used from #logsize where dbase ='''+ @db +''' ' " "EXEC (@stmnt1) FETCH NEXT from dbases into @db END CLOSE dbases DEALLOCATE dbases drop table #logsize"}, {"mirror_state", "select 'dummy' dummy"}, {"mirror_witness_server", "select 'dummy' dummy"}, {"mirror_sqlinstance", "select 'dummy' dummy"}, {"suspect_pages", "select 'dummy' dummy"}, {"agent_job_failure", "select h.job_id as job_id, j.name as job_name, c.name category_name," "dateadd(hh,run_time/10000,dateadd(mi,run_time%10000/100,dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) as rundate, " "datediff(minute, dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))), getdate()) as elapsed_time " "from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id " "inner join msdb.dbo.syscategories c on j.category_id = c.category_id " "where c.category_class = 1 and h.run_status <> 1 and h.step_id = 0 and " "dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) > dateadd(ss,-60*60*36,getdate()) " "order by elapsed_time"}, {"ls_primary_status", "select 'dummy' dummy"}, {"ls_secondary_status", "select 'dummy' dummy"}, {"ls_primary_time_since_last_backup","select 'dummy' dummy"}, {"ls_secondary_time_since_last_copy","select 'dummy' dummy"}, {"ls_secondary_time_since_last_restore","select 'dummy' dummy"}, {"ls_secondary_last_restored_latency", "select 'dummy' dummy"}, {"fg_freeSpace_with_avail_disk", " SET NOCOUNT ON " " DECLARE @database_id int " " DECLARE @db_name sysname " " DECLARE @sql_string nvarchar(2000) " " DECLARE @adriveletter nchar(2),@agroupname sysname,@adbname nvarchar(256),@adbid smallint,@agroupid smallint,@aspace_available int,@aspace_used int, " " @file_size int " " DECLARE @maxavailable bigint,@old_spaceused bigint,@new_spaceused bigint,@old_spaceav bigint,@new_spaceav bigint, @old_file_size bigint,@new_file_size bigint " " DECLARE @autogrow int,@maxsize int,@space_left int,@fileid int,@groupid int,@driveletter nvarchar(1),@mbfree int,@spaceused int,@spaceavialable int, " " @dbid sysname,@canttest smallint " " DECLARE @ver nvarchar(128) " " CREATE TABLE [#temptab]([driveletter] nvarchar(2), " " [groupname] [sysname] NULL, " " [dbname] [nvarchar](128) NULL, " " [dbid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [space_used] [int] NULL, " " [space_available] [int] NULL, " " [file_size] [int] NULL) ON [PRIMARY] " " create table #freespace( Drive nvarchar(1),MB_Free int) " " insert into #freespace exec [master].[dbo].[xp_fixeddrives] " " CREATE TABLE [#space_tab]([groupname] [sysname] NULL,[dbname] [nvarchar] (128)NULL, " " [dbid] [smallint] NULL, " " [fileid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [filetype] [varchar](9) NOT NULL, " " [name] [sysname] NOT NULL, " " [filename] [nvarchar](260) NOT NULL, " " [file_size] [int] NULL, " " [space_used] [int] NULL, " " [space_left] [int] NULL, " " [autogrow] [int] NOT NULL, " " [maxsizeinmb] [int] NULL, " " [maxsize] [int] NOT NULL, " " [space_available] [int] NOT NULL) ON [PRIMARY] " " SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar) " " SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > 0 " " group by dbid,name " " ORDER BY dbid " " end " " else select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state when 0 then 0 else 1 end " " from master.sys.databases " " WHERE database_id > 0 " " group by database_id,name,state " " ORDER BY database_id " " WHILE @db_name IS NOT NULL " " BEGIN " " if @canttest = 0 " " begin " " SET @sql_string = 'USE ' + QUOTENAME(@db_name) + CHAR(10) " " SET @sql_string = @sql_string + 'Select isnull(g.groupname,''TRAN LOG''),DB_NAME(),db_id() as dbid,fileid,s.groupid,case when s.groupid = 0 " " then ''log file'' else ''data file'' end as filetype, name ,filename, " " [file_size] = convert(int,round((s.size*1.000)/128.000,0)), " " [space_used] = convert(int,round(fileproperty(s.name,''SpaceUsed'')/128.000,0)), " " [space_left] = convert(int,round((s.size-fileproperty(s.name,''SpaceUsed''))/128.000,0)), " " case when growth > 0 then 1 else 0 end as autogrow, " " case when maxsize = -1 then -1 else convert(decimal,maxsize)*8/1024 end as maxsizeinmb" " ,maxsize,0 as space_available from dbo.sysfiles s " " left outer join dbo.sysfilegroups g " " on s.groupid = g.groupid' " " INSERT INTO [#space_tab] " " EXEC sp_executesql @sql_string " " end " " SET @db_name = NULL " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > @database_id " " group by dbid,name " " ORDER BY dbid " " end " " else " " select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state " " when 0 then 0 else 1 end " " from master.sys.databases d " " WHERE database_id > @database_id " " and database_id not in (select database_id from sys.master_files where is_sparse = 1) " " group by database_id,name,state " " ORDER BY database_id " " END " " DECLARE freespace_cur Cursor " " FOR select autogrow,maxsizeinmb,space_left,fileid,groupid,substring(filename,1,1),space_used,dbid from #space_tab " " Open freespace_cur " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if @autogrow = 1 " " begin " " select @mbfree = (select MB_Free from #freespace where Drive = @driveletter) " " if @maxsize <> -1 " " begin " " if @maxsize > @mbfree " " select @maxsize = @mbfree " " else " " select @maxsize = @maxsize - @spaceused " " end " " else " " begin " " select @maxsize = @mbfree + @space_left " " end " " update [#space_tab] " " set space_available = @maxsize " " where fileid = @fileid and groupid = @groupid and dbid = @dbid " " end " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " END " " CLOSE freespace_cur " " DEALLOCATE freespace_cur " " drop table #freespace " " declare autogrow_cur cursor for " " select substring(filename,1,1),groupname,dbname,dbid,groupid,space_available,space_used,file_size auto_ from [#space_tab] " " where autogrow = 1 " " order by dbid,groupid,1 " " Open autogrow_cur " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if not exists (select * from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " begin " " insert into #temptab values(@adriveletter,@agroupname,@adbname,@adbid,@agroupid,@aspace_used,@aspace_available,@file_size) " " end " " else " " begin " " select @old_spaceused = (select space_used from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceused = @old_spaceused + @aspace_used " " select @old_spaceav = (select space_available from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceav = @old_spaceav+ @aspace_available " " select @old_file_size = (select file_size from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_file_size = @old_file_size + @file_size " " update #temptab set space_used = @new_spaceused ,space_available= @new_spaceav, file_size=@new_file_size " " where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid " " end " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " end " " close autogrow_cur " " deallocate autogrow_cur " " select groupname,dbname,dbid,groupid,sum(space_used) as space_used,sum(space_available) as space_available,sum(file_size) as file_size " " into #autogrowfiles from #temptab " " group by groupname,dbname,groupid,dbid " " order by dbid,groupid " " drop table #temptab " " select groupname,dbname,dbid,groupid,sum(space_left) as space_left,SUM(space_used) as space_used,sum(file_size) as file_size " " into #fixedfiles " " from [#space_tab] " " where autogrow = 0 " " group by dbid,groupid,dbname,groupname " " select isnull(a.dbname,b.dbname) as db_name,isnull(a.groupname,b.groupname) as fg_name,convert(decimal,(isnull(a.space_available,0)+isnull(b.space_left,0)))*1024*1024 as total_space_available, " " convert(decimal,(isnull(a.space_used,0)+isnull(b.space_used,0)))*1024*1024 as used, " " convert(decimal,(isnull(a.file_size,0)+isnull(b.file_size,0)))*1024*1024 as allocated, " " convert(decimal,isnull(a.space_available,0))*1024*1024 as autogrowavailable,convert(decimal,isnull(b.space_left,0))*1024*1024 as fixedfileavailable into #results_tab from " " #autogrowfiles a " " full outer join " " #fixedfiles b " " on a.dbid = b.dbid and a.groupid = b.groupid " " order by 1,2 " " select db_name,fg_name,used,allocated, " " 100-(convert (decimal,used)+0.001) / ((convert (decimal,used)+0.001)+(convert (decimal,total_space_available)+0.001))*100 " " as 'free_pct' " " from #results_tab " " drop table #results_tab " " drop table #space_tab " " drop table #autogrowfiles " " drop table #fixedfiles "}, {NULL,NULL} }; QueryVers MSSql80Create[] = { {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {"server_cpu", "CREATE PROCEDURE sqlsrv_cpu AS SET NOCOUNT ON CREATE TABLE #msver (index_val INT, name VARCHAR(50), internal_value INT, char_value VARCHAR(255)) INSERT #msver EXEC master.dbo.xp_msver ProcessorCount select @@CPU_BUSY cpu_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time, (select internal_value from #msver) cpu_count drop table #msver"}, {NULL,NULL}, {NULL,NULL}, {"user_cpu", "CREATE PROCEDURE sqlusr_cpu AS SET NOCOUNT ON CREATE TABLE #msver (index_val INT, name VARCHAR(50), internal_value INT, char_value VARCHAR(255))INSERT #msver EXEC master.dbo.xp_msver ProcessorCount select convert(varchar(10),spid) spid, cpu cpu_busy, rtrim(loginame) loginname, rtrim(hostprocess) hostid, ISNULL(rtrim(program_name),' ') programname, datediff(ss, '1970-01-01 00:00:00', getdate()) time, (select internal_value from #msver) cpu_count from master.dbo.sysprocesses where hostname <> '' and program_name <> '' order by 2 desc drop table #msver"}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL} }; QueryVers MSSql80Drop[] = { {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {"server_cpu", "IF object_id('sqlsrv_cpu', 'P') IS NOT NULL DROP PROCEDURE sqlsrv_cpu"}, {NULL,NULL}, {NULL,NULL}, {"user_cpu", "IF object_id('sqlusr_cpu', 'P') IS NOT NULL DROP PROCEDURE sqlusr_cpu"}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL} }; QueryVers MSSql90NoLock[] = { {"generic_query", "select getdate()"}, {"database_size", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " "declare " " @dbsize bigint," " @logsize bigint," " @reservedpages bigint," " @usedpages bigint," " @pages bigint" " select @dbsize = sum(convert(bigint,case when type <> 1 then size else 0 end)) " " , @logsize = sum(convert(bigint,case when type = 1 then size else 0 end)) " " from [?].sys.database_files " " select @reservedpages = sum(a.total_pages), " " @usedpages = sum(a.used_pages), " " @pages = sum( " " CASE " " When it.internal_type IN (202,204) Then 0 " " When a.type <> 1 Then a.used_pages " " When p.index_id < 2 Then a.data_pages " " Else 0 " " END " " ) " " from [?].sys.partitions p join [?].sys.allocation_units a on p.partition_id = a.container_id " " left join sys.internal_tables it on p.object_id = it.object_id " " select " " object = ''?'', " " database_size = ((convert(dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192), " " free_space = case when @dbsize >= @reservedpages then " " ((convert(dec (15,2),@dbsize) - convert(dec (15,2),@reservedpages)) * 8192) " " else 0 " " end, " " reserved = (@reservedpages * 8192), " " data = (@pages * 8192), " " index_size = ((@usedpages - @pages) * 8192), " " unused = ((@reservedpages - @usedpages) * 8192)' "}, {"buf_cachehit_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.cntr_value as page_lookups, b.cntr_value as page_reads, c.cntr_value as page_writes from master.sys.dm_os_performance_counters a, master.sys.dm_os_performance_counters b, master.sys.dm_os_performance_counters c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec' " " and LTRIM(RTRIM(a.object_name)) LIKE '%:Buffer Manager' and LTRIM(RTRIM(b.object_name)) LIKE '%:Buffer Manager' and LTRIM(RTRIM(c.object_name)) LIKE '%:Buffer Manager'"}, {"lock_requests", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time " "from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Requests/sec'"}, {"database_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(name) object, state, rtrim(state_desc) state_desc from master.sys.databases"}, {"log_cachehit_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select RTRIM(a.instance_name) as object,a.cntr_value cache_hits,b.cntr_value cache_base from master.sys.dm_os_performance_counters a, master.sys.dm_os_performance_counters b " "WHERE LTRIM(RTRIM(a.object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(b.object_name)) LIKE '%:Databases' AND a.instance_name = b.instance_name and " "replace(lower(a.counter_name),char(32),'') LIKE 'logcachehitratio' and replace(lower(b.counter_name),char(32),'') LIKE 'logcachehitratiobase'"}, {"login_count", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select cntr_value connections from master.sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:General Statistics' AND LTRIM(RTRIM(counter_name)) = 'User Connections'"}, {"active_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object ,cntr_value active_connections from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Active Transactions'"}, {"deadlocks", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object ,cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Number of Deadlocks/sec'"}, {"lock_timeouts", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Timeouts/sec'"}, {"lock_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(counter_name)) = 'Lock Waits/sec'"}, {"transactions", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Transactions/sec' order by 1"}, {"log_flush_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters a " "WHERE LTRIM(RTRIM(a.object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(a.counter_name)) = 'Log Flush Waits/sec'"}, {"page_reads", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Buffer Manager' AND LTRIM(RTRIM(counter_name)) = 'Page reads/sec'"}, {"page_writes", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Buffer Manager' AND LTRIM(RTRIM(counter_name)) = 'Page writes/sec'"}, {"latch_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Latches' AND LTRIM(RTRIM(counter_name)) = 'Latch Waits/sec'"}, {"full_scans", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select cntr_value count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.sys.dm_os_performance_counters " "WHERE LTRIM(RTRIM(object_name)) LIKE '%:Access Methods' AND LTRIM(RTRIM(counter_name)) = 'Full Scans/sec'"}, {"database_count", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count from master.sys.sysdatabases"}, {"server_startup", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select datediff(dd, crdate, getdate()) count, crdate date from master.sys.sysdatabases where name ='tempdb'"}, {"log_file_growths", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Databases' AND LTRIM(RTRIM(counter_name)) = 'Log Growths'"}, {"log_file_shrinks", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, cntr_value count from master.dbo.sysperfinfo where counter_name = 'Log Shrinks'"}, {"lock_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Memory (KB)'"}, {"connection_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Connection Memory (KB)'"}, {"optimizer_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Optimizer Memory (KB)'"}, {"sqlcache_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (cntr_value*1024) count from master.sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'SQL Cache Memory (KB)'"}, {"total_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select (cntr_value*1024) from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Total Server Memory (KB)') count, " "(select (cntr_value*1024) from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Target Server Memory (KB)') target"}, {"locks_used", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Blocks') lock_blocks, " "(select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Lock Owner Blocks') owner_blocks, " " convert(dec(15),maximum) maximum, convert(dec(15),value_in_use) value_in_use from sys.configurations where configuration_id = 106"}, {"workspace_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select (cntr_value*1024) from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Granted Workspace Memory (KB)') granted," "(select (cntr_value*1024) value from sys.dm_os_performance_counters WHERE LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager' AND LTRIM(RTRIM(counter_name)) = 'Maximum Workspace Memory (KB)') maximum"}, {"average_waittime", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(a.instance_name) object, a.cntr_value lock_waits, b.cntr_value wait_time from sys.dm_os_performance_counters a, sys.dm_os_performance_counters b WHERE " "LTRIM(RTRIM(a.object_name)) LIKE '%:Locks' AND LTRIM(RTRIM(a.counter_name)) = 'Lock Waits/sec' and LTRIM(RTRIM(b.object_name)) LIKE '%:Locks' AND " "LTRIM(RTRIM(b.counter_name)) = 'Lock Wait Time (ms)' and a.instance_name = b.instance_name"}, {"server_cpu", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select @@CPU_BUSY cpu_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time ,(select cpu_count from sys.dm_os_sys_info) cpu_count"}, {"server_io", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select @@IO_BUSY io_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time"}, {"free_connections", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select cntr_value from sys.dm_os_performance_counters where LTRIM(RTRIM(object_name)) LIKE '%:General Statistics' AND LTRIM(RTRIM(counter_name)) = 'User Connections') connections, " "(select convert(dec(15),value_in_use) from sys.configurations where configuration_id = 103) config_connections, convert(dec(12,0),@@MAX_CONNECTIONS) max_connections"}, {"user_cpu", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(varchar(10),session_id) spid, cpu_time cpu_busy, rtrim(login_name) loginname, rtrim(host_process_id) hostid, (ISNULL(rtrim(program_name),' ')) programname, " "datediff(ss, '1970-01-01 00:00:00', getdate()) time, (select cpu_count from sys.dm_os_sys_info) cpu_count from sys.dm_exec_sessions where host_name <> '' and program_name <> '' order by 2 desc"}, {"user_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT @@ROWCOUNT as bcount,db_name(dtra.database_id) db_name, (convert(dec(20,3),wait_duration_ms))/1000 waittime, task.blocking_session_id sid_blocker, isNull(bsess.host_process_id,-1) hid_blocker, " "isNull(bsess.login_name,'n/a') uid_blocker, task.session_id sid_blocked, sess.host_process_id hid_blocked, sess.login_name uid_blocked, convert(varchar(255),isNull(SUBSTRING (sql.text,1,255),'n/a')) as sql_blocked " "FROM sys.dm_os_waiting_tasks AS task, sys.dm_tran_database_transactions as dtra, sys.dm_exec_sessions as sess, sys.dm_exec_sessions as bsess, sys.dm_exec_requests as t2 CROSS APPLY sys.dm_exec_sql_text(t2.sql_handle) AS sql " "where t2.session_id = task.session_id and sess.session_id = task.session_id and bsess.session_id = task.blocking_session_id and dtra.transaction_id = t2.transaction_id and IsNull(task.blocking_session_id,0) <> 0"}, {"locked_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count FROM sys.dm_os_waiting_tasks where blocking_session_id <> 0"}, {"blocked_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count from sys.sysprocesses a, sys.sysprocesses b where a.blocked != 0 and a.blocked = b.spid and a.status != 'suspended'"}, {"av_fragmentation", "sp_MSforeachdb 'SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT distinct ''?'' db_name, OBJECT_NAME(i.object_id) AS tablename, isNull(i.name,'''') AS indexname, phystat.avg_fragmentation_in_percent as fragmentation, alloc_unit_type_desc as alloc_unit " "FROM sys.dm_db_index_physical_stats(db_id(''?''), NULL, NULL, NULL, ''LIMITED'') phystat JOIN [?].sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id where OBJECT_NAME(i.object_id) is not null'"}, {"logic_fragment", "select 'dummy' dummy"}, {"free_space", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated, " "ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where ((f.type <> 1) and f.growth > 0 and size > 0 ) having ((select count(*) from [?].sys.database_files where max_size = -1 and type <> 1 and growth > 0) = 0)),-1) max, " "ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files where type <> 1 and growth = 0 and size > 0),-1) max2, " "ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a),-1) used, " "(select count(*) from [?].sys.database_files cg where max_size = -1 and type <> 1 and growth > 0) unlimited' "}, {"fg_free_space", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, g.name fg_name, ISNULL((select convert(bigint,sum(size))* 8192 from [?].sys.database_files f where f.data_space_id = g.data_space_id),-1) allocated, " "ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where (f.growth > 0 and f.data_space_id = g.data_space_id and type <> 1) having ((select count(*) from [?].sys.database_files f where max_size = -1 and growth > 0 and f.data_space_id = g.data_space_id and type <> 1) = 0 )),-1) max, " "ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files f where growth = 0 and f.data_space_id = g.data_space_id and type <> 1),-1) max2, " "ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a where a.data_space_id = g.data_space_id),0) used, " "(select count(*) from [?].sys.database_files f where max_size = -1 and growth > 0 and f.data_space_id = g.data_space_id and type <> 1) unlimited from [?].sys.filegroups g' "}, {"table_space", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED use [?] select ''?'' db_name, SCHEMA_NAME(o.schema_id) schema_name, o.name table_name, " "((SELECT IsNULL(SUM(reserved_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id) + " "(SELECT IsNULL(SUM(reserved_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats p, [?].sys.internal_tables it WHERE " "it.parent_id = o.object_id AND it.internal_type IN (202,204) AND p.object_id = it.object_id)) table_size, " "(((SELECT IsNULL(SUM(used_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id ) + " "(SELECT IsNULL(SUM(used_page_count),0)*8192 FROM [?].sys.dm_db_partition_stats p, [?].sys.internal_tables it WHERE " "it.parent_id = o.object_id AND it.internal_type IN (202,204) AND p.object_id = it.object_id)) - (SELECT SUM(CASE WHEN (index_id < 2) " "THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)*8192 ELSE (lob_used_page_count + " "row_overflow_used_page_count)*8192 END) FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id)) index_size, (SELECT " "SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)*8192 ELSE " "(lob_used_page_count + row_overflow_used_page_count)*8192 END) FROM [?].sys.dm_db_partition_stats WHERE object_id = o.object_id) " "data_size from [?].sys.objects o where type=''U'' use master'"}, {"logfile_usage", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, (select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1) allocated, ISNULL((select sum(convert(bigint,size))*8 from [?].sys.database_files f where f.state_desc != ''RESTORING'' and (f.type = 1) and f.growth = 0),0) max, " "ISNULL((select sum(convert(bigint,max_size))*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1 and growth > 0),0) max2, (select count(*) from [?].sys.database_files cg where state_desc != ''RESTORING'' and max_size = -1 and type = 1 and growth > 0) unlimited, " "(select CASE WHEN (a.cntr_value) > 0 THEN convert(dec(15,3),b.cntr_value)/convert(dec(15,3),a.cntr_value)*100 ELSE 0 END from sys.dm_os_performance_counters a,sys.dm_os_performance_counters b " "where a.counter_name = ''Log File(s) Size (KB)'' and b.counter_name = ''Log File(s) Used Size (KB)'' AND LTRIM(RTRIM(a.object_name)) LIKE ''%:Databases'' AND LTRIM(RTRIM(b.object_name)) LIKE ''%:Databases'' AND a.instance_name = b.instance_name and a.instance_name = ''?'') used, " "(select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1)-8 logsize '"}, {"backup_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT B.name as db_name,ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes ,ISNULL(ABS(DATEDIFF(day, GetDate(), MAX(backup_finish_date))), -1) as days_sincebackup, " "ISNULL(ABS(DATEDIFF(day, GetDate(), MAX(create_date))), -1) as days_sincecreation,MAX(B.state) as db_state,ISNULL(Convert(char(10), MAX(backup_finish_date), 101),'NEVER') as last_backup,ISNULL(MAX(B.source_database_id),0) as is_snapshot " " FROM master.sys.databases B LEFT OUTER JOIN msdb.dbo.backupset A ON (A.database_name = B.name AND A.type = 'D') GROUP BY B.name ORDER BY B.name "}, {"transaction_backup_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM ( SELECT * from master.sys.databases C where C.recovery_model != 3 ) B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'L' GROUP BY B.name ORDER BY B.name"}, {"differential_backup_status", "SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.sys.databases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'I' GROUP BY B.name ORDER BY B.name"}, {"long_queries", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT r.session_id spid, s.host_process_id hostid, s.host_name, s.login_name loginname, s.program_name, (convert(dec(15,3),r.cpu_time))/1000 cpu_time, (convert(dec(15,3),r.total_elapsed_time))/1000 elapsed_time, " "convert(varchar(3000),isNull(SUBSTRING (st.text,1,3000),'n/a')) as sql_text FROM sys.dm_exec_sessions s, sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st " "where s.session_id = r.session_id and r.status = 'running' and s.session_id <> @@SPID"}, {"long_jobs", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select sp.session_id spid, sjs.step_id, sjs.step_name, sj.name jobname, datediff(s,dms.last_request_start_time,getdate()) elapsed_time,sce.name as category_name, " "sp.host_name host_name, sp.login_name loginname, dms.last_request_start_time start_time, sql_details.text sql_text from sys.dm_exec_sessions dms, " "msdb.dbo.sysjobs sj, msdb.dbo.sysjobsteps sjs,msdb.dbo.syscategories sce, " "sys.dm_exec_sessions sp, sys.dm_exec_requests rq outer apply sys.dm_exec_sql_text(rq.sql_handle) sql_details where sp.session_id <> @@SPID and " "dms.session_id = sp.session_id and sp.session_id = rq.session_id " "and STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') = RIGHT(sj.job_id, 17) " "and sp.program_name LIKE 'SQLAgent - TSQL JobStep %' and SUBSTRING(REVERSE(RTRIM(sp.program_name)),2,1) = sjs.step_id " "and sj.job_id = sjs.job_id and sj.category_id = sce.category_id "}, {"active_connection_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select CASE value WHEN 0 THEN 32767 ELSE value END from sys.sysconfigures where comment like '%user connections%') as total," " (select count(dbid) from sys.sysprocesses where hostname <> '' and dbid > 0) as active"}, {"logfile_size", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, (select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1) allocated, ISNULL((select sum(max_size)*8 from [?].sys.database_files f where f.state_desc != ''RESTORING'' and (f.type = 1) and f.growth > 0 and max_size = -1),-1) max, " "ISNULL((select sum(convert(bigint,max_size))*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1 and growth > 0),-1) max2, (select count(*) from [?].sys.database_files cg where state_desc != ''RESTORING'' and max_size = -1 and type = 1 and growth > 0) unlimited, " "(select CASE WHEN (a.cntr_value) > 0 THEN convert(dec(15,3),b.cntr_value)/convert(dec(15,3),a.cntr_value)*100 ELSE 0 END from sys.dm_os_performance_counters a,sys.dm_os_performance_counters b where a.counter_name = ''Log File(s) Size (KB)'' and b.counter_name = ''Log File(s) Used Size (KB)'' AND LTRIM(RTRIM(a.object_name)) LIKE ''%:Databases'' AND LTRIM(RTRIM(b.object_name)) LIKE ''%:Databases'' AND a.instance_name = b.instance_name and a.instance_name = ''?'') used, " "(select sum(size)*8 from [?].sys.database_files where state_desc != ''RESTORING'' and type = 1)-8 logsize '"}, {"mirror_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT rtrim(DB_NAME(database_id)) AS 'object', mirroring_state AS state, rtrim(mirroring_state_desc) AS mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL"}, {"mirror_witness_server","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT rtrim(DB_NAME(database_id)) AS 'object', rtrim(mirroring_witness_name) AS mirroring_witness_name, mirroring_witness_state AS state, rtrim(mirroring_witness_state_desc) AS mirroring_witness_state_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL"}, {"mirror_sqlinstance","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT rtrim(DB_NAME(database_id)) AS 'object', rtrim(mirroring_partner_instance) AS mirroring_partner_instance FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_role = 1"}, {"suspect_pages","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT count (A.database_id) pdcount, B.name as object FROM msdb.dbo.suspect_pages A RIGHT OUTER JOIN master.sys.databases B ON A.database_id = B.database_id AND (event_type = 1 OR event_type = 2 OR event_type = 3) GROUP BY B.name"}, {"agent_job_failure", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select h.job_id as job_id, j.name as job_name, c.name category_name," "dateadd(hh,run_time/10000,dateadd(mi,run_time%10000/100,dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) as rundate, " "datediff(minute, dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))), getdate()) as elapsed_time " "from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id " "inner join msdb.dbo.syscategories c on j.category_id = c.category_id " "where c.category_class = 1 and h.run_status <> 1 and h.step_id = 0 and " "dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) > dateadd(ss,-60*60*36,getdate()) " "order by elapsed_time"}, {"log_shipping_primary_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select primary_server, primary_database, " "case when (datediff(minute, last_backup_date_utc, getutcdate()) > backup_threshold) then 0 else 1 end as primary_status " "from msdb.dbo.log_shipping_monitor_primary with (nolock) order by primary_server, primary_database"}, {"log_shipping_secondary_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select secondary_server, secondary_database, primary_server, primary_database, " "case when ((datediff(minute, last_restored_date_utc, getutcdate()) > restore_threshold) " "or (last_restored_latency > restore_threshold)) then 0 else 1 end as secondary_status " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_primary_time_since_last_backup","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select primary_server, primary_database, last_backup_file, " "datediff(minute, last_backup_date_utc, getutcdate()) as time_since_last_backup " "from msdb.dbo.log_shipping_monitor_primary with (nolock) order by primary_server, primary_database"}, {"log_shipping_secondary_time_since_last_copy","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select secondary_server, secondary_database, primary_server, primary_database, last_copied_file, " "datediff(minute, last_copied_date_utc, getutcdate()) as time_since_last_copy " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_secondary_time_since_last_restore","SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select secondary_server, secondary_database, primary_server, primary_database, last_restored_file, " "datediff(minute, last_restored_date_utc, getutcdate()) as time_since_last_restore " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"log_shipping_secondary_last_restored_latency", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select secondary_server, secondary_database, primary_server, primary_database, last_restored_latency " "from msdb.dbo.log_shipping_monitor_secondary with (nolock) order by primary_server, primary_database "}, {"fg_freeSpace_with_avail_disk", " SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " " DECLARE @database_id int " " DECLARE @db_name sysname " " DECLARE @sql_string nvarchar(2000) " " DECLARE @adriveletter nchar(2),@agroupname sysname,@adbname nvarchar(256),@adbid smallint,@agroupid smallint,@aspace_available int,@aspace_used int, " " @file_size int " " DECLARE @maxavailable bigint,@old_spaceused bigint,@new_spaceused bigint,@old_spaceav bigint,@new_spaceav bigint, @old_file_size bigint,@new_file_size bigint " " DECLARE @autogrow int,@maxsize int,@space_left int,@fileid int,@groupid int,@driveletter nvarchar(1),@mbfree int,@spaceused int,@spaceavialable int, " " @dbid sysname,@canttest smallint " " DECLARE @ver nvarchar(128) " " CREATE TABLE [#temptab]([driveletter] nvarchar(2), " " [groupname] [sysname] NULL, " " [dbname] [nvarchar](128) NULL, " " [dbid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [space_used] [int] NULL, " " [space_available] [int] NULL, " " [file_size] [int] NULL) ON [PRIMARY] " " create table #freespace( Drive nvarchar(1),MB_Free int) " " insert into #freespace exec [master].[dbo].[xp_fixeddrives] " " CREATE TABLE [#space_tab]([groupname] [sysname] NULL,[dbname] [nvarchar] (128)NULL, " " [dbid] [smallint] NULL, " " [fileid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [filetype] [varchar](9) NOT NULL, " " [name] [sysname] NOT NULL, " " [filename] [nvarchar](260) NOT NULL, " " [file_size] [int] NULL, " " [space_used] [int] NULL, " " [space_left] [int] NULL, " " [autogrow] [int] NOT NULL, " " [maxsizeinmb] [int] NULL, " " [maxsize] [int] NOT NULL, " " [space_available] [int] NOT NULL) ON [PRIMARY] " " SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar) " " SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > 0 " " group by dbid,name " " ORDER BY dbid " " end " " else select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state when 0 then 0 else 1 end " " from master.sys.databases " " WHERE database_id > 0 " " group by database_id,name,state " " ORDER BY database_id " " WHILE @db_name IS NOT NULL " " BEGIN " " if @canttest = 0 " " begin " " SET @sql_string = 'USE ' + QUOTENAME(@db_name) + CHAR(10) " " SET @sql_string = @sql_string + 'Select isnull(g.groupname,''TRAN LOG''),DB_NAME(),db_id() as dbid,fileid,s.groupid,case when s.groupid = 0 " " then ''log file'' else ''data file'' end as filetype, name ,filename, " " [file_size] = convert(int,round((s.size*1.000)/128.000,0)), " " [space_used] = convert(int,round(fileproperty(s.name,''SpaceUsed'')/128.000,0)), " " [space_left] = convert(int,round((s.size-fileproperty(s.name,''SpaceUsed''))/128.000,0)), " " case when growth > 0 then 1 else 0 end as autogrow, " " case when maxsize = -1 then -1 else convert(decimal,maxsize)*8/1024 end as maxsizeinmb" " ,maxsize,0 as space_available from dbo.sysfiles s " " left outer join dbo.sysfilegroups g " " on s.groupid = g.groupid' " " INSERT INTO [#space_tab] " " EXEC sp_executesql @sql_string " " end " " SET @db_name = NULL " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > @database_id " " group by dbid,name " " ORDER BY dbid " " end " " else " " select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state " " when 0 then 0 else 1 end " " from master.sys.databases d " " WHERE database_id > @database_id " " and database_id not in (select database_id from sys.master_files where is_sparse = 1) " " group by database_id,name,state " " ORDER BY database_id " " END " " DECLARE freespace_cur Cursor " " FOR select autogrow,maxsizeinmb,space_left,fileid,groupid,substring(filename,1,1),space_used,dbid from #space_tab " " Open freespace_cur " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if @autogrow = 1 " " begin " " select @mbfree = (select MB_Free from #freespace where Drive = @driveletter) " " if @maxsize <> -1 " " begin " " if @maxsize > @mbfree " " select @maxsize = @mbfree " " else " " select @maxsize = @maxsize - @spaceused " " end " " else " " begin " " select @maxsize = @mbfree + @space_left " " end " " update [#space_tab] " " set space_available = @maxsize " " where fileid = @fileid and groupid = @groupid and dbid = @dbid " " end " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " END " " CLOSE freespace_cur " " DEALLOCATE freespace_cur " " drop table #freespace " " declare autogrow_cur cursor for " " select substring(filename,1,1),groupname,dbname,dbid,groupid,space_available,space_used,file_size auto_ from [#space_tab] " " where autogrow = 1 " " order by dbid,groupid,1 " " Open autogrow_cur " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if not exists (select * from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " begin " " insert into #temptab values(@adriveletter,@agroupname,@adbname,@adbid,@agroupid,@aspace_used,@aspace_available,@file_size) " " end " " else " " begin " " select @old_spaceused = (select space_used from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceused = @old_spaceused + @aspace_used " " select @old_spaceav = (select space_available from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceav = @old_spaceav+ @aspace_available " " select @old_file_size = (select file_size from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_file_size = @old_file_size + @file_size " " update #temptab set space_used = @new_spaceused ,space_available= @new_spaceav, file_size=@new_file_size " " where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid " " end " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " end " " close autogrow_cur " " deallocate autogrow_cur " " select groupname,dbname,dbid,groupid,sum(space_used) as space_used,sum(space_available) as space_available,sum(file_size) as file_size " " into #autogrowfiles from #temptab " " group by groupname,dbname,groupid,dbid " " order by dbid,groupid " " drop table #temptab " " select groupname,dbname,dbid,groupid,sum(space_left) as space_left,SUM(space_used) as space_used,sum(file_size) as file_size " " into #fixedfiles " " from [#space_tab] " " where autogrow = 0 " " group by dbid,groupid,dbname,groupname " " select isnull(a.dbname,b.dbname) as db_name,isnull(a.groupname,b.groupname) as fg_name,convert(decimal,(isnull(a.space_available,0)+isnull(b.space_left,0)))*1024*1024 as total_space_available, " " convert(decimal,(isnull(a.space_used,0)+isnull(b.space_used,0)))*1024*1024 as used, " " convert(decimal,(isnull(a.file_size,0)+isnull(b.file_size,0)))*1024*1024 as allocated, " " convert(decimal,isnull(a.space_available,0))*1024*1024 as autogrowavailable,convert(decimal,isnull(b.space_left,0))*1024*1024 as fixedfileavailable into #results_tab from " " #autogrowfiles a " " full outer join " " #fixedfiles b " " on a.dbid = b.dbid and a.groupid = b.groupid " " order by 1,2 " " select db_name,fg_name,used,allocated, " " 100-(convert (decimal,used)+0.001) / ((convert (decimal,used)+0.001)+(convert (decimal,total_space_available)+0.001))*100 " " as 'free_pct' " " from #results_tab " " drop table #results_tab " " drop table #space_tab " " drop table #autogrowfiles " " drop table #fixedfiles "}, {"aag_cluster_quorum_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(cluster_name) cluster_name, quorum_state, rtrim(quorum_state_desc) quorum_state_desc from sys.dm_hadr_cluster"}, {"aag_cluster_members_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(member_name) object, member_state, rtrim(member_state_desc) member_state_desc from sys.dm_hadr_cluster_members"}, {"aag_synchronization_health", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.primary_replica) primary_replica, b.synchronization_health, " "rtrim(b.synchronization_health_desc) synchronization_health_desc " "from sys.availability_groups a, sys.dm_hadr_availability_group_states b where a.group_id = b.group_id"}, {"aag_replica_synchronization_health", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.synchronization_health, c.synchronization_health_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id"}, {"aag_replica_connected_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.connected_state, c.connected_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id"}, {"aag_replica_recovery_health", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.recovery_health, c.recovery_health_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.is_local != 0"}, {"aag_replica_operational_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, c.role_desc, c.operational_state, c.operational_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_availability_replica_states c " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.is_local != 0"}, {"aag_db_replica_synchronization_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, rtrim(d.name) database_name, c.synchronization_state, " "c.synchronization_state_desc " "from sys.availability_groups a,sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_database_replica_states c,master.sys.databases d " "where a.group_id = b.group_id and b.group_id = c.group_id and b.replica_id = c.replica_id and c.database_id = d.database_id"}, {"aag_db_page_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select a.name, rtrim(b.replica_server_name) object, rtrim(d.name) database_name,c.page_status " "from sys.availability_groups a, sys.dm_hadr_availability_replica_cluster_states b,sys.dm_hadr_auto_page_repair c,master.sys.databases d, " "sys.dm_hadr_database_replica_states e " "where a.group_id = b.group_id and b.group_id = e.group_id and b.replica_id = e.replica_id and c.database_id = d.database_id and c.database_id = e.database_id"}, {"aag_listener_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT rtrim(AGC.name) name, rtrim(RCS.replica_server_name)object, ARS.role, ARS.role_desc, rtrim(AGL.dns_name) dns_name, LIP.state_desc, LIP.state FROM " "sys.availability_groups_cluster AS AGC INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id INNER JOIN " "sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id INNER JOIN sys.availability_group_listeners AS AGL " "ON AGL.group_id = ARS.group_id INNER JOIN sys.availability_group_listener_ip_addresses AS LIP ON AGL.listener_id = LIP.listener_id ORDER BY AGL.dns_name,RCS.replica_server_name,LIP.state DESC, ARS.role_desc"}, {"wait_stats_count", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT [wait_type],[waiting_tasks_count] AS [wait_count] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN (" " N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', " " N'CHKPT', N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', " " N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', " " N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',N'ONDEMAND_TASK_QUEUE',N'PREEMPTIVE_XE_GETTARGETSTATE',N'PWAIT_ALL_COMPONENTS_INITIALIZED', " " N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',N'QDS_SHUTDOWN_QUEUE', " " N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', " " N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', " " N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_RECOVERY',N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', " " N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ORDER BY waiting_tasks_count DESC"}, {"wait_stats_time", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT [wait_type],[wait_time_ms] AS [wait_time] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN (" " N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', " " N'CHKPT', N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', " " N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', " " N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',N'ONDEMAND_TASK_QUEUE',N'PREEMPTIVE_XE_GETTARGETSTATE',N'PWAIT_ALL_COMPONENTS_INITIALIZED', " " N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',N'QDS_SHUTDOWN_QUEUE', " " N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', " " N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', " " N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_RECOVERY',N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', " " N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [wait_time_ms] > 0 ORDER BY wait_time_ms DESC"}, {NULL,NULL} }; QueryVers MSSql80NoLock[] = { {"generic_query", "select getdate()"}, {"database_size", "sp_MSforeachdb ' SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " "declare @dbsize_pg bigint " "declare @logsize_pg bigint " "declare @reserved_pg bigint " "declare @used_pg bigint " "declare @data_pg bigint " "select @dbsize_pg = sum(size) from [?].dbo.sysfiles where (status & 64 = 0) " "select @logsize_pg = sum(size) from [?].dbo.sysfiles where (status & 64 <> 0) " "select @reserved_pg = sum(reserved) from [?].dbo.sysindexes where indid in (0, 1, 255) " "select @used_pg = sum(used) from [?].dbo.sysindexes where indid in (0, 1, 255) " "select @data_pg = sum(dpages) from [?].dbo.sysindexes where indid < 2 " "select @data_pg = @data_pg + isnull(sum(used),0) from [?].dbo.sysindexes where indid = 255 " "select object = ''?'', " " database_size = ((convert(dec(15,2),@dbsize_pg) + convert(dec(15,2),@logsize_pg)) * 8192), " " free_space = ((convert(dec(15,2),(@dbsize_pg+@logsize_pg)) - convert(dec(15,2),@reserved_pg)) * 8192), " " reserved = (@reserved_pg * 8192), " " data = (@data_pg * 8192), " " index_size = ((@used_pg - @data_pg) * 8192), " " unused = ((@reserved_pg - @used_pg) * 8192)'"}, {"buf_cachehit_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),a.cntr_value) as page_lookups, convert(dec(12,0),b.cntr_value) as page_reads, convert(dec(12,0),c.cntr_value) as page_writes from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b, master.dbo.sysperfinfo c where a.counter_name = 'Page lookups/sec' and b.counter_name = 'Page reads/sec' and c.counter_name = 'Page writes/sec'"}, {"lock_requests", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Requests/sec'"}, {"database_state", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(name) object, state = " " CASE" " when status & 32 = 32 then 6" " when status & 64 = 64 then 3" " when status & 262144 = 262144 then 3" " when status & 128 = 128 then 2" " when status & 256 = 256 then 4" " when status & 512 = 512 then 6" " when status & 2048 = 2048 then 5" " when status & 4096 = 4096 then 5" " when status & 32768 = 32768 then 5" " else 0" " END , state_desc =" " CASE" " when status & 32 = 32 then 'OFFLINE'" " when status & 64 = 64 then 'RECOVERY_PENDING'" " when status & 262144 = 262144 then 'RECOVERY_PENDING'" " when status & 128 = 128 then 'RECOVERING'" " when status & 256 = 256 then 'SUSPECT'" " when status & 512 = 512 then 'OFFLINE'" " when status & 2048 = 2048 then 'EMERGENCY'" " when status & 4096 = 4096 then 'EMERGENCY'" " when status & 32768 = 32768 then 'EMERGENCY'" " else 'ONLINE'" " END " "from master..sysdatabases"}, {"log_cachehit_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(a.instance_name) as object, convert(dec(12,0),a.cntr_value) as cache_hits, convert(dec(12,0),b.cntr_value) as cache_base from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b " "where a.counter_name = 'Log Cache Hit Ratio' and b.counter_name = 'Log Cache Hit Ratio Base' and a.instance_name = b.instance_name"}, {"login_count", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value) connections from master.dbo.sysperfinfo where counter_name ='User Connections'"}, {"active_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) active_connections from master.dbo.sysperfinfo where counter_name ='Active Transactions'"}, {"deadlocks", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Number of Deadlocks/sec'"}, {"lock_timeouts", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Timeouts/sec'"}, {"lock_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Lock Waits/sec'"}, {"transactions", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name ='Transactions/sec' order by 1"}, {"log_flush_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Log Flush Waits/sec'"}, {"page_reads", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"}, {"page_writes", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00', getdate()) time from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"}, {"latch_waits", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Latch Waits/sec'"}, {"full_scans", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value) count, datediff(ss, '1970-01-01 00:00:00',getdate()) time from master.dbo.sysperfinfo where counter_name = 'Full Scans/sec'"}, {"database_count", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count from master.dbo.sysdatabases"}, {"server_startup", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select datediff(dd, crdate, getdate()) count, crdate date from master.dbo.sysdatabases where name ='tempdb'"}, {"log_file_growths", "select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count from master.dbo.sysperfinfo where counter_name = 'Log Growths'"}, {"log_file_shrinks", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(instance_name) object, convert(dec(12,0),cntr_value) count from master.dbo.sysperfinfo where counter_name = 'Log Shrinks'"}, {"lock_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Lock Memory (KB)'"}, {"connection_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Connection Memory (KB)'"}, {"optimizer_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'Optimizer Memory (KB)'"}, {"sqlcache_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select convert(dec(12,0),cntr_value)*1024 count from master.dbo.sysperfinfo where counter_name = 'SQL Cache Memory (KB)'"}, {"total_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Total Server Memory (KB)') count, (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Target Server Memory(KB)') target"}, {"locks_used", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name = 'Lock Blocks') lock_blocks, (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name = 'Lock Owner Blocks') owner_blocks, " "(select convert(dec(12,0),high) from master.dbo.spt_values where name = 'locks') maximum, ISNULL((select convert(dec(12,0),value) from master.dbo.syscurconfigs where config=106),0) value_in_use"}, {"workspace_memory", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Granted Workspace Memory (KB)') granted, (select convert(dec(12,0),cntr_value)*1024 from master.dbo.sysperfinfo where counter_name = 'Maximum Workspace Memory (KB)') maximum"}, {"average_waittime", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select rtrim(a.instance_name) object, convert(dec(12,0),a.cntr_value) lock_waits, convert(dec(12,0),b.cntr_value) wait_time from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b where a.counter_name = 'Lock Waits/sec' and b.counter_name = 'Lock Wait Time (ms)' and a.instance_name = b.instance_name"}, {"server_cpu", "exec sqlsrv_cpu"}, {"server_io", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select @@IO_BUSY io_busy, @@TIMETICKS timeticks, datediff(ss, '1970-01-01 00:00:00', getdate()) time"}, {"free_connections", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select convert(dec(12,0),cntr_value) from master.dbo.sysperfinfo where counter_name ='User Connections') connections, (select convert(dec(12,0),value) from master.dbo.syscurconfigs where config=103) config_connections, convert(dec(12,0),@@MAX_CONNECTIONS) max_connections"}, {"user_cpu", "exec sqlusr_cpu"}, {"user_waits", "SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @waittime int " "declare @blockerid smallint " "declare @blockerhid nchar(8) " "declare @blockeruid varchar(128) " "declare @blockedid smallint " "declare @blockedhid nchar(8) " "declare @blockeduid varchar(128) " "declare @dbname sysname " "declare @sql_text varchar(255) " "create table #sqltext (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255)) " "create table #blocker (waittime int, sid_blocker smallint, hid_blocker nchar(8), uid_blocker nchar(128), sid_blocked smallint, hid_blocked nchar(8), uid_blocked nchar(128), dbname sysname, sqltext varchar(255)) " "declare user_spid cursor for select a.waittime, a.blocked, b.hostprocess, rtrim(b.loginame), a.spid, a.hostprocess, rtrim(a.loginame), db_name(a.dbid) from master.dbo.sysprocesses a, master.dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid " "open user_spid " "fetch next from user_spid into @waittime,@blockerid, @blockerhid, @blockeruid, @blockedid, @blockedhid, @blockeduid, @dbname WHILE @@FETCH_STATUS = 0 " "BEGIN " "insert into #sqltext EXEC ('dbcc inputbuffer('+@blockedid+') with tableresults, NO_INFOMSGS') " "select @sql_text = EventInfo from #sqltext " "insert into #blocker values (@waittime,@blockerid,@blockerhid, @blockeruid, @blockedid,@blockedhid, @blockeduid, @dbname, @sql_text) " "delete from #sqltext " "fetch next from user_spid into @waittime, @blockerid, @blockerhid, @blockeruid, @blockedid, @blockedhid, @blockeduid, @dbname " "END " "close user_spid " "deallocate user_spid " "select convert(dec(20,3),waittime)/1000 waittime, convert(varchar(5),sid_blocker) sid_blocker, hid_blocker, rtrim(uid_blocker) uid_blocker," "convert(varchar(5),sid_blocked) sid_blocked, hid_blocked, rtrim(uid_blocked) uid_blocked, rtrim(dbname) db_name, rtrim(ltrim(sqltext)) sql_blocked from #blocker " "drop table #sqltext " "drop table #blocker"}, {"locked_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count from master.dbo.sysprocesses a, master.dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid"}, {"blocked_users", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select count(*) count from dbo.sysprocesses a, dbo.sysprocesses b where a.blocked != 0 and a.blocked = b.spid and a.status != 'suspended'"}, {"av_fragmentation", "select 'dummy' dummy"}, {"logic_fragment", "SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @tablename VARCHAR (128) declare @dbname sysname declare @stmnt VARCHAR (512) declare @stmnt2 VARCHAR (512) select @stmnt2 = '''DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS''' " "CREATE TABLE #fraglist (objectname CHAR (255), objectid INT, indexname CHAR (255), indexid INT, lvl INT NULL, countpages INT NULL, countrows INT NULL, minrecsize INT NULL, maxrecsize INT NULL, avgrecsize INT NULL, forreccount INT NULL, extents INT NULL, extentswitches INT NULL, avgfreebytes INT NULL, avgpagedensity INT NULL, scandensity DECIMAL, bestcount INT, actualcount INT, logicalfrag DECIMAL, extentfrag DECIMAL NULL) " "SET LOCK_TIMEOUT %ld DECLARE dbases CURSOR FOR SELECT name from master..sysdatabases OPEN dbases FETCH NEXT from dbases into @dbname WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt = 'use ['+ @dbname +'] INSERT INTO #fraglist EXEC ('+ @stmnt2 +')' EXEC (@stmnt) select @dbname db_name, rtrim(objectname) object, rtrim(indexname) indexname, scandensity, logicalfrag, indid from #fraglist f, sysindexes i WHERE f.objectid = i.id and (i.indid = 0 or (i.indid = 1 and i.name = f.indexname) or (i.indid = 255 and i.name = f.indexname)) " "delete from #fraglist FETCH NEXT from dbases into @dbname END CLOSE dbases DEALLOCATE dbases DROP TABLE #fraglist"}, {"free_space", "sp_MSforeachdb 'SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, (select convert(bigint,sum(size))*8192 from [?].dbo.sysfiles where (status & 64 = 0)) allocated, " "ISNULL((select convert(bigint,sum(maxsize))*8192 from [?].dbo.sysfiles where ((status & 64 = 0) and growth > 0) having (select count(*) from [?].dbo.sysfiles where maxsize = -1 and status & 64 = 0 and growth > 0) = 0),-1) max, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),size)))*8192 from [?].. sysfiles where (status & 64 = 0) and growth = 0),0) max2, " "(select convert(bigint,sum(convert(dec(18,0),used)))* 8192 from [?].. sysindexes where indid in (0, 1, 255)) used, " "(select count(*) from [?].. sysfiles where maxsize = -1 and status & 64 = 0 and growth > 0) unlimited ' "}, {"fg_free_space", "sp_MSforeachdb 'SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select ''?'' db_name, groupname fg_name, (select convert(bigint,sum(size))*8192 from [?].dbo.sysfiles f where f.groupid = g.groupid and (status & 64 = 0)) allocated, " "ISNULL((select convert(bigint,sum(maxsize))*8192 from [?].dbo.sysfiles f where (growth > 0 and f.groupid = g.groupid and (status & 64 = 0)) having ((select count(*) from [?].. sysfiles f where maxsize = -1 and growth > 0 and (status & 64 = 0) and f.groupid = g.groupid) = 0)),-1) max, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),size)))*8192 from [?].. sysfiles f where growth = 0 and f.groupid = g.groupid and (status & 64 = 0)),0) max2, " "ISNULL((select convert(bigint,sum(convert(dec(18,0),used)))* 8192 from [?].. sysindexes f where indid in (0, 1, 255) and f.groupid = g.groupid),0) used, " "(select count(*) from [?].. sysfiles f where maxsize = -1 and growth > 0 and (status & 64 = 0) and f.groupid = g.groupid) unlimited from [?].dbo.sysfilegroups g' "}, {"table_space", "sp_MSforeachdb 'SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED use [?] select ''?'' db_name, USER_NAME(o.uid) schema_name, o.name table_name, ((select IsNULL(sum(dpages),0)*8192 from " "[?].dbo.sysindexes i where indid < 2 and i.id=o.id) + (select IsNULL(sum(used),0)*8192 from [?].dbo.sysindexes i where indid = 255 and " "i.id=o.id)) data_size, ((select IsNULL(sum(used),0)*8192 from [?].dbo.sysindexes i where indid in (0, 1, 255) and i.id=o.id) - " "((select IsNULL(sum(dpages),0)*8192 from [?].dbo.sysindexes i where indid < 2 and i.id=o.id) + (select IsNULL(sum(used),0)*8192 from " "[?].dbo.sysindexes i where indid = 255 and i.id=o.id))) index_size, (select IsNULL(sum(reserved),0)*8192 from [?].dbo.sysindexes i " "where indid in (0, 1, 255) and i.id=o.id) table_size from [?].dbo.sysobjects o where o.type = ''U'' use master'"}, {"logfile_usage", "SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @db sysname declare @stmnt1 VARCHAR (1500) create table #logsize ( dbase varchar(255), logsize float, logused float, status bigint ) " "insert #logsize exec( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS' ) DECLARE dbases CURSOR FOR SELECT name from (select [dbid],name,[status] " "from master..sysdatabases where (status & 32 != 32) AND (status & 64 != 64) AND (status & 128 != 128)) tbl OPEN dbases FETCH NEXT from dbases into @db " "WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt1 = 'select dbase db_name, +'+ " "'+(select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0)) allocated,+'+ " "'+ISNULL((select CONVERT(bigint,sum(maxsize))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth > 0 having (select count(*) from ['+ @db+'].dbo.sysfiles where maxsize = -1 and (status & 64 <> 0) and growth > 0) = 0),-1) max,+'+ " "'+ISNULL((select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth = 0),0) max2,+'+ " "'+(select count(*) from ['+@db+'].dbo.sysfiles where maxsize = -1 and status & 64 <> 0 and growth > 0) unlimited,+'+ " "'+CONVERT(bigint,logsize)*1024 logsize, logused as used from #logsize where dbase ='''+ @db +''' ' " "EXEC (@stmnt1) FETCH NEXT from dbases into @db END CLOSE dbases DEALLOCATE dbases drop table #logsize"}, {"backup_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D' GROUP BY B.name ORDER BY B.name"}, {"transaction_backup_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM ( SELECT * from master.dbo.sysdatabases C where DATABASEPROPERTYEX(C.name, 'Recovery') != 'SIMPLE' ) B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'L' GROUP BY B.name ORDER BY B.name"}, {"differential_backup_status", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT B.name as db_name, ISNULL(ABS(DATEDIFF(MINUTE, GetDate(), MAX(backup_finish_date))), -1) as minutes, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as last_backup " "FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'I' GROUP BY B.name ORDER BY B.name"}, {"long_queries", "select 'dummy' dummy"}, {"long_jobs", "select 'dummy' dummy"}, {"active_connection_ratio", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select CASE value WHEN 0 THEN 32767 ELSE value END from dbo.sysconfigures where comment like '%user connections%') as total, " " (select count(dbid) from dbo.sysprocesses where hostname <> '' and dbid > 0) as active"}, {"logfile_size", "SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @db sysname declare @stmnt1 VARCHAR (2000) create table #logsize ( dbase varchar(255), logsize float, logused float, status bigint ) " "insert #logsize exec( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS' ) DECLARE dbases CURSOR FOR SELECT name from (select [dbid],name,[status] " "from master..sysdatabases where (status & 32 != 32) AND (status & 64 != 64) AND (status & 128 != 128)) tbl OPEN dbases FETCH NEXT from dbases into @db " "WHILE @@FETCH_STATUS = 0 BEGIN " "select @stmnt1 = 'select dbase db_name, +'+ " "'+(select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0)) allocated,+'+ " "'+ISNULL((select CONVERT(bigint,sum(maxsize))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth > 0 having (select count(*) from ['+ @db+'].dbo.sysfiles where maxsize = -1 and (status & 64 <> 0) and growth > 0) = 0),-1) max,+'+ " "'+ISNULL((select CONVERT(bigint,sum(size))*8 from ['+@db+'].dbo.sysfiles where (status & 64 <> 0) and growth = 0),0) max2,+'+ " "'+(select count(*) from ['+@db+'].dbo.sysfiles where maxsize = -1 and status & 64 <> 0 and growth > 0) unlimited,+'+ " "'+CONVERT(bigint,logsize)*1024 logsize, logused as used from #logsize where dbase ='''+ @db +''' ' " "EXEC (@stmnt1) FETCH NEXT from dbases into @db END CLOSE dbases DEALLOCATE dbases drop table #logsize"}, {"mirror_state", "select 'dummy' dummy"}, {"mirror_witness_server", "select 'dummy' dummy"}, {"mirror_sqlinstance", "select 'dummy' dummy"}, {"suspect_pages", "select 'dummy' dummy"}, {"agent_job_failure", "SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select h.job_id as job_id, j.name as job_name, c.name category_name," "dateadd(hh,run_time/10000,dateadd(mi,run_time%10000/100,dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) as rundate, " "datediff(minute, dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))), getdate()) as elapsed_time " "from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id " "inner join msdb.dbo.syscategories c on j.category_id = c.category_id " "where c.category_class = 1 and h.run_status <> 1 and h.step_id = 0 and " "dateadd(hh,run_time/10000, dateadd(mi,run_time%10000/100, dateadd(ss,run_time%100, convert(datetime,convert(nvarchar(8),run_date),112)))) > dateadd(ss,-60*60*36,getdate()) " "order by elapsed_time"}, {"ls_primary_status", "select 'dummy' dummy"}, {"ls_secondary_status", "select 'dummy' dummy"}, {"ls_primary_time_since_last_backup","select 'dummy' dummy"}, {"ls_secondary_time_since_last_copy","select 'dummy' dummy"}, {"ls_secondary_time_since_last_restore","select 'dummy' dummy"}, {"ls_secondary_last_restored_latency", "select 'dummy' dummy"}, {"fg_freeSpace_with_avail_disk", " SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED " " DECLARE @database_id int " " DECLARE @db_name sysname " " DECLARE @sql_string nvarchar(2000) " " DECLARE @adriveletter nchar(2),@agroupname sysname,@adbname nvarchar(256),@adbid smallint,@agroupid smallint,@aspace_available int,@aspace_used int, " " @file_size int " " DECLARE @maxavailable bigint,@old_spaceused bigint,@new_spaceused bigint,@old_spaceav bigint,@new_spaceav bigint, @old_file_size bigint,@new_file_size bigint " " DECLARE @autogrow int,@maxsize int,@space_left int,@fileid int,@groupid int,@driveletter nvarchar(1),@mbfree int,@spaceused int,@spaceavialable int, " " @dbid sysname,@canttest smallint " " DECLARE @ver nvarchar(128) " " CREATE TABLE [#temptab]([driveletter] nvarchar(2), " " [groupname] [sysname] NULL, " " [dbname] [nvarchar](128) NULL, " " [dbid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [space_used] [int] NULL, " " [space_available] [int] NULL, " " [file_size] [int] NULL) ON [PRIMARY] " " create table #freespace( Drive nvarchar(1),MB_Free int) " " insert into #freespace exec [master].[dbo].[xp_fixeddrives] " " CREATE TABLE [#space_tab]([groupname] [sysname] NULL,[dbname] [nvarchar] (128)NULL, " " [dbid] [smallint] NULL, " " [fileid] [smallint] NULL, " " [groupid] [smallint] NULL, " " [filetype] [varchar](9) NOT NULL, " " [name] [sysname] NOT NULL, " " [filename] [nvarchar](260) NOT NULL, " " [file_size] [int] NULL, " " [space_used] [int] NULL, " " [space_left] [int] NULL, " " [autogrow] [int] NOT NULL, " " [maxsizeinmb] [int] NULL, " " [maxsize] [int] NOT NULL, " " [space_available] [int] NOT NULL) ON [PRIMARY] " " SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar) " " SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > 0 " " group by dbid,name " " ORDER BY dbid " " end " " else select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state when 0 then 0 else 1 end " " from master.sys.databases " " WHERE database_id > 0 " " group by database_id,name,state " " ORDER BY database_id " " WHILE @db_name IS NOT NULL " " BEGIN " " if @canttest = 0 " " begin " " SET @sql_string = 'USE ' + QUOTENAME(@db_name) + CHAR(10) " " SET @sql_string = @sql_string + 'Select isnull(g.groupname,''TRAN LOG''),DB_NAME(),db_id() as dbid,fileid,s.groupid,case when s.groupid = 0 " " then ''log file'' else ''data file'' end as filetype, name ,filename, " " [file_size] = convert(int,round((s.size*1.000)/128.000,0)), " " [space_used] = convert(int,round(fileproperty(s.name,''SpaceUsed'')/128.000,0)), " " [space_left] = convert(int,round((s.size-fileproperty(s.name,''SpaceUsed''))/128.000,0)), " " case when growth > 0 then 1 else 0 end as autogrow, " " case when maxsize = -1 then -1 else convert(decimal,maxsize)*8/1024 end as maxsizeinmb" " ,maxsize,0 as space_available from dbo.sysfiles s " " left outer join dbo.sysfilegroups g " " on s.groupid = g.groupid' " " INSERT INTO [#space_tab] " " EXEC sp_executesql @sql_string " " end " " SET @db_name = NULL " " IF ( @ver = '8' ) " " begin " " SELECT TOP 1 @database_id = dbid " " ,@db_name = name " " ,@canttest = sum((status & 32) +(status & 64) + (status & 128) +(status & 256) + (status & 512) + (status & 32768)) " " FROM master.dbo.sysdatabases " " WHERE dbid > @database_id " " group by dbid,name " " ORDER BY dbid " " end " " else " " select TOP 1 @database_id = database_id,@db_name =name,@canttest = case state " " when 0 then 0 else 1 end " " from master.sys.databases d " " WHERE database_id > @database_id " " and database_id not in (select database_id from sys.master_files where is_sparse = 1) " " group by database_id,name,state " " ORDER BY database_id " " END " " DECLARE freespace_cur Cursor " " FOR select autogrow,maxsizeinmb,space_left,fileid,groupid,substring(filename,1,1),space_used,dbid from #space_tab " " Open freespace_cur " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if @autogrow = 1 " " begin " " select @mbfree = (select MB_Free from #freespace where Drive = @driveletter) " " if @maxsize <> -1 " " begin " " if @maxsize > @mbfree " " select @maxsize = @mbfree " " else " " select @maxsize = @maxsize - @spaceused " " end " " else " " begin " " select @maxsize = @mbfree + @space_left " " end " " update [#space_tab] " " set space_available = @maxsize " " where fileid = @fileid and groupid = @groupid and dbid = @dbid " " end " " Fetch NEXT FROM freespace_cur INTO @autogrow ,@maxsize ,@space_left ,@fileid ,@groupid ,@driveletter,@spaceused,@dbid " " END " " CLOSE freespace_cur " " DEALLOCATE freespace_cur " " drop table #freespace " " declare autogrow_cur cursor for " " select substring(filename,1,1),groupname,dbname,dbid,groupid,space_available,space_used,file_size auto_ from [#space_tab] " " where autogrow = 1 " " order by dbid,groupid,1 " " Open autogrow_cur " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " WHILE @@FETCH_STATUS = 0 " " BEGIN " " if not exists (select * from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " begin " " insert into #temptab values(@adriveletter,@agroupname,@adbname,@adbid,@agroupid,@aspace_used,@aspace_available,@file_size) " " end " " else " " begin " " select @old_spaceused = (select space_used from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceused = @old_spaceused + @aspace_used " " select @old_spaceav = (select space_available from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_spaceav = @old_spaceav+ @aspace_available " " select @old_file_size = (select file_size from #temptab where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid) " " select @new_file_size = @old_file_size + @file_size " " update #temptab set space_used = @new_spaceused ,space_available= @new_spaceav, file_size=@new_file_size " " where driveletter = @adriveletter and groupid = @agroupid and dbid = @adbid " " end " " Fetch NEXT FROM autogrow_cur INTO @adriveletter,@agroupname,@adbname,@adbid,@agroupid ,@aspace_available,@aspace_used,@file_size " " end " " close autogrow_cur " " deallocate autogrow_cur " " select groupname,dbname,dbid,groupid,sum(space_used) as space_used,sum(space_available) as space_available,sum(file_size) as file_size " " into #autogrowfiles from #temptab " " group by groupname,dbname,groupid,dbid " " order by dbid,groupid " " drop table #temptab " " select groupname,dbname,dbid,groupid,sum(space_left) as space_left,SUM(space_used) as space_used,sum(file_size) as file_size " " into #fixedfiles " " from [#space_tab] " " where autogrow = 0 " " group by dbid,groupid,dbname,groupname " " select isnull(a.dbname,b.dbname) as db_name,isnull(a.groupname,b.groupname) as fg_name,convert(decimal,(isnull(a.space_available,0)+isnull(b.space_left,0)))*1024*1024 as total_space_available, " " convert(decimal,(isnull(a.space_used,0)+isnull(b.space_used,0)))*1024*1024 as used, " " convert(decimal,(isnull(a.file_size,0)+isnull(b.file_size,0)))*1024*1024 as allocated, " " convert(decimal,isnull(a.space_available,0))*1024*1024 as autogrowavailable,convert(decimal,isnull(b.space_left,0))*1024*1024 as fixedfileavailable into #results_tab from " " #autogrowfiles a " " full outer join " " #fixedfiles b " " on a.dbid = b.dbid and a.groupid = b.groupid " " order by 1,2 " " select db_name,fg_name,used,allocated, " " 100-(convert (decimal,used)+0.001) / ((convert (decimal,used)+0.001)+(convert (decimal,total_space_available)+0.001))*100 " " as 'free_pct' " " from #results_tab " " drop table #results_tab " " drop table #space_tab " " drop table #autogrowfiles " " drop table #fixedfiles "}, {NULL,NULL} };