SELECT DB_NAME([database_id]) AS [Database Name]    ,[file_id]    ,[name]    ,physical_name    ,type_desc    ,state_desc    ,is_percent_growth    ,growth    ,CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]    ,CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]FROM sys.master_files WITH (NOLOCK)WHERE [database_id] > 4    AND [database_id] <> 32767    OR [database_id] = 2ORDER BY DB_NAME([database_id])OPTION (RECOMPILE);

SELECT f.[name] AS [File Name]    ,f.physical_name AS [Physical Name]    ,CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [Total Size in MB]    ,CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.[name], ‘SpaceUsed‘) AS INT) / 128.0 AS DECIMAL(15, 2)) AS [Available Space In MB]    ,[file_id]    ,fg.[name] AS [Filegroup Name]FROM sys.database_files AS f WITH (NOLOCK)LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_idOPTION (RECOMPILE);

SELECT DISTINCT vs.volume_mount_point    ,vs.file_system_type    ,vs.logical_volume_name    ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]    ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]    ,CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Space Free %]FROM sys.master_files AS f WITH (NOLOCK)CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vsOPTION (RECOMPILE);

SELECT db_name(vs.database_id) AS DatabaseName    ,vs.file_id    ,vs.volume_mount_point    ,vs.volume_id    ,vs.logical_volume_name    ,vs.file_system_type    ,(vs.total_bytes / 1024 / 1024 / 1024) AS [TotalSize(GB)]    ,(vs.available_bytes / 1024 / 1024 / 1024) AS [AvailableSize(GB)]    ,vs.supports_compression    ,vs.supports_alternate_streams    ,vs.supports_sparse_files    ,vs.is_read_only    ,vs.is_compressedFROM sys.master_files mfCROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs;

EXEC master.dbo.xp_fixeddrives

SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS Volume_mount_point    ,total_bytes / 1024 / 1024 AS Total_MB    ,available_bytes / 1024 / 1024 AS Available_MBFROM sys.master_files AS fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

SELECT db.[name] AS [Database Name]    ,db.recovery_model_desc AS [Recovery Model]    ,db.state_desc    ,db.log_reuse_wait_desc AS [Log Reuse Wait Description]    ,CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)]    ,CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)]    ,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]    ,db.[compatibility_level] AS [DB Compatibility Level]    ,db.page_verify_option_desc AS [Page Verify Option]    ,db.is_auto_create_stats_on    ,db.is_auto_update_stats_on    ,db.is_auto_update_stats_async_on    ,db.is_parameterization_forced    ,db.snapshot_isolation_state_desc    ,db.is_read_committed_snapshot_on    ,db.is_auto_close_on    ,db.is_auto_shrink_on    ,db.target_recovery_time_in_seconds    ,db.is_cdc_enabledFROM sys.databases AS db WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.NAME = lu.instance_nameINNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.NAME = ls.instance_nameWHERE lu.counter_name LIKE N‘Log File(s) Used Size (KB)%‘    AND ls.counter_name LIKE N‘Log File(s) Size (KB)%‘    AND ls.cntr_value > 0OPTION (RECOMPILE);

SELECT TOP (30) bs.machine_name    ,bs.server_name    ,bs.database_name AS [Database Name]    ,bs.recovery_model    ,CONVERT(BIGINT, bs.backup_size / 1048576) AS [Uncompressed Backup Size (MB)]    ,CONVERT(BIGINT, bs.compressed_backup_size / 1048576) AS [Compressed Backup Size (MB)]    ,CONVERT(NUMERIC(20, 2), (CONVERT(FLOAT, bs.backup_size) / CONVERT(FLOAT, bs.compressed_backup_size))) AS [Compression Ratio]    ,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)]    ,bs.backup_finish_date AS [Backup Finish Date]FROM msdb.dbo.backupset AS bs WITH (NOLOCK)WHERE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0    AND bs.backup_size > 0    AND bs.type = ‘D‘ -- Change to L if you want Log backups    AND database_name = DB_NAME(DB_ID())ORDER BY bs.backup_finish_date DESCOPTION (RECOMPILE);

