Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
What version of SQL? –  SQLChicken May 28 '09 at 12:35
    
this is a programatic question, show be in StackOverflow! read: stackoverflow.com/questions/914182 –  balexandre May 28 '09 at 13:01
    
I disagree. It's fundamentally a sysadmin question. Why do programmers care if the server runs out of disk space? –  Nick Kavadias May 28 '09 at 13:11
    
I agree with Nick. It's a either/or question. DBA questions definitely belong here. –  squillman May 28 '09 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 '11 at 20:24

8 Answers 8

up vote 22 down vote accepted

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.

share|improve this answer
    
Just to add a note, sp_spaceused returns the number of 8KB pages the database files occupy. –  Dario Solera May 28 '09 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 –  Nick Kavadias May 28 '09 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).

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer
    
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 '09 at 12:43

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+

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.