Wednesday, April 4, 2012

sp_spaceused & dbcc updateusage

sp_spaceused -- Returns the row count, space used, space reserved and space used by indexes.

Example :
sp_spaceused 'HumanResources.Department'  -- Returns the row count from Department Table, and data size, Index size, Object Size of that table.
sp_spaceussed -- Returns the size of the whole database.

Some time sp_spaceused doesn't report the accurate result, we can query it, In that situation we can use dbcc updateusage command to make the counts accurate.

dbcc updateusage -- It corrects the inaccuracies in row counts, disk space etc.,

Usage & Examples:
dbcc updateusage (0) -- zero indicates current database name.

dbcc updateusage (DatabaseName, "HumanResources.Department")

dbcc updateusage (DatabaseName, TableName, IndexName)

No comments: