none
How to get total DB size

    Question

  • I am using SQL 2005/2008/2012. I want to get such type of information:-

    Server Name

    Total Number DB

    Total DB Size in GB

    Server1

    10

    100

    Server2

    15

    200

    Any help would be highly appreciated.

    Thanks

    Friday, January 17, 2014 10:09 PM

Answers

  • The below query for one server

    USE MASTER
    Go

    select servername,
    count(dbname) NoOfDatabase,
    sum(TotalSizeMB)TotalSizeMB
    From
    (
    SELECT @@SERVERNAME Servername,
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB
    FROM sysdatabases DB
    )T
    Group by ServerName

    See this below link on how to capture execute the script across multiple servers and store the data in a centralized server.

    http://gallery.technet.microsoft.com/T-SQL-DiskSpace-of-d55e9314

    --Prashanth

    • Marked as answer by XYZ001 Friday, January 17, 2014 10:26 PM
    Friday, January 17, 2014 10:19 PM
  • Try converting your values to BIGINT and see....

    Not tested:

    select servername,
    count(dbname) NoOfDatabase,
    sum(TotalSizeMB)TotalSizeMB
    From
    (
    SELECT @@SERVERNAME Servername,
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    (SELECT SUM((Cast(size as bigint)*Cast(8 as bigint))/cast(1024 as bigint)) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)
    +(SELECT SUM((Cast(size as bigint)*Cast(8 as bigint))/cast(1024 as bigint)) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB
    FROM sysdatabases DB
    )T
    Group by ServerName

    • Marked as answer by XYZ001 Monday, January 20, 2014 5:15 PM
    Monday, January 20, 2014 4:54 PM

All replies

  • The below query for one server

    USE MASTER
    Go

    select servername,
    count(dbname) NoOfDatabase,
    sum(TotalSizeMB)TotalSizeMB
    From
    (
    SELECT @@SERVERNAME Servername,
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB
    FROM sysdatabases DB
    )T
    Group by ServerName

    See this below link on how to capture execute the script across multiple servers and store the data in a centralized server.

    http://gallery.technet.microsoft.com/T-SQL-DiskSpace-of-d55e9314

    --Prashanth

    • Marked as answer by XYZ001 Friday, January 17, 2014 10:26 PM
    Friday, January 17, 2014 10:19 PM
  • i am getting following error:- 

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    How to resolve it?

    Monday, January 20, 2014 4:41 PM
  • Try converting your values to BIGINT and see....

    Not tested:

    select servername,
    count(dbname) NoOfDatabase,
    sum(TotalSizeMB)TotalSizeMB
    From
    (
    SELECT @@SERVERNAME Servername,
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    (SELECT SUM((Cast(size as bigint)*Cast(8 as bigint))/cast(1024 as bigint)) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)
    +(SELECT SUM((Cast(size as bigint)*Cast(8 as bigint))/cast(1024 as bigint)) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB
    FROM sysdatabases DB
    )T
    Group by ServerName

    • Marked as answer by XYZ001 Monday, January 20, 2014 5:15 PM
    Monday, January 20, 2014 4:54 PM