Search This Blog

SQL to check Data and Log Files are on same drive or not

The below query help us in finding the physical existence of data and log files are on same drive or not of all the database in a SQL Instance.
;WITH LogCTE AS 
SELECT DISTINCT LD.Database_Name 
FROM 
SELECT DB_NAME(database_id) AS [Database_Name], LEFT(Physical_Name, 1) AS [Drive_Letter] FROM sys.master_files WHERE type_desc = 'LOG' AND database_id > 4 
) AS LD 
 INNER JOIN 
  sys.master_files mf 
   ON LD.Drive_Letter = LEFT(mf.physical_name, 1) and mf.name=LD.Database_Name 
WHERE 
 mf.database_id > 4 
 AND mf.type_desc = 'ROWS' 
SELECT Database_Name into #FailedDatabase FROM LogCTE 

IF ((SELECT COUNT(*) FROM #FailedDatabase) = 0) 
 BEGIN 
  SELECT 'Data And Log File Seperated' 
 END 
ELSE 
 BEGIN 
    SELECT Database_Name 'Data and Log Files are not Seperated' FROM #FAILEDDATABASE 
 END 

 DROP TABLE #FailedDatabase