SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

T-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.

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 
  sys.master_files mf 
   ON LD.Drive_Letter = LEFT(mf.physical_name, 1) and mf.name=LD.Database_Name 
 mf.database_id > 4 
 AND mf.type_desc = 'ROWS' 
SELECT Database_Name into #FailedDatabase FROM LogCTE 

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

 DROP TABLE #FailedDatabase

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.


Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...