T-SQL Script for Monitoring Database Size in SQL Server

  • 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

  • We have a big database. I had to make this change:

    SUM(CAST(size AS BIGINT))

    Otherwise, nice compact script.

  • 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

    • This reply was modified 2 weeks, 5 days ago by  chuckh1958.
  • Duplicate post deleted

    • This reply was modified 2 weeks, 5 days ago by  chuckh1958. Reason: duplicate
  • chuckh1958 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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