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