sp_spaceused help

Question

sp_spaceused help

California

SSCommitted

Points: 1888
More actions
May 27, 2009 at 2:46 pm

#155751

When I run sp_spaceused I get something like:
database_name . database_size . unallocated space
------------------ ----------------- ------------------
. . . Prod . . . . . . . 122.50 MB . . . . . 0.07 MB
reserved . . . . . . data . . . . . . . index_size . . . . unused
------------------ ------------------ ------------------ ------------------
1720 KB . . . . . . . 888 KB . . . . . . 648 KB . . . . . .184 KB
I would like this info to load into a table and with this regard, create a table which looks like this:
CREATE TABLE [dbo].[space](
[database_name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[database_size] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unallocated space] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reserved] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[data] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[index_size] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unused] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
When i run the following, i am having trouble dumping the info to the table:
SQL Statement:
run insert into space
exec sp_spaceused
Results: this is what i get:
Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 113
Insert Error: Column name or number of supplied values does not match table definition.
anything i am missing here? appreciated any input here.
Many thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 1

You can't insert the results directly into a table because you have two different results sets (and so two different tables).

In SQL Server 2005 upwards these results are generated from the DMV sys.dm_db_partition_stats

So you should be ablle to query this (perhaps with some summarisation)

Further details:

http://msdn.microsoft.com/en-us/library/ms187737.aspx

Tim

.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

Or you can use this script if you need info per file.

SELECT

a.FILEID,

CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,

CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],

CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],

a.name as [DATABASENAME],

a.FILENAME as [FILENAME]

FROM dbo.sysfiles a

Alex S

California SSCommitted Points: 1888 More actions · Answer 3

Gentleman,

Thanks a lot for the contribution. the provided info was very helpful and i was able to resolve my issue.

you guys are best.

Thanks again,