THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Utility Queries–Database Files, (and Filegroups)

It has been a while since I last posted a utility query, and today, to avoid other work I am supposed to be doing, I decided to go ahead and work on another post.  Today, I went ahead and worked on a server configuration type query. One query I find I use pretty often is the following one that lists the files in the database. In this blog I will include 3 queries.  The first will deal with files and databases, and the second runs in a database to see the files and their filegroups (If there is an easy way to get the filegroups at a server level, I am not sure of it…let me know).

Database Files, All Databases – File Level (Download source)

It is a pretty simple query, and it returns the following columns. (A value of '@TOTAL' indicates that the row is a summary row, and some file_types will not report a file size. ):

  • database_name – The name of the database
  • database_file_name – The file name that was set when the file was added to the database (the logical name, not the physical name)
  • size_in_kb – The size of the file in kilobytes, such that it matches the file size in the Windows Explorer
  • size_in_mb – The size of the file in megabytes, a size that is more typical the people want to see
  • size_in_gb – The size of the file in gigabytes, useful when looking at really large files
  • file_type – How the file is used in the server
  • filesystem_drive_letter – the drive letter where the file is located
  • filesystem_file_name – name of the physical file
  • filesystem_path – the path where the files are located.

--Get the files and total size of files for all databases

SELECT  --the name of the database
        CASE WHEN GROUPING(DB_NAME(database_id)) = 1 THEN '@TOTAL'
             ELSE DB_NAME(database_id)
        END AS database_name ,

        --the logical name of the file
        CASE WHEN GROUPING(master_files.name) = 1 THEN '@TOTAL'
             ELSE master_files.name
        END AS database_file_name ,

        --the size of the file is stored in # of pages
        SUM(master_files.size * 8.0) AS size_in_kb,
        SUM(master_files.size * 8.0) / 1024.0 AS size_in_mb,
        SUM(master_files.size * 8.0) / 1024.0 / 1024.0 AS size_in_gb,

        --the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ''
             ELSE MAX(master_files.type_desc)
        END AS file_type , 
       
        --the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ''
             ELSE MAX(UPPER(SUBSTRING(master_files.physical_name, 1, 1)))
        END AS filesystem_drive_letter ,              


       --thanks to Phillip Kelley from http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql
       --for the REVERSE code to get the filename and path.

        --the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ''
             ELSE MAX(REVERSE(LEFT(REVERSE(master_files.physical_name),
                     CHARINDEX('\', REVERSE(physical_name)) - 1)))
        END AS filesystem_file_name ,

        --the path of the file only
       cASE WHEN GROUPING(master_files.name) = 1 THEN ''
             ELSE MAX(REPLACE(master_files.physical_name,
                REVERSE(LEFT(REVERSE(master_files.physical_name),
                             CHARINDEX('\', REVERSE(physical_name)) - 1)), ''))
             END AS filesystem_path

FROM    sys.master_files
GROUP BY DB_NAME(database_id) , --the database and filegroup and the file (all of the parts)
         master_files.name WITH rollup
ORDER BY database_name, database_file_name

Single Database By Filegroup (Download source)

Edited: Added code based on one of the comments here: http://www.sqlblog.lv/2011/05/ka-apskatit-datu-bazes-failu-izmeru-un.html.  His post does all db’s with sizing, but I preferred to have this query only work on one database. I added columns for available space, used space, as well as on disk space

In the second query, it will, for one database, list all of the row and log filegroups and their files. Like the previous query, it may list filegroups that have a 0 size for types like full text. It uses sys.database_files for the files. This has one downside, and that is that if the database is read only, it is possible that the results will not be correct and will reflect a previous version of the metadata. Use master_files if you want to get current values, but there is no guarantees that it will match the filegroups. 

It will return:

  • filegroup_name – The name of the filegroup in the database
  • database_file_name – The file name that was set when the file was added to the database (the logical name, not the physical name)
  • size_in_kb – The size of the file in kilobytes, such that it matches the file size in the Windows Explorer
  • size_in_mb – The size of the file in megabytes, a size that is more typical the people want to see (Commented Out)
  • size_in_gb – The size of the file in gigabytes, useful when looking at really large files
  • used_size_in_kb – The amount of the file that has data allocated, in kilobytes
  • used_size_in_mb – The amount of the file that has data allocated in megabytes, a size that is more typical the people want to see (Commented Out)
  • used_size_in_gb – The amount of the file that has data allocated, in gigabytes, useful when looking at really large files
  • available_size_in_kb – The amount of free space in kilobytes, such that it matches the file size in the Windows Explorer
  • available_size_in_mb – The amount of free space in megabytes, a size that is more typical the people want to see (Commented Out)
  • available_size_in_gb – The amount of free space in gigabytes, useful when looking at really large files
  • size_on_disk_kb – The amount of space the file takes in the file system (reported from the DMVs)
  • file_type – How the file is used in the server
  • filesystem_drive_letter – the drive letter where the file is located
  • filesystem_file_name – name of the physical file
  • filesystem_path – the path where the files are located.

SELECT  --the name of the database

               --the name of the filegroup (or Log for the log file, which doesn't have a filegroup)
               CASE WHEN GROUPING(filegroups.name) = 1 THEN '@TOTAL'
                         WHEN filegroups.name IS NULL THEN 'LOGS'
                         ELSE filegroups.name
                END AS filegroup_name ,
       
               --the logical name of the file
               CASE WHEN GROUPING(database_files.name) = 1 THEN '@TOTAL'
                        ELSE database_files.name
               END AS database_file_name ,

               --the size of the file is stored in # of pages
               SUM(database_files.size * 8.0) AS size_in_kb,
               --SUM(database_files.size * 8.0) / 1024.0 AS size_in_mb,
               SUM(database_files.size * 8.0) / 1024.0 / 1024.0 AS size_in_gb,
              
               SUM(FILEPROPERTY(database_files.NAME,'SpaceUsed') * 8.0) AS used_size_in_kb,
               --SUM(FILEPROPERTY(database_files.NAME,'SpaceUsed') * 8.0)/ 1024.0  AS used_size_in_mb,
               SUM(FILEPROPERTY(database_files.NAME,'SpaceUsed') * 8.0) / 1024.0 / 1024.0 AS used_size_in_gb,                             

               SUM((database_files.size - FILEPROPERTY(database_files.NAME,'SpaceUsed')) * 8.0) AS available_size_in_kb,
               --SUM((database_files.size - FILEPROPERTY(database_files.NAME,'SpaceUsed')) * 8.0)/ 1024.0  AS available_size_in_mb,
               SUM((database_files.size - FILEPROPERTY(database_files.NAME,'SpaceUsed')) * 8.0) / 1024.0 / 1024.0 AS available_size_in_gb,  

               SUM(DIVFS.size_on_disk_bytes/1024.0) AS size_on_disk_kb,
              
              --the physical filename only
              CASE WHEN GROUPING(database_files.name) = 1 THEN ''
                        ELSE MAX(database_files.type_desc)
               END AS file_type , 

               --the physical filename only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ''
                        ELSE MAX(UPPER(SUBSTRING(database_files.physical_name, 1, 1)))
               END AS filesystem_drive_letter ,        
       
              --thanks to Phillip Kelley from http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql

               --the physical filename only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ''
                         ELSE MAX(REVERSE(LEFT(REVERSE(database_files.physical_name), CHARINDEX('\', REVERSE(database_files.physical_name)) - 1)))
                END AS filesystem_file_name ,

                --the path of the file only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ''
                         ELSE MAX(REPLACE(database_files.physical_name, REVERSE(LEFT(REVERSE(database_files.physical_name),
                                                                                                                   CHARINDEX('\', REVERSE(database_files.physical_name)) - 1)), ''))
                END AS filesystem_path
FROM    sys.database_files --use sys.master_files if the database is read only and you want to see the metadata that is the database
             --log files do not have a filegroup
                     LEFT OUTER JOIN sys.filegroups
                             ON database_files.data_space_id = filegroups.data_space_id
                    Left Join sys.dm_io_virtual_file_stats(DB_ID(), DEFAULT) DIVFS
                            On database_files.file_id = DIVFS.file_id                        
GROUP BY  filegroups.name ,
                 database_files.name WITH ROLLUP
ORDER BY     --the name of the filegroup (or Log for the log file, which doesn't have a filegroup)
                 CASE WHEN GROUPING(filegroups.name) = 1 THEN '@TOTAL'
                          WHEN filegroups.name IS NULL THEN '@TOTAL-SortAfter'
                          ELSE filegroups.name
                  END,
                  database_file_name

Hope these queries help out sometime.  More on the way as I finish up other projects!

Published Sunday, July 01, 2012 10:33 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul B said:

Thanks! These queries will help me size up requirements for a DR site.

July 1, 2012 11:26 PM
 

adisapir said:

A very helpful article!

If I may, I've written a free tool which visualizes the entire database->filegroups->files consumption on your storage. It's called "Database File Explorer" and can be downloaded from here: http://www.doccolabs.com/products_dbfileexplorer.html

Hope it helps!

July 2, 2012 4:06 AM
 

Jānis said:

Its nice query, but should say I like mine more this time, as it shows also empty space in each file + takes in account sparse files :).

Unfortunately (or fortunately) my blog is in Latvian, but SQL is still SQL http://www.sqlblog.lv/2011/05/ka-apskatit-datu-bazes-failu-izmeru-un.html (the one with global temptable)

July 3, 2012 4:31 AM
 

drsql said:

Janis:

I will change my second query to include the free space for sure. I will also add the foreachdb call to loop through if desired.

For your query (and my change), you sould probably include the database state in your query to handle databases that aren't online (which my manual version of accessing the db will do quite loudly.)

No matter what I change, I will direct people to your query from mine (especially if they speak Latvian :))

July 3, 2012 10:36 AM
 

cviloria said:

nice query and very useful if the database is not online and you need the logical-physical structure.

May 20, 2013 6:39 PM
 

cviloria said:

nice query and very useful if the database is not online and you need the logical-physical structure.

May 21, 2013 12:19 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement