Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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.

;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

Comments

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

Loading comments...