List of all Data and Log Files of a SQL Instance

Ever faced a sitaution where you had to find out all the database files (Data and Log) of a SQL Instance?

I came across this situaion many times, especially when I am working with a new SQL Instance which was taken over by my team OR when the previous admin of that box has left the company and its now my turn to maintain / administer that instance.

In order to list the files, we just need to query the system table sysaltfiles.  The picture below is a screen show that I ran earlier today on one of my SQL Instances.


This  query can be further tweaked to get only the data files or only the log files by adding where clause and filter by griupid column. Groupid = 1 indicates it is a data file and groupid = 0 indicates it is a log file.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

Leave a Reply