Skip to main content
How can we help?
< All Topics
Print

Data drive with incorrect cluster size

It is best practise to format the data drive with 64 KB NTFS cluster size.

To query the Performance Store database for data 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 @temp 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 ('data', 'data and log')
and c.block_size_bytes != 65536
and not exists
(
	select 1
	from dbo.v_latest_stats s
	where s.name = 'Data 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.

Table of Contents