Using SQL Server 2008, I have found that the database size can be obtained using

exec sp_spaceused

This procedure returns two tables:

database_name | database_size | unallocated space

reserved | data | index_size | unused

I would like to create a query where the only output of the SQL call is the value returned for database_size. I do not want any extraneous output (no table headers, column names, etc).

Is there a simple way to do this?

migrated from serverfault.com Nov 23 '11 at 20:49

This question came from our site for system and network administrators.


Another way to accomplish this without the creating database objects is to use the below query taken from the sp_spaceused stored procedure itself:

declare @dbsize bigint
declare @logsize bigint

select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
* 8192 / 1048576,15,2) + ' MB') as Database_Size

Short answer: You can't do what you want with sp_spaceused.

Longer answer: You may be able to do what you want with sp_helpdb and creative use of temporary tables.
This bytes.com discussion includes one example which I've cleaned up and reproduced below:

create table dbo.DBs (
    dbname sysname,
    db_size varchar(20),
    owner sysname,
    dbid int,
    created datetime,
    status varchar(1000),
    compat int

insert into dbo.DBs
    exec sp_helpdb

select cast(replace(db_size, 'MB', '') as decimal(10,2))
       from dbo.DBs
       where dbname = 'SomeDatabase'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy