Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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
share|improve this answer

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

insert into dbo.DBs
    exec sp_helpdb
go

select cast(replace(db_size, 'MB', '') as decimal(10,2))
       from dbo.DBs
       where dbname = 'SomeDatabase'
go
share|improve this answer

Your Answer

 
discard

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