sp_spaceused or sysfiles - 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 or sysfiles

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-12-22 : 06:56:29

i need to calculate how much space is remaining to be utilised in my database.
One option is to use sp_spaceused and using "unallocated space" and "database_size", i can get DB size availability percentage


DBAvlPercent = unallocated space / database_size

Second option is to use sysfiles value


select Round(CAST(100 * (CAST (((sysfiles.size -CAST(FILEPROPERTY(sysfiles.name,'SpaceUsed' ) AS float))/
(sysfiles.size))     AS float)) as float),2) as [DBAvlPercent]

Please let me know which option is correct.
I am asking this question because both the formulae give different values

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-22 : 07:12:03

Hi
sp_spaceused

Note

If index is dropped the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database.

So you can run DBCC UPDATEUSAGE separately.

-------------------------
R...

zion99
Posting Yak Master

141 Posts

Posted - 2009-12-22 : 22:55:26

thanks rajdaksha, but I am not obtaining any info for tables. it is just sp_spaceused v/s sysfiles...

quote:
Originally posted by rajdaksha

Hi
sp_spaceused

Note

If index is dropped the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database.

So you can run DBCC UPDATEUSAGE separately.

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources