Log drive with incorrect cluster size

Alert will be raised for log drives with a cluster size different from either 8K or 64K.

To query the Performance Store database for log drives with incorrect cluster sizes, use:

declare @temp table
(
	post_time datetime2(0) not null,
	server_name nvarchar(128) not null,
	drive_letter char(1) null,
	mount_point nvarchar(1024) null
)

insert into @temp (post_time, server_name, drive_letter, mount_point)
select distinct c.post_time, c.server_name, c.drive_letter, c.mount_point
from dbo.v_capacity c
where c.post_time in
(
	select top 1 c1.post_time
	from dbo.v_capacity c1
	order by c1.post_time desc
)

select c.*
from
(
    select c.post_time, c.server_name, c.drive_letter, c.mount_point, c.block_size_bytes
    , s1.default_drive_type
    from dbo.v_capacity c
    inner join
    (
        select distinct c.post_time, c.server_name, c.drive_letter, c.mount_point
        , case
			when c.drive_letter is not null and left(s.default_data_path, 1) = c.drive_letter and left(s.default_log_path, 1) = c.drive_letter and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_data_path not like t.mount_point + '%') and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_log_path not like t.mount_point + '%') then 'data and log'
			when c.drive_letter is not null and left(s.default_data_path, 1) = c.drive_letter and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_data_path not like t.mount_point + '%') and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_log_path not like t.mount_point + '%') then 'data'
			when c.drive_letter is not null and left(s.default_log_path, 1) = c.drive_letter and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_data_path not like t.mount_point + '%') and not exists (select 1 from @temp t where t.server_name = s.computer_name and s.default_log_path not like t.mount_point + '%') then 'log'
			when c.drive_letter is null and left(s.default_data_path, len(c.mount_point)) = c.mount_point and left(s.default_log_path, len(c.mount_point)) = c.mount_point then 'data and log'
			when c.drive_letter is null and left(s.default_data_path, len(c.mount_point)) = c.mount_point then 'data'
			when c.drive_letter is null and left(s.default_log_path, len(c.mount_point)) = c.mount_point then 'log'
        end default_drive_type
        from dbo.v_capacity c
        inner join dbo.v_servers s with (nolock) on s.computer_name = c.server_name
    ) s1 on s1.post_time = c.post_time and s1.server_name = c.server_name and ((s1.drive_letter is not null and s1.drive_letter = c.drive_letter) or (s1.mount_point is not null and s1.mount_point = c.mount_point))
) c
where c.default_drive_type in ('log', 'data and log')
and c.block_size_bytes not in (8192, 65536)
and c.post_time in
(
    select top 1 c1.post_time
    from dbo.v_capacity c1
    order by c1.post_time desc
)
and not exists
(
    select 1
    from dbo.v_latest_stats s
    where s.name = 'Log drive with incorrect cluster size' and s.server_name = c.server_name and s.object_name = isnull(convert(nvarchar(1024), c.drive_letter), c.mount_point)
)
order by 1, 2

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.