Thursday, August 30, 2012

SQL sp_spaceused Database Disk Space Usage

In my previous post, I showed how to use several DBCC commands to look up how much space has been used in the log file. Today I have a quick post of finding the disk space reserved and used for database, table and indexed view.

To find out about disk space usage information about my 'Test' database,

USE Test;
GO
EXEC sp_spaceused;
GO



Database size is the total size of both data file and log file. Unallocated space is the space has not been reserved. Unused is the space reserved but not being used yet. Combine of unallocated and reserved space is the data file size.

The reserved space also give a pretty close estimate of how big is the full backup size, if you don't have any previous full backup history for reference.

To display object (e.g. table) disk space usage information,

USE Test;
GO
EXEC sp_spaceused N'dbo.Table1';
GO



Reserved is the size that has been allocated for the object. Unused is the space has been reserved but not being used yet (think fill factor). Index size is the space used for index for the object.

You can also use @updateusage optional argument to return the most udpdated current database or table disk space information.

USE Test;
GO
EXEC sp_spacedused @updateusage = N'TRUE'
GO

Google+