CREATE TABLE #VLFInfo (    RecoveryUnitID INT    ,FileID INT    ,FileSize BIGINT    ,StartOffset BIGINT    ,FSeqNo BIGINT    ,[Status] BIGINT    ,Parity BIGINT    ,CreateLSN NUMERIC(38)    );CREATE TABLE #VLFCountResults (    DatabaseName SYSNAME    ,VLFCount INT    );EXEC sp_MSforeachdb N‘Use [?];                 INSERT INTO #VLFInfo                 EXEC sp_executesql N‘‘DBCC LOGINFO([?])‘‘;                      INSERT INTO #VLFCountResults                 SELECT DB_NAME(), COUNT(*)                 FROM #VLFInfo;                 TRUNCATE TABLE #VLFInfo;‘SELECT DatabaseName    ,VLFCountFROM #VLFCountResultsORDER BY VLFCount DESC;DROP TABLE #VLFInfo;DROP TABLE #VLFCountResults;

SELECT is_enabled    ,[path]    ,max_size    ,max_filesFROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK)OPTION (RECOMPILE);

DECLARE @Time_Start DATETIME;DECLARE @Time_End DATETIME;SET @Time_Start = getdate() - 2;SET @Time_End = getdate();-- Create the temporary tableCREATE TABLE #ErrorLog (    logdate DATETIME    ,processinfo VARCHAR(255)    ,Message VARCHAR(500)    )-- Populate the temporary tableINSERT #ErrorLog (    logdate    ,processinfo    ,Message    )EXEC master.dbo.xp_readerrorlog 0    ,1    ,NULL    ,NULL    ,@Time_Start    ,@Time_End    ,N‘desc‘;-- Filter the temporary tableSELECT LogDate    ,MessageFROM #ErrorLogWHERE (        Message LIKE ‘%error%‘        OR Message LIKE ‘%failed%‘        )    AND processinfo NOT LIKE ‘logon‘ORDER BY logdate DESC-- Drop the temporary table DROP TABLE #ErrorLog

CREATE TABLE #IOWarningResults (    LogDate DATETIME    ,ProcessInfo SYSNAME    ,LogText NVARCHAR(1000)    );INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 0    ,1    ,N‘taking longer than 15 seconds‘;INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 1    ,1    ,N‘taking longer than 15 seconds‘;INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 2    ,1    ,N‘taking longer than 15 seconds‘;INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 3    ,1    ,N‘taking longer than 15 seconds‘;INSERT INTO #IOWarningResultsEXEC xp_readerrorlog 4    ,1    ,N‘taking longer than 15 seconds‘;SELECT LogDate    ,ProcessInfo    ,LogTextFROM #IOWarningResultsORDER BY LogDate DESC;DROP TABLE #IOWarningResults;

SELECT [Drive]    ,CASE         WHEN num_of_reads = 0            THEN 0        ELSE (io_stall_read_ms / num_of_reads)        END AS [Read Latency (ms)]    ,CASE         WHEN io_stall_write_ms = 0            THEN 0        ELSE (io_stall_write_ms / num_of_writes)        END AS [Write Latency (ms)]    ,CASE         WHEN (                num_of_reads = 0                AND num_of_writes = 0                )            THEN 0        ELSE (io_stall / (num_of_reads + num_of_writes))        END AS [Overall Latency (ms)]    ,CASE         WHEN num_of_reads = 0            THEN 0        ELSE (num_of_bytes_read / num_of_reads)        END AS [Avg Bytes/Read]    ,CASE         WHEN io_stall_write_ms = 0            THEN 0        ELSE (num_of_bytes_written / num_of_writes)        END AS [Avg Bytes/Write]    ,CASE         WHEN (                num_of_reads = 0                AND num_of_writes = 0                )            THEN 0        ELSE ((num_of_bytes_read + num_of_bytes_written) / (num_of_reads + num_of_writes))        END AS [Avg Bytes/Transfer]FROM (    SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive        ,SUM(num_of_reads) AS num_of_reads        ,SUM(io_stall_read_ms) AS io_stall_read_ms        ,SUM(num_of_writes) AS num_of_writes        ,SUM(io_stall_write_ms) AS io_stall_write_ms        ,SUM(num_of_bytes_read) AS num_of_bytes_read        ,SUM(num_of_bytes_written) AS num_of_bytes_written        ,SUM(io_stall) AS io_stall    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs    INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id        AND vfs.file_id = mf.file_id    GROUP BY LEFT(UPPER(mf.physical_name), 2)    ) AS tabORDER BY [Overall Latency (ms)]OPTION (RECOMPILE);

SELECT DB_NAME(fs.database_id) AS [Database Name]    ,CAST(fs.io_stall_read_ms / (1.0 + fs.num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]    ,CAST(fs.io_stall_write_ms / (1.0 + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]    ,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms) / (1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]    ,CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)]    ,mf.physical_name    ,mf.type_desc    ,fs.io_stall_read_ms    ,fs.num_of_reads    ,fs.io_stall_write_ms    ,fs.num_of_writes    ,fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]    ,fs.num_of_reads + fs.num_of_writes AS [total_io]FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fsINNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id    AND fs.[file_id] = mf.[file_id]ORDER BY avg_io_stall_ms DESCOPTION (RECOMPILE);

SELECT [ReadLatency] = CASE         WHEN [num_of_reads] = 0            THEN 0        ELSE ([io_stall_read_ms] / [num_of_reads])        END    ,[WriteLatency] = CASE         WHEN [num_of_writes] = 0            THEN 0        ELSE ([io_stall_write_ms] / [num_of_writes])        END    ,[Latency] = CASE         WHEN (                [num_of_reads] = 0                AND [num_of_writes] = 0                )            THEN 0        ELSE ([io_stall] / ([num_of_reads] + [num_of_writes]))        END    ,[AvgBytesPerRead] = CASE         WHEN [num_of_reads] = 0            THEN 0        ELSE ([num_of_bytes_read] / [num_of_reads])        END    ,[AvgBytesPerWrite] = CASE         WHEN [num_of_writes] = 0            THEN 0        ELSE ([num_of_bytes_written] / [num_of_writes])        END    ,[AvgBytesPerTransfer] = CASE         WHEN (                [num_of_reads] = 0                AND [num_of_writes] = 0                )            THEN 0        ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))        END    ,LEFT([mf].[physical_name], 2) AS [Drive]    ,DB_NAME([vfs].[database_id]) AS [DB]    ,[mf].[physical_name]    ,[mf].file_idFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id]    AND [vfs].[file_id] = [mf].[file_id]ORDER BY [WriteLatency] DESC;

WITH Aggregate_IO_StatisticsAS (    SELECT DB_NAME(database_id) AS [Database Name]        ,CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]    GROUP BY database_id    )SELECT ROW_NUMBER() OVER (        ORDER BY io_in_mb DESC        ) AS [I/O Rank]    ,[Database Name]    ,io_in_mb AS [Total I/O (MB)]    ,CAST(io_in_mb / SUM(io_in_mb) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]FROM Aggregate_IO_StatisticsORDER BY [I/O Rank]OPTION (RECOMPILE);

SELECT DB_NAME(DB_ID()) AS [Database Name]    ,df.[name] AS [Logical Name]    ,vfs.[file_id]    ,df.physical_name AS [Physical Name]    ,vfs.num_of_reads    ,vfs.num_of_writes    ,vfs.io_stall_read_ms    ,vfs.io_stall_write_ms    ,CAST(100. * vfs.io_stall_read_ms / (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]    ,CAST(100. * vfs.io_stall_write_ms / (vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]    ,(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads]    ,CAST(vfs.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read]    ,CAST(vfs.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written]    ,CAST(100. * vfs.num_of_reads / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct]    ,CAST(100. * vfs.num_of_writes / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct]    ,CAST(100. * vfs.num_of_bytes_read / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct]    ,CAST(100. * vfs.num_of_bytes_written / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfsINNER JOIN sys.database_files AS df WITH (NOLOCK) ON vfs.[file_id] = df.[file_id]OPTION (RECOMPILE);

SELECT TOP (50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name]    ,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO]    ,qs.execution_count AS [Execution Count]    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (            CASE                 WHEN qs.statement_end_offset = - 1                    THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2                ELSE qs.statement_end_offset                END - qs.statement_start_offset            ) / 2) AS [Query Text]FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE qt.[dbid] = DB_ID()ORDER BY [Avg IO] DESCOPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [DBNAME]    ,file_id    ,io_stall    ,io_pending_ms_ticks    ,scheduler_addressFROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs    ,sys.dm_io_pending_io_requests AS iopiorWHERE iovfs.file_handle = iopior.io_handle