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) >= 2
) Drives
)
) Drive
ORDER BY OrderBy, drive_letter
On my dev box it looks like this:
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)
10 Comments. Leave new
I am suggesting one correction in script.
1)If data and log files are on same drive, but in different folder, this script will not show that database in result.
2)If one database having 1 data file and 1 log file in same folder of c:, now 2nd log file(or data file) is created on D:, still this script will show that database in result.
So, I think we can write something like this:
SELECT MIN(drive_letter) drive_letter, name
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’)
–ORDER BY DB_NAME(database_id)
) a
GROUP BY a.name
HAVING MIN(drive_letter)= MAX(drive_letter)
Good finding Harsh. Thanks for sharing.
I like to just list them all so i can check for myself, and also see some read/write statistics. That was I can also detect if a specific disk is slow.
select db_name(mf.database_id) as databaseName, mf.physical_name,
case num_of_reads when 0 then 0 else (io_stall_read_ms / num_of_reads * 1.0) end as avg_ms_read_stall,
case num_of_writes when 0 then 0 else (num_of_bytes_written / num_of_writes * 1.0) end as avg_bytes_per_write,
case num_of_writes when 0 then 0 else (io_stall_write_ms / num_of_writes * 1.0) end as avg_ms_write_stall,
(io_stall / 1000.0 ) as total_io_stall_sec,
size_on_disk_bytes / 1024 / 1024 as size_on_disk_MB
from sys.dm_io_virtual_file_stats(null,null) as divfs
join sys.master_files as mf
on mf.database_id = divfs.database_id
and mf.file_id = divfs.file_id
Good one thyrr.
If you’re using mounted volumes its perfectly accrptable that the files have the same drive letter. In this case the file path should be different and should be separate mounted volumes
Good Point Perry.
Where is the final script?
Original script is fine. Do you have mount points?
Yes , what to do when we ‘ve mountpoints
Yes, Do we need to modify the code if we are dealing with mountpoints.