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

  • 1
    What version of SQL?
    – SQLChicken
    May 28, 2009 at 12:35
  • this is a programatic question, show be in StackOverflow! read: stackoverflow.com/questions/914182
    – balexandre
    May 28, 2009 at 13:01
  • 2
    I disagree. It's fundamentally a sysadmin question. Why do programmers care if the server runs out of disk space? May 28, 2009 at 13:11
  • 2
    I agree with Nick. It's a either/or question. DBA questions definitely belong here.
    – squillman
    May 28, 2009 at 13:44
  • It's both: programmers care about handling the exception if the DB runs out of usable space; sysadmins care for obvious reasons. :)
    – JYelton
    May 10, 2011 at 20:24

9 Answers 9


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.

  • Just to add a note, sp_spaceused returns the number of 8KB pages the database files occupy. May 28, 2009 at 12:46
  • 3
    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 May 28, 2009 at 13:02

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).


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:



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 ;

/* 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. */
  ) ;

/* 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 )
    insert into #TableSizes
            exec sp_spaceused @objname = @name ;       
    fetch curSchemaTable into @name ;   

/* 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+


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.


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

  • 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.
    – David
    May 28, 2009 at 12:43

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:
    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
    IndexKB     space used by the table's indexes
    DataKB      space used by the table's data

 16-March-2008, [email protected]
 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')



If you want to check database size of particular database using SSMS Gui

Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size

Source: Check database size in Sql server ( Various Ways explained)


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

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


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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .