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
|