May 27, 2009 at 2:46 pm
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,
May 27, 2009 at 3:36 pm
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
.
May 27, 2009 at 3:47 pm
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
May 27, 2009 at 8:13 pm
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,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply