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

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in T-SQL, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s