January 20, 2025 at 6:41 pm
Comments posted to this topic are about the item T-SQL Script for Monitoring Database Size in SQL Server
SQL Server Expert | Automating solutions for database performance & security | C#, .NET, Web Forms, DevExpress
January 29, 2025 at 12:40 pm
We have a big database. I had to make this change:
SUM(CAST(size AS BIGINT))
Otherwise, nice compact script.
January 29, 2025 at 6:11 pm
This does not show database sizes but rather individual database file sizes. To do what I think you want it to do - show total space used per database, change it to this...
SELECT
b.name AS DatabaseName, -- Name of the database
SUM(a.size) * 8 / 1024 AS SizeInMB, -- Total size in MB
b.state_desc AS Status -- Current status of the database
FROM sys.master_files a
join sys.databases b on b.database_id = a.database_id
GROUP BY b.name, b.state_desc
ORDER BY SizeInMB desc
January 29, 2025 at 6:13 pm
Duplicate post deleted
January 30, 2025 at 11:54 pm
This does not show database sizes but rather individual database file sizes. To do what I think you want it to do - show total space used per database, change it to this...
SELECT
b.name AS DatabaseName, -- Name of the database
SUM(a.size) * 8 / 1024 AS SizeInMB, -- Total size in MB
b.state_desc AS Status -- Current status of the database
FROM sys.master_files a
join sys.databases b on b.database_id = a.database_id
GROUP BY b.name, b.state_desc
ORDER BY SizeInMB desc
First, welcome aboard!
Just as a bit of a shortcut, there are exactly 128 pages per "binary" MegaByte, which is what your good code produces. Here's your code with the shortcut.
SELECT
b.name AS DatabaseName, -- Name of the database
SUM(a.size) / 128 AS SizeInMB, -- Total size in MB
b.state_desc AS Status -- Current status of the database
FROM sys.master_files a
join sys.databases b on b.database_id = a.database_id
GROUP BY b.name, b.state_desc
ORDER BY SizeInMB desc
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2025 at 1:49 am
Greetings... thanks for the code. I am a beginner. Would like to ask, is this for all interfaces or specific to SISS, SASS, SSMS or can we use this in other interfaces as well, like MySQL?
Rajakumar Keelu
January 31, 2025 at 12:56 pm
Its for SQL Server only. Any other RDBMS would have it's own similar query but using its own internal functions and views.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply