Announcement

Collapse
No announcement yet.

How to find size of SQL Server database?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to find size of SQL Server database?

    My apologizes for the short question but how can I find out what the size is of SQL Server database?
    This would include tables, logs, etc.

  • #2
    Try the following -

    Query:
    Code:
    SELECT 
          database_name = DB_NAME(database_id)
        , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
        , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
        , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
    FROM sys.master_files WITH(NOWAIT)
    WHERE database_id = DB_ID() -- for current db 
    GROUP BY database_id
    Output:
    Code:
    -- my query
    name           log_size_mb  row_size_mb   total_size_mb
    -------------- ------------ ------------- -------------
    0000000000    512.00       302.81        814.81
    
    -- sp_spaceused
    database_name    database_size      unallocated space
    ---------------- ------------------ ------------------
    0000000000      814.81 MB          13.04 MB
    Function:
    Code:
    ALTER FUNCTION [dbo].[GetDBSize] 
    (
        @db_name NVARCHAR(100)
    )
    RETURNS TABLE
    AS
    RETURN
    
      SELECT 
            database_name = DB_NAME(database_id)
          , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
          , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
          , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
      FROM sys.master_files WITH(NOWAIT)
      WHERE database_id = DB_ID(@db_name)
          OR @db_name IS NULL
      GROUP BY database_id

    Comment


    • #3
      You need to start with the sp_spaceused command.

      For example:

      sp_spaceused // Returns information about the total size of the database

      Comment


      • #4


        I have worked as a manager at a small to mid size company for almost 10 years and been a key player in terms of developing various excel worksheets for things such as project cost estimating and scheduling, as well as some Sharepoint/PowerApps and other web applications that house cloud data stored (that can snaptube vidmate be exported into excel).

        Comment

        Working...
        X