SQL Server – Check Data and Log files are on separate drives

A common requirement on SQL Server environments is to maintain the SQL Server database data files and log files on separate drives – mapped to different IO channels. There  is much evidence and discussion on this performance issue.

 The DBA needs to ensure this policy is maintained.  In a busy environment this can be challenging where there are many different DBAs and third party vendor products. An easy way to manage this process is by including the SQL code below into your nightly systems batch runs.

 The script checks to see if a data and log file from  a database are on the same drive . The first column returns the database name, the second column flags if a data and log file are on the same drive, the third column is the drive letter.

select 
DB_NAME(mf.database_id) as [db_name], 
checkDBLocation = CASE WHEN ( 
select count(*) from sys.master_files as m1  
where  m1.type_desc IN ('LOG')  and mf.type_desc IN ('ROWS') 
AND substring(m1.physical_name,1,1) = substring(mf.physical_name,1,1) 
AND m1.database_id = mf.database_id 
) > 0 THEN '1' 
ELSE '0' 
END, 
substring(physical_name,1,1)  as 'Drive letter'
from sys.master_files as mf


--In the situation of MOUNTED DRIVES where you might have
--d:\data , d:\logs , d:\temp 
--this version will compare the drive letter and upto the second "\"

select 
DB_NAME(mf.database_id) as [db_name], 
checkDBLocation = CASE WHEN ( 
select count(*) from sys.master_files as m1  
where  m1.type_desc IN ('LOG')  and mf.type_desc IN ('ROWS') 
AND SUBSTRING(m1.physical_name,1,CHARINDEX('\',m1.physical_name,4)) = SUBSTRING(mf.physical_name,1,CHARINDEX('\',m1.physical_name,4))
AND m1.database_id = mf.database_id 
) > 0 THEN '1' 
ELSE '0' 
END, 
substring(physical_name,1,1)  as 'Drive letter'
from sys.master_files as mf

Related Posts

SQL Server DBA Top 10 automation tasks

Powershell Scripts for DBA