This post contains a script I’ve put together to query database sizes in SQL Server along with some additional info like file counts per file types and their sizes in GB.
Get all database sizes in a SQL Server 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(CAST([size] AS BIGINT) * 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 (CAST(f.[size] AS BIGINT) * 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 (CAST(f.[size] AS BIGINT) * 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(CAST(f.[size] AS BIGINT) * 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.
Getting the size of 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 (CAST([size] AS BIGINT) * 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 (CAST([size] AS BIGINT) * 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 (CAST([size] AS BIGINT) * 8 / 1024.00 / 1024.00 ) ELSE 0.00 END) AS NUMERIC(15, 3)) AS [FSContainersSizeGB], CAST(SUM(CAST([size] AS BIGINT) * 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 query for SQL Server database size info in a certain way and without omitting FILESTREAM containers.
If you’ve found this post helpful, you might also like this post about getting table sizes in SQL Server.
3 comments
Hello,
Thank you for the script…
It used to work but for whatever reason, I now get an error :
Msg 8115, Level 16, State 2, Line 41
Arithmetic overflow error converting expression to data type int.
Completion time: 2024-05-03T10:49:56.0646263+02:00
Any idea ?
Thank you
Marc
Hi Marc,
most likely the size column sums up to something that exceeds INT and it needs to be cast as BIGINT first.
So changing every occurrence of the size column to cast(sum as bigint) or f.size to cast(f.size as bigint) should sort it out.
Hi Marc,
I’ve updated both scripts, they should no longer throw that error on larger databases.