sp_spaceused - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_spaceused

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-06 : 18:16:06

hello there

why are the values from sp_spaceused different than


FROM    sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL,
                                       'SAMPLED') AS ps -- (databaseID, ObjectID,null,null,'SAMPLED')
        INNER JOIN sys.indexes AS b ON ps.object_id = b.object_id
                                       AND ps.index_id = b.index_id
        INNER JOIN sys.all_objects AS o ON o.object_id = ps.object_id

I want to be able to know exactly how much physical space is used by indexes and then another query for finding the exact physical data being used to estimate new server size.

If you don't have the passion to help people, you have no passion

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-06 : 18:35:06

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

The answer is likely there, specifically---database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

You may need to run DBCC UPDATEUSAGE or specify the update usage parameter (or run DBCC UPDATEUSAGE )

Poor planning on your part does not constitute an emergency on my part.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-06 : 20:28:34

thanks dataguru!

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources