SQL Databases. Everything you need to know about them.

This post is about all currently available queries associated with "SQL Databases" functionalities assigned to "Ctrl+5" keyboard shortcut in SSMS (SQL Server management studio).

I'll uncover internals and hidden features of "Ctrl+5" shortcut. You can download the entire script from here.

Associating script to the SSMS shortcut is very handy for an experienced SQL DBA and it is a wonderful learning source for a beginner.
You can you that script to easily get list of ALL your databases, all files in the databases, their sizes and free space, all databases' settings and more. You can troubleshoot pure I/O performance and easily see which database is the busiest one in your system. And many other things.

To have a brief understanding what I'm talking about you can read my previous posts: SSMS Query Shortcuts and SSMS Custom Query shortcuts Update or you can just watch a short video:

So, here are all scripts behind the seen:

1. "Ctrl+5" without parameters:

List of all databases with following Database attributes:
- Database ID, State, Name, File Groups, File Ids, File Names, Physical File Names, File Types,
VLF Count (for Log files), File Sizes, Used File Space, Free File Space, File Auto Growth, Max File Size, Average Read/Write Waits, CPU Usage Percent, Create date, File State, Log Reuse Wait, User Access type, Database Recovery Model, Database Compatibility Level, Auto Create Stats, Auto Update Stats, Auto Close setting, Auto Shrink setting, Snapshot Isolation setting, Database Collation,
Database Page Verify Option, Access State, Access Mode, Role in Availability Group (AG), AG Health, AG State, Is Database Readable in AG.

A. At first we have to create four temporary tables to collect all intermediate results:

CREATE TABLE #tbl_DB_Statistics(
 database_id INT,
 [File Id] INT,
 SizeMB VARCHAR(16),
 MaxSizeMB VARCHAR(16),
 UsedSpaceMB VARCHAR(16),
 FreeSpacePrc CHAR(6),
 [File Group] VARCHAR(128),
 UsedSpace FLOAT
);
GO
CREATE TABLE #tbl_VLFInfo (
 RecoveryUnitID INT,
 FileID INT,
 FileSize BIGINT,
 StartOffset BIGINT,
 FSeqNo BIGINT,
 [Status] BIGINT,
 Parity BIGINT,
 CreateLSN NUMERIC(38));
GO
CREATE TABLE #tbl_AG_DBs(
 database_id INT,
 synchronization_health_desc VARCHAR(60),
 synchronization_state_desc VARCHAR(60),
 database_state_desc VARCHAR(60),
 role_desc VARCHAR(60),
 IsReadable VARCHAR(60));
GO
CREATE TABLE #tbl_VLFCountResults([DB_id] INT, VLFCount INT);
GO

B. All these parameters are collected in just two queries.
The first query is performing multiple times because it collects numbers, which are available only under current database context. That query has to be performed for all databases, which have online status. In case you have more than thousand databases on an instance it can take a while to collect all data. Here is the query:

USE [master];
WITH DbData as (
SELECT
 DB_ID('master') as Database_Id,
 f.file_id as "File Id",
 f.physical_name as "Physical Name",
 CAST(CAST(ROUND(f.Size/128.,3) as DECIMAL(16,3)) 
  as VARCHAR(16)) AS SizeMB,
 CASE f.max_size WHEN 0 THEN 'NO GROWTH' 
  WHEN -1 THEN '2097152.000' 
  ELSE CAST(CAST(ROUND(f.max_size/128.,3) 
   as DECIMAL(16,3)) as VARCHAR(16)) END AS MaxSizeMB,
 CAST(CAST(ROUND(FILEPROPERTY(f.name, 'SpaceUsed')/128.,3) 
   as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB,
 RIGHT('  ' + CAST(CAST((1 - FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size) 
   * 100 as DECIMAL(5,2)) as VARCHAR(6)),6) AS FreeSpacePrc
 , CASE f.file_id WHEN 2 THEN 'Log File' 
  ELSE IsNull(g.name,'N/A') END as [File Group]
 , FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size as UsedSpace
FROM sys.database_files as f with (nolock)
LEFT JOIN sys.filegroups as g with (nolock) 
 ON f.data_space_id = g.data_space_id
)
INSERT INTO #tbl_DB_Statistics
SELECT Database_Id, [File Id],
RIGHT(SPACE(16) + CASE WHEN Len(SizeMB) > 7
 THEN CASE WHEN Len(SizeMB) > 10
 THEN LEFT(SizeMB, LEN(SizeMB) - 10) + ',' 
 + SUBSTRING(SizeMB, LEN(SizeMB) - 10, 3) + ',' 
 + RIGHT(SizeMB, 7)
 ELSE LEFT(SizeMB, LEN(SizeMB) - 7) + ',' 
 + RIGHT(SizeMB, 7) END ELSE SizeMB END, 16) as SizeMB,
RIGHT(SPACE(16) + CASE WHEN Len(MaxSizeMB) > 7
 THEN CASE WHEN Len(MaxSizeMB) > 10
 THEN LEFT(MaxSizeMB, LEN(MaxSizeMB) - 10) 
 + ',' + SUBSTRING(MaxSizeMB, LEN(MaxSizeMB) - 10, 3) 
 + ',' + RIGHT(MaxSizeMB, 7)
 ELSE LEFT(MaxSizeMB, LEN(MaxSizeMB) - 7) + ',' 
 + RIGHT(MaxSizeMB, 7) END ELSE MaxSizeMB END, 16) as MaxSizeMB,
RIGHT(SPACE(16) + CASE WHEN Len(UsedSpaceMB) > 7
 THEN CASE WHEN Len(UsedSpaceMB) > 10
 THEN LEFT(UsedSpaceMB, LEN(UsedSpaceMB) - 10) 
 + ',' + SUBSTRING(UsedSpaceMB, LEN(UsedSpaceMB) - 10, 3) 
 + ',' + RIGHT(UsedSpaceMB, 7)
 ELSE LEFT(UsedSpaceMB, LEN(UsedSpaceMB) - 7) + ',' 
 + RIGHT(UsedSpaceMB, 7) END ELSE UsedSpaceMB END, 16) as UsedSpaceMB
 , FreeSpacePrc
 , [File Group]
 , UsedSpace
FROM DbData
OPTION (RECOMPILE)
   
INSERT INTO #tbl_VLFInfo(RecoveryUnitID, FileID, FileSize
 , StartOffset, FSeqNo, [Status], Parity, CreateLSN)
EXEC sp_executesql N'DBCC LOGINFO() WITH NO_INFOMSGS';
    
INSERT INTO #tbl_VLFCountResults
SELECT DB_ID(), COUNT(*)
FROM #tbl_VLFInfo;

TRUNCATE TABLE #tbl_VLFInfo;

In order to get accurate info from all databases that query has to be run in each of them. This one runs just in context of "master" database.

C. After all necessary data has been collected we can run the final query to produce an output:

;WITH DB_CPU_Stats AS (SELECT DatabaseID, 
 CAST(CAST(SUM(total_worker_time)*100./(
  SELECT SUM(total_worker_time) 
  FROM sys.dm_exec_query_stats with (NOLOCK)
 ) as DECIMAL(5,2)) as VARCHAR) + ' %' as [CPU Percent]
 FROM sys.dm_exec_query_stats AS qs with (NOLOCK)
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
 FROM sys.dm_exec_plan_attributes(qs.plan_handle)
 WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID
 HAVING SUM(total_worker_time) > (
  SELECT SUM(total_worker_time) 
  FROM sys.dm_exec_query_stats with (NOLOCK)) / 100
)
SELECT t.database_id as [Database ID]
 , d.state_desc as [Database State]
 , d.name as [Database Name]
 , t.[File Group]
 , mf.[file_id] as [File Id]
 , mf.name as [File Name]
 , mf.physical_name as [Physical Name]
 , mf.type_desc AS [File Type]
 , IsNull(CASE mf.type_desc WHEN 'LOG' THEN CAST(v.VLFCount as VARCHAR) 
  ELSE '' END,-1) as [VLF Count]
 , IsNull(t.SizeMB, CASE WHEN mf.size/128. > = 1000000 
  THEN CAST(mf.size/128000000 as VARCHAR) + ',' ELSE '' END
  + RIGHT(CASE WHEN mf.size >= 128000000 THEN '000' ELSE '' END 
  + CASE WHEN mf.size/128. > = 1000 
   THEN CAST(((mf.size/128) % 1000000) / 1000 as VARCHAR) + ',' 
   ELSE '' END ,4)
  + RIGHT(CASE WHEN mf.size >= 128000 
   THEN '000' ELSE '' END + CASE WHEN mf.size/128. > 0 
   THEN CAST(((mf.size/128) % 1000) as VARCHAR) END,3)
  + '.' + SUBSTRING(CAST( ROUND(mf.size/128. - mf.size/128,1) 
   as VARCHAR),3,1)) as [File Size, MB]
 , IsNull(t.UsedSpaceMB,'N/A') as [Used Space, MB]
 , IsNull(t.FreeSpacePrc + ' %','N/A') as [Free Space]
 , CASE is_percent_growth WHEN 0 THEN CAST(growth/128 as VARCHAR) + ' Mb'
   ELSE CAST(growth as VARCHAR) + ' %' END as AutoGrowth
 , t.MaxSizeMB as [Max File Size, MB]
 , CAST(ROUND(( s.io_stall_read_ms / ( 1.0 + s.num_of_reads ) ),3) 
  as FLOAT) as [Avg Read Wait, ms]
 , CAST(ROUND(( s.io_stall_write_ms / ( 1.0 + s.num_of_writes ) ),3) 
  as FLOAT) as [Avg Write Wait, ms]
 , IsNull(dbs.[CPU Percent],'') as [CPU Percent]
 , d.create_date as [Created]
 , mf.state_desc as [File State]
 , d.log_reuse_wait_desc as [Log Reuse Wait]
 , d.user_access_desc as [User Access]
 , d.recovery_model_desc as [Recovery Model]
 , [compatibility_level] as [Compatibility Level]
 , CASE d.is_auto_create_stats_on WHEN 1 
  THEN 'Yes' ELSE 'No' END as [Auto Create Stats]
 , CASE d.is_auto_update_stats_on WHEN 1 
  THEN 'Yes' ELSE 'No' END as [Auto Update Stats]
 , CASE d.is_auto_close_on WHEN 1 
  THEN 'Yes' ELSE 'No' END as [Auto Close]
 , CASE d.is_auto_shrink_on WHEN 1 
  THEN 'Yes' ELSE 'No' END as [Auto Shrink]
 , d.snapshot_isolation_state_desc as [Snapshot Isolation]
 , d.collation_name as [Collation Name]
 , d.page_verify_option_desc as [Page Verify Option]
 , CASE d.is_read_only WHEN 0 
  THEN 'READ_WRITE' ELSE 'READ_ONLY' END as [Access State]
 , CASE d.is_in_standby WHEN 0 
  THEN 'Active' ELSE 'Standby for Log Restore' END as [Mode]  
FROM #tbl_DB_Statistics as t
INNER JOIN sys.databases as d with (NOLOCK)
 ON t.database_id = d.database_id 
LEFT JOIN sys.master_files AS mf with (NOLOCK)
 ON d.database_id = mf.database_id 
  AND (t.[File Id] = mf.[file_id] or t.[File Id] is Null)
LEFT JOIN sys.dm_io_virtual_file_stats(NULL, NULL) as s
 ON t.database_id = s.database_id and mf.[file_id] = s.[file_id]
LEFT JOIN #tbl_VLFCountResults as v ON v.[DB_id] = d.database_id
LEFT JOIN DB_CPU_Stats as dbs ON dbs.DatabaseID = d.database_id
OPTION (RECOMPILE);

As the result, you'll get something like this:

From here you can easily identify the biggest database "TestFF", which has data file size of 10 Gb.
Also, form this view you see that all databases are "Online" and some log files have more VLFs than others.

When we scroll, we see Used space and percentage of free space. What auto growth settings each file has and what are the file sizes limits.
Performance troubleshooting starts right here. You can notice that  Average Reading I/O for "TestFF" database files is more then 10 times slower then for all other files. If we scroll back you will see that it is because "TestFF" files are located on drive "D:\", while all others are stored on "C:\" drive.
Next you see that more than 99% of my CPU is used by operations associated with "AdventureWorks2014" database. That means if I have CPU bottleneck, I have to look there.
When you scroll more to the left you'll see more database related settings. Just do not want to waste time and space to describe all of them.

D. After the main data set will be a smaller one: Aggregated statistics for all local drives used by SQL Server. These statistics are extracted by following query:

SELECT vs.volume_mount_point as [Mount Point]
, CAST(ROUND(( SUM(s.io_stall_read_ms) / 
 ( 1.0 + SUM(s.num_of_reads) ) ),3) as FLOAT) as [Avg Read Wait, ms]
, CAST(ROUND(( SUM(s.io_stall_write_ms) 
 / ( 1.0 + SUM(s.num_of_writes) ) ),3) as FLOAT) as [Avg Write Wait, ms]
, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Volume Size (GB)]
, CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Volume Size (GB)]
, CAST(CAST(vs.available_bytes AS FLOAT)
 / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Volume Space Free %]
, vs.file_system_type
, vs.logical_volume_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) as s
CROSS APPLY sys.dm_os_volume_stats(s.database_id, s.[file_id]) AS vs
GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name
 , vs.total_bytes, vs.available_bytes
OPTION (RECOMPILE);

That query produces following result:

As expected, "D:\" drives looks much slower than "C:\", but "C:\" has much less free space.

E. If you have availability groups you will get one more data set, which would provide you all AG settings and current health and synchronization conditions.

2. . "Ctrl+5" with "database" as a parameter.
As a parameter you can use database name or database id.
You just have to highlight database name or id in the Text window and click "Ctrl+5". If database name has spaces or other not alpha-numeric characters it has to be covered in quotations:

A. That call will originate querying only attributes and settings only for that database.
It is handy when you have 1000-s of databases, but want to look only at the particular one.

B. Second data set will be similar drive information as it was without parameters.

C. Third data set will return database partition parameters if any:

USE [AW_BigCopy];
IF EXISTS (SELECT TOP 1 1 FROM sys.partition_schemes with (NOLOCK))
SELECT ps.name as Partition_Schema, pf.name as Partition_Function
, pf.modify_date as Last_Modified
, CASE pf.boundary_value_on_right WHEN 0 THEN 'LEFT' ELSE 'RIGHT' END as Function_Type
, R1.value as Min_Border_Value, R2.value as Max_Border_Value
, ds.destination_id as Partition_Order
, FG.name as [FileGroup_Name]
, SUM(IsNull(AU.total_pages,0)) as total_pages
, SUM(IsNull(AU.used_pages,0)) as used_pages
, SUM(IsNull(AU.data_pages,0)) as data_pages
, sf.name as [File_Name], sf.filename as Physical_File_Name
FROM sys.partition_schemes as ps with (NOLOCK)
INNER JOIN sys.destination_data_spaces as ds with (NOLOCK) 
 ON ps.data_space_id = ds.partition_scheme_id
INNER JOIN sys.partition_functions as pf with (NOLOCK) 
 ON pf.function_id = ps.function_id
INNER JOIN sys.filegroups AS FG with (NOLOCK) 
 ON FG.data_space_id = ds.data_space_id
INNER JOIN sys.sysfiles AS sf with (NOLOCK) 
 ON sf.groupid = ds.data_space_id
LEFT JOIN sys.partition_range_values as R1 with (NOLOCK) 
 ON R1.function_id = pf.function_id and R1.boundary_id + 1 = ds.destination_id
LEFT JOIN sys.partition_range_values as R2 with (NOLOCK) 
 ON R2.function_id = pf.function_id and R2.boundary_id = ds.destination_id
LEFT JOIN sys.allocation_units AS AU with (NOLOCK) 
 ON AU.data_space_id = ds.data_space_id
GROUP BY  ps.name, pf.name, pf.modify_date, pf.boundary_value_on_right
 , R1.value, R2.value, ds.destination_id, FG.name, sf.name, sf.filename
ORDER BY ps.name, ds.destination_id
OPTION (RECOMPILE);

D. Fourth data set uncover ALL database settings using following query:

SELECT tt.DBProperty, tt.DBPropertyValue
FROM sys.databases as t with (NOLOCK)
CROSS APPLY (VALUES (CAST(database_id as NVARCHAR), 'Database Id')
,(name, 'Datanase Name')
,(state_desc COLLATE SQL_Latin1_General_CP1_CI_AS, 'DB State')
,(user_access_desc COLLATE SQL_Latin1_General_CP1_CI_AS, 'User Access')
,(recovery_model_desc COLLATE SQL_Latin1_General_CP1_CI_AS, 'DB recovery model')
,(collation_name COLLATE SQL_Latin1_General_CP1_CI_AS, 'DB Collation')
,(log_reuse_wait_desc COLLATE SQL_Latin1_General_CP1_CI_AS, 'Log Reuse')
,(page_verify_option_desc COLLATE SQL_Latin1_General_CP1_CI_AS, 'PAGE_VERIFY')
,(CASE is_read_only WHEN 0 THEN 'NO' ELSE 'YES' END, 'READ_ONLY')
,(CASE is_in_standby WHEN 0 THEN 'NO' ELSE 'YES' END, 'DB IN Standby')
,(CASE is_cleanly_shutdown WHEN 0 THEN 'NO' ELSE 'YES' END, 'DB Is cleanly shutdown')
,(CASE is_encrypted WHEN 0 THEN 'NO' ELSE 'YES' END, 'DB_ENCRYPTED')
,(CASE is_auto_shrink_on WHEN 0 THEN 'NO' ELSE 'YES' END, 'AUTO_SHRINK')
,(CASE is_ansi_null_default_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'ANSI_NULL_DEFAULT')
,(CASE is_ansi_nulls_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'ANSI_NULLS')
,(CASE is_ansi_warnings_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'ANSI_WARNINGS')
,(CASE is_ansi_padding_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'ANSI_PADDING')
,(CASE is_arithabort_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'Arithmetic Abort Enabled')
,(CASE is_quoted_identifier_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'QUOTED_IDENTIFIER')
,(CASE is_auto_create_stats_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'AUTO_CREATE_STATISTICS')
,(CASE is_auto_update_stats_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'AUTO_UPDATE_STATISTICS')
,(CASE is_auto_update_stats_async_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'AUTO_UPDATE_STATISTICS_ASYNC')
,(CASE is_read_committed_snapshot_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'READ_COMMITTED_SNAPSHOT')
,(CASE snapshot_isolation_state WHEN 0 THEN 'NO' ELSE 'YES' END, 'ALLOW_SNAPSHOT_ISOLATION')
,(CASE is_concat_null_yields_null_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'CONCAT_NULL_YIELDS_NULL')
,(CASE is_recursive_triggers_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'RECURSIVE_TRIGGERS')
,(CASE is_parameterization_forced WHEN 0 THEN 'NO' ELSE 'YES' END, 'FORCED_PARAMETRIZATION')
,(CASE is_db_chaining_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'DB_CHAINING')
,(CASE is_numeric_roundabort_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'NUMERIC_ROUNDABORT')
,(CASE is_trustworthy_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'TRUSTWORTHY')
,(CASE is_auto_close_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'AUTO_CLOSE')
,(CASE is_date_correlation_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'DATE_CORRELATION_OPTIMIZATION')
,(CASE is_cdc_enabled WHEN 0 THEN 'NO' ELSE 'YES' END, 'CHANGE_DATA_CAPTURE Enabled')
,(CASE is_fulltext_enabled WHEN 0 THEN 'NO' ELSE 'YES' END, 'FULL-TEXT Enabled')
,(CASE is_supplemental_logging_enabled WHEN 0 THEN 'NO' ELSE 'YES' END, 'SUPPLEMENTAL_LOGGING Enabled')
,(CASE is_broker_enabled WHEN 0 THEN 'NO' ELSE 'YES' END, 'DB_BROKER Enabled')
,(CASE is_honor_broker_priority_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'HONOR_BROKER_PRIORITY')
,(CASE is_local_cursor_default WHEN 0 THEN 'NO' ELSE 'YES' END, 'CURSOR_DEFAULT')
,(CASE is_cursor_close_on_commit_on WHEN 0 THEN 'OFF' ELSE 'ON' END, 'CURSOR_CLOSE_ON_COMMIT')
,(CASE is_subscribed WHEN 0 THEN 'NO' ELSE 'YES' END, 'REPLICATION_SUBSCRIPTION_DB')
,(CASE is_published WHEN 0 THEN 'NO' ELSE 'YES' END, 'REPLICATION_PUBLICATION_DB')
,(CASE is_merge_published WHEN 0 THEN 'NO' ELSE 'YES' END, 'MERGE_REPLICATION_PUBLICATION_DB')
,(CASE is_distributor WHEN 0 THEN 'NO' ELSE 'YES' END, 'REPLICATION_DISTRIBUTION_DB')
,(CASE is_sync_with_backup WHEN 0 THEN 'NO' ELSE 'YES' END, 'MARKED_FOR_REPLICATION_BACKUP_SYNC')
,(CASE is_master_key_encrypted_by_server WHEN 0 THEN 'NO' ELSE 'YES' END, 'DB_ENCRYPTED_MASTER_KEY')
) tt (DBPropertyValue, DBProperty)
WHERE name = 'AW_BigCopy'
OPTION (RECOMPILE);

It returns data in following format:

E. Fifth data set returns all backups associated with the database. Here is the query:

;WITH BU as (SELECT backup_start_date, backup_finish_date,
CASE type
 WHEN 'L' THEN 'LOG'
 WHEN 'D' THEN 'FULL'
 WHEN 'I' THEN 'DIFF'
 ELSE 'N/A'
END as [Type],
 CAST(CAST(ROUND(backup_size / 1048576.,3) 
  as DECIMAL(16,3)) as VARCHAR(16)) AS BackupSizeMb,
 CAST(CAST(ROUND(compressed_backup_size / 1048576.,3) 
  as DECIMAL(16,3)) as VARCHAR(16)) AS CompressedMb,
 f.physical_device_name as [Backup File Full Name]
, backup_set_id
FROM msdb.dbo.backupset as s
LEFT JOIN msdb.dbo.backupmediafamily as f 
 ON s.media_set_id = f.media_set_id
WHERE database_name = 'AW_BigCopy'
)
SELECT backup_start_date, backup_finish_date
, [Type], [Backup File Full Name],
RIGHT(SPACE(16) + CASE WHEN Len(BackupSizeMb) > 7
 THEN CASE WHEN Len(BackupSizeMb) > 10
  THEN LEFT(BackupSizeMb, LEN(BackupSizeMb) - 10) + ',' 
   + SUBSTRING(BackupSizeMb, LEN(BackupSizeMb) - 10, 3) 
   + ',' + RIGHT(BackupSizeMb, 7)
  ELSE LEFT(BackupSizeMb, LEN(BackupSizeMb) - 7) + ',' 
   + RIGHT(BackupSizeMb, 7) END 
 ELSE BackupSizeMb END, 16) as BackupSizeMb,
