I'm trying to write a query for a report to show how much free space is in our data files so we can plan storage for the future. I'm pulling data out of sys.database_files, and I can narrow it down to just type 0 (Rows) data - we're not worried about log growth at this point.

The problem I'm having is that a number of databases have a full text catalog. The Full Text Catalog file is also displaying as type 0. We want to exclude these from the report as well. Is there anyway we can tell from sys.database_files (or join back to that) so we can just see the data files?

I notice any newly created Full Text Catalog files in SQL Server 2016 will have type = 4, but that doesn't really help me on 2014 (or upgraded databases in 2016).


I believe type = 4 only applies to pre-2008 fulltext files which have been upgraded, because since then there has been no way to create separate fulltext files -- only separate filegroups. (See type = 4 doc for SQL 2012 at https://msdn.microsoft.com/en-gb/library/ms174397(v=sql.110).aspx which confusingly says the same as for 2016 except version number.)

Something like http://www.connectsql.com/2011/01/sql-server-how-to-get-physical-path-of.html may technically do what you are looking for, but won't actually be relevant for your report.

| improve this answer | |
  • Thanks! I didn't even look at the older versions - dumb of me. These databases were upgraded from 2005 so I might need to look at dropping and recreating the full text catalog. – Greg Feb 1 '17 at 19:11
  • I'm still a bit confused. According to the MSDN Article: Once a full-text catalog is upgraded or any new ones created in SQL 2012 (or 2016) they will report a file type of 0. That explains why I'm seeing 0, but my new question is how is it possible to have a file type = 4? Once you get the database on 2012 won't it upgrade and make file type = 0? – Greg Feb 1 '17 at 23:31
  • Yeah I missed the "or upgraded to" bit. If that's correct then that type = 4 bit of documentation should probably just be removed from later versions as it's completely irrelevant to them. – T.H. Feb 2 '17 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.