This post contains a script I’ve put together to get database sizes in SQL Server along with some additional info like file counts per file types and their sizes in GB.
For the whole instance
The following query retrieves the following information, one record per database:
- Database – the name of the database
- DatabaseState – the state of the database (duh)
- DataFiles – number of data files (.mdf and .ndf)
- DataFilesSizeGB – the total size of the data file(s) in GB
- LogFIles – number of transaction log files (.ldf) – note: 99% of the time there is no valid reason to have more than one transaction log file per database.
- LogFileSizeGB – the total size of the transaction log file(s) in GB
- FILESTREAMContainers – the number of FILESTREAM containers – at the disk level these are actually directories
- FSContainersSizeGB – the total size of the FILESTREAM container(s) in GB
- DatabaseSizeGB – the total size of the database in GB
You might ask yourself “isn’t the internet full of scripts that do the same thing?”, and the answer is “yes, but there’s a catch”.
In this case the catch being that, although there are some great examples out there, the ones I’ve found rely solely on sys.master_files so they end up omitting FILESTREAM container sizes altogether.
Since FILESTREAM size information is not returned by the system-wide view sys.master_files, but by the database-level view sys.database_files, I had to use a temp table and populate it via a cursor with the FILESTREAM info for each database with such containers.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | /*Make sure temp table doesn't exist*/ IF OBJECT_ID(N'tempdb.dbo.#FSFiles', N'U') IS NOT NULL DROP TABLE #FSFiles; /*Create temp table*/ CREATE TABLE #FSFiles ( [DatabaseID] [SMALLINT] NULL, [FSFilesCount] [INT] NULL, [FSFilesSizeGB] [NUMERIC](15, 3) NULL); /*Cursor to get FILESTREAM files and their sizes for databases that use FS*/ DECLARE @DBName NVARCHAR(128), @ExecSQL NVARCHAR(MAX); DECLARE DBsWithFS CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT DB_NAME(database_id) FROM sys.master_files WHERE [type] = 2; OPEN DBsWithFS; FETCH NEXT FROM DBsWithFS INTO @DBName; WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecSQL = N'USE ['+@DBName+N']; INSERT INTO #FSFiles ([DatabaseID],[FSFilesCount],[FSFilesSizeGB]) SELECT DB_ID(), COUNT([type]), CAST(SUM([size] * 8 / 1024.00 / 1024.00) AS NUMERIC(15, 3)) FROM sys.database_files WHERE [type] = 2 GROUP BY [type];'; EXEC (@ExecSQL); FETCH NEXT FROM DBsWithFS INTO @DBName; END; CLOSE DBsWithFS; DEALLOCATE DBsWithFS; /*Return database files and size info*/ SELECT d.[name] AS [Database], d.[state_desc] AS [DatabaseState], SUM(CASE WHEN f.[type] = 0 THEN 1 ELSE 0 END) AS [DataFiles], CAST(SUM(CASE WHEN f.[type] = 0 THEN ( f.size * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [DataFilesSizeGB], SUM(CASE WHEN f.[type] = 1 THEN 1 ELSE 0 END) AS [LogFiles], CAST(SUM(CASE WHEN f.[type] = 1 THEN ( f.size * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [LogFilesSizeGB], ISNULL(fs.FSFilesCount, 0) AS [FILESTREAMContainers], ISNULL(fs.FSFilesSizeGB, 0.000) AS [FSContainersSizeGB], CAST(SUM(f.size * 8 / 1024.00 / 1024.00) AS NUMERIC(15, 3)) + ISNULL(fs.FSFilesSizeGB, 0.000) AS [DatabaseSizeGB] FROM sys.master_files AS f INNER JOIN sys.databases AS d ON f.database_id = d.database_id LEFT JOIN #FSFiles AS fs ON f.database_id = fs.DatabaseID GROUP BY d.[name], d.[state_desc], fs.FSFilesCount, fs.FSFilesSizeGB ORDER BY [DatabaseSizeGB] DESC; /*Drop temp table*/ IF OBJECT_ID(N'tempdb.dbo.#FSFiles', N'U') IS NOT NULL DROP TABLE #FSFiles; |
Output example
Note that I’m not excluding system databases from the output but, if needed, that can be done by adding WHERE d.[database_id] > 4 to the query.
For a specific database
If needed, you can run this script in the context of a specific database to get the files and size info for that database alone.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SELECT DB_NAME() AS [Database], SUM(CASE WHEN [type] = 0 THEN 1 ELSE 0 END) AS [DataFiles], CAST(SUM(CASE WHEN [type] = 0 THEN ( size * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [DataFilesSizeGB], SUM(CASE WHEN [type] = 1 THEN 1 ELSE 0 END) AS [LogFiles], CAST(SUM(CASE WHEN [type] = 1 THEN ( size * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [LogFilesSizeGB], SUM(CASE WHEN [type] = 2 THEN 1 ELSE 0 END) AS [FILESTREAMContainers], CAST(SUM(CASE WHEN [type] = 2 THEN ( size * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [FSContainersSizeGB], CAST(SUM(size * 8 / 1024.00 / 1024.00) AS NUMERIC(15, 3)) AS [DatabaseSizeGB] FROM sys.database_files; |
Conclusion
The post is pretty much self-explanatory, I needed a script to return database size info in a certain way and without omitting FILESTREAM containers.