How do I determine the size of my SQL Server database?

Basic: what's the size on disk of my MS SQL Server DB?
More: can I quickly see where the data is? i.e. which tables, logs, etc

From serverfault adambox

• You'll probably want to start with the sp_spaceused command.

  For example:

  sp_spaceused Returns information about the total size of the database

  sp_spaceused 'MyTable' Returns information about the size of MyTable

  Read the docs for all the things you can get information about. You can also use the sp_msforeachtable command to run sp_spaceused against all tables at once.

  Edit: Be aware the command sometimes returns multiple datasets, each set containing a different chunk of stats.

  Dario Solera : Just to add a note, sp_spaceused returns the number of 8KB pages the database files occupy.

  Nick Kavadias : If your db is 2000 then you might need to run DBCC UPDATEUSAGE to get the right numbers in here. Should always be correct in 2005
  From David

• Run Start\ Programs\ Microsoft SQL Server\ Enterprise Manager. Open Database sheet, in property %databasename% you can see location Data filrs & Transaction files.

  David : Or if it's SQL Server 2005, 2008, etc, open SQL Management Studio, right click the database, select properties and then click the second item on the left tab, files. However this will only return the overall file size - which you could see by just looking in the folder where the data and log file are stored.
  From $pageUsers[$post.author].name

• You can see the physical files in sys.database_files. This has the path to the file and the size (in blocks IIRC).

  sp_spaceused will show you how much space an individual object takes up.

  From ConcernedOfTunbridgeWells

• run this to get the size per table:

  /******************************************************************************
  **    File: “GetTableSpaceUsage.sql”
  **    Name: Get Table Space Useage for a specific schema
  **    Auth: Robert C. Cain
  **    Date: 01/27/2008
  **
  **    Desc: Calls the sp_spaceused proc for each table in a schema and returns
  **        the Table Name, Number of Rows, and space used for each table.
  **
  **    Called by:
  **     n/a – As needed
  **
  **    Input Parameters:
  **     In the code check the value of @schemaname, if you need it for a
  **     schema other than dbo be sure to change it.
  **
  **    Output Parameters:
  **     NA
  *******************************************************************************/
  
  /*—————————————————————————*/
  /* Drop the temp table if it's there from a previous run                     */
  /*—————————————————————————*/
  if object_id(N'tempdb..[#TableSizes]') is not null
    drop table #TableSizes ;
  go
  
  /*—————————————————————————*/
  /* Create the temp table                                                     */
  /*—————————————————————————*/
  create table #TableSizes
    (
      [Table Name] nvarchar(128)   /* Name of the table */
    , [Number of Rows] char(11)    /* Number of rows existing in the table. */
    , [Reserved Space] varchar(18) /* Reserved space for table. */
    , [Data Space] varchar(18)    /* Amount of space used by data in table. */
    , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */
    , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */
    ) ;
  go
  
  /*—————————————————————————*/
  /* Load the temp table                                                        */
  /*—————————————————————————*/
  declare @schemaname varchar(256) ;
  -- Make sure to set next line to the Schema name you want!
  set @schemaname = 'dbo' ;
  
  -- Create a cursor to cycle through the names of each table in the schema
  declare curSchemaTable cursor
    for select sys.schemas.name + '.' + sys.objects.name
        from    sys.objects
        , sys.schemas
        where   object_id > 100
          and sys.schemas.name = @schemaname
          /* For a specific table uncomment next line and supply name */
          --and sys.objects.name = 'specific-table-name-here'    
          and type_desc = 'USER_TABLE'
          and sys.objects.schema_id = sys.schemas.schema_id ;
  
  open curSchemaTable ;
  declare @name varchar(256) ;  /* This holds the name of the current table*/
  
  -- Now loop thru the cursor, calling the sp_spaceused for each table
  fetch curSchemaTable into @name ;
  while ( @@FETCH_STATUS = 0 )
    begin    
      insert into #TableSizes
        exec sp_spaceused @objname = @name ;       
      fetch curSchemaTable into @name ;   
    end
  
  /* Important to both close and deallocate! */
  close curSchemaTable ;     
  deallocate curSchemaTable ;
  
  
  /*—————————————————————————*/
  /* Feed the results back                                                     */
  /*—————————————————————————*/
  select [Table Name]
        , [Number of Rows]
        , [Reserved Space]
        , [Data Space]
        , [Index Size]
        , [Unused Space]
  from    [#TableSizes]
  order by [Table Name] ;
  
  /*—————————————————————————*/
  /* Remove the temp table                                                     */
  /*—————————————————————————*/
  drop table #TableSizes ;
  

  taken from Robert Caine blog

  This code is for Microsoft SQL 2005+

  From balexandre

• This script loops through all of the tables in the current database and shows how much space each one takes up for data, indexes, and unused space:

  http://sqlserverpedia.com/wiki/Calculate_Current_Table_Sizes

  From Brent Ozar

• This is a query/view that gets all of this info, and more, without any "evil" cursors or loops. ;-)

      /*
      vwTableInfo - Table Information View
  
   This view display space and storage information for every table in a
  SQL Server 2005 database.
  Columns are:
      Schema
      Name
      Owner  may be different from Schema)
      Columns  count of the max number of columns ever used)
      HasClusIdx 1 if table has a clustered index, 0 otherwise
      RowCount
      IndexKB  space used by the table's indexes
      DataKB  space used by the table's data
  
   16-March-2008, RBarryYoung@gmail.com
   31-January-2009, Edited for better formatting
  */
  --CREATE VIEW vwTableInfo
  -- AS
  
      SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
      , tbl.Name
      , Coalesce((Select pr.name 
              From sys.database_principals pr 
              Where pr.principal_id = tbl.principal_id)
          , SCHEMA_NAME(tbl.schema_id)) as [Owner]
      , tbl.max_column_id_used as [Columns]
      , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
      , Coalesce( (Select sum (spart.rows) from sys.partitions spart 
          Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
  
      , Coalesce( (Select Cast(v.low/1024.0 as float) 
          * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
              FROM sys.indexes as i
               JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
               JOIN sys.allocation_units as a ON a.container_id = p.partition_id
              Where i.object_id = tbl.object_id  )
          , 0.0) AS [IndexKB]
  
      , Coalesce( (Select Cast(v.low/1024.0 as float)
          * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
              FROM sys.indexes as i
               JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
               JOIN sys.allocation_units as a ON a.container_id = p.partition_id
              Where i.object_id = tbl.object_id)
          , 0.0) AS [DataKB]
      , tbl.create_date, tbl.modify_date
  
       FROM sys.tables AS tbl
        INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
        INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
  

  Enjoy.

  From RBarryYoung

• The easiest way (no typing!): In SQL 2005/8 from Management Studio, right click the database, select Reports, Standard Reports, Disk Usage (also By Top Tables, Table and Partition).

  From

• There are several descriptions of how to do it from the GUI.

  Real DBA's know: GUI's are for chumps.

  sp_helpdb

  Returns a recordset of all of the file names, locations, space on disk, and type.

  You can also retrieve the file names from each databases's sysfiles table.

  From $pageUsers[$post.author].name