SQL SERVER – Script: Knowing Data and Log Files Are On the Same Drive

April 29, 2015

Last week it was wonderful to spend time at the GIDS conference to meet all the enthusiastic developers. Had fun preparing for the session and the number of people who walked to me and say they read this blog makes me really humble and motivated to write more. I love attending and presenting at these conferences because I get an opportunity to meet people and at many times able to answer real world problems. Let’s learn Script: Knowing Data and Log Files Are On the Same Drive.

During one of my sessions on performance, I mentioned about the need to keep the data files and log files on different drives. I showed the impact of placing log and/or data files on a slower drive and we had loads of fun learning. After the session, one of the attendees walked up and asked a simple question. He said he had 100’s of databases running in their environment. He wanted to know which of these databases were having both the data files and log files on the same drive.

My first instinct was to search the blog to see if I had already written about it, but to my surprise it was not. So with a little bit of query to DMVs and metadata tables, here is what I came up with.

SELECT SERVERPROPERTY('machinename') AS 'Server Name',
ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename'))  AS 'Instance Name',
name,
drive_letter AS 'Drive Letter',
Comments, Path
FROM
(
(
SELECT DISTINCT
UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
N'Device holds both tempdb and user database objects' AS 'Comments',
DB_NAME(database_id) [name],
1 AS OrderBy
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) = 'tempdb'
AND UPPER(LEFT(LTRIM(physical_name),2)) IN
(
SELECT UPPER(LEFT(LTRIM(physical_name),2))
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'tempdb', N'master', N'msdb',
 N'adventureworks', N'adventureworksdw', N'model')
)
)
UNION
(
SELECT drive_letter, path,
N'Device holds both data and log objects' AS 'Comments', name,
2 AS OrderBy
FROM
(
SELECT drive_letter, name, Path
FROM
(
SELECT DISTINCT UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
TYPE, DB_NAME(database_id) [name] FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) 
NOT IN (N'master', N'msdb', N'tempdb', N'adventureworks', N'adventureworksdw', N'model')
) a
GROUP BY drive_letter, a.name, path
HAVING COUNT(1) &amp;gt;= 2
) Drives
)
) Drive
ORDER BY OrderBy, drive_letter

On my dev box it looks like this:

SQL SERVER - Script: Knowing Data and Log Files Are On the Same Drive data-log-same-drive-01

I am sure if you run the script, it will show something similar to this. Do let me know if you ever needed such a script and were not able to get the same? I would love to know, are there some simple daily scripts that will make your life easy and you don’t have them handy? Let me know, we will try to make them into the blog somehow.

Reference: Pinal Dave (https://blog.sqlauthority.com)