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

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.

Solarwinds
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:

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)

Solarwinds
, , ,
Previous Post
Interview Question of the Week #017 – Performance Comparison of Union vs Union All
Next Post
SQL SERVER – Recovering from Snapshots – Notes from the Field #078

Related Posts

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)

    Reply
  • 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

    Reply
  • perry whittle
    May 2, 2015 2:19 pm

    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

    Reply
  • Where is the final script?

    Reply
  • Yes , what to do when we ‘ve mountpoints

    Reply
  • Yes, Do we need to modify the code if we are dealing with mountpoints.

    Reply

Leave a Reply

Menu