RIGHT(SPACE(16) + CASE WHEN Len(CompressedMb) > 7
 THEN CASE WHEN Len(CompressedMb) > 10
  THEN LEFT(CompressedMb, LEN(CompressedMb) - 10) + ',' 
   + SUBSTRING(CompressedMb, LEN(CompressedMb) - 10, 3) 
   + ',' + RIGHT(CompressedMb, 7)
  ELSE LEFT(CompressedMb, LEN(CompressedMb) - 7) + ',' 
   + RIGHT(CompressedMb, 7) END 
 ELSE CompressedMb END, 16) as CompressedMb
FROM BU
ORDER BY backup_set_id DESC
OPTION (RECOMPILE);

And it produces following result:

That is very useful when you want to make sure that your database backups are OK.

F. Sixth data set would return Availability Group information for the database.

3. "Ctrl+5" with extra parameters.
If you highlight just one letter (one of the following) and press "Ctrl+5" the script will return you the list of databases with all parameters, but will sort it or filter it accordingly to the specified parameter:

• N - Sorts Databases by Name;
• D - Sorts Databases by Creation Date/Time;
• S - Sorts Database files by Size (Desc);
• U - Sorts Database files by Usage percentage (Desc);
• W - Sorts Database files by Write/Read Waits (Desc);
• C - Filters Databases, which are not in Current compatibility mode;
• B - Filters Databases, which are in BULK recovery model;
• F - Filters Databases, which are in FULL recovery model;
• L - Filters Databases, which are in SIMPLE recovery model;
• M - Filters Databases, which are not in MULTI_USER mode;
• O - Filters Databases, which are not ONLINE;
• R - Filters Databases, which are in Read-Only Mode;

And currently that is it about "Ctrl+5" script.

You do not have to memorize all parameters, all of them will be available if you just switch to "Messages" tab in your SSMS after you pressed "Ctrl+5". You'll see something like this:

In order to better understand on how to use the script: watch the video and read my overview blog posts.
And now you are fully ready to download the entire script from here.

If you have any questions, requests, suggestions or want to report a bug, you can leave your comment below or under the video.