Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

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

19 October,2012 by Jack Vamvas

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



Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer