sp_spaceused question - 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 2000 Forums

SQL Server Administration (2000)

sp_spaceused question

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-11-10 : 15:56:03

Hi -- As you probably know, the sp_spaceused stored procedure can return space information at both the table level and the database level. If I execute the following:


USE pubs
GO
EXEC sp_MSforeachtable 'sp_spaceused ''?'', @updateusage = ''TRUE'''
GO
EXEC sp_spaceused @updateusage='TRUE'

then I'm confused as to why the sum of the reserved space among all the tables (496 KB) doesn't come close to the size of the pubs database (2.31 MB). Even if I subtract the unallocated space from the total database size, the result is still far above 496 KB. Is the remaining space taken by views, stored procedures, users, UDFs, etc.?

Bill

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-10 : 16:45:58

The answer is because of the system tables. When you run sp_MSforeachtable, it returns only the user tables. In most user databases, these are the bulk of the disk space usage. Pubs is a bit smaller. It is roughly half system data, and half user data. The "missing" reserved pages can be found by running these:


exec sp_spaceused sysobjects
exec sp_spaceused sysindexes
exec sp_spaceused syscolumns
exec sp_spaceused systypes
exec sp_spaceused syscomments
exec sp_spaceused sysfiles1
exec sp_spaceused syspermissions
exec sp_spaceused sysusers
exec sp_spaceused sysproperties
exec sp_spaceused sysdepends
exec sp_spaceused sysreferences
exec sp_spaceused sysfulltextcatalogs
exec sp_spaceused sysindexkeys
exec sp_spaceused sysforeignkeys
exec sp_spaceused sysmembers
exec sp_spaceused sysprotects
exec sp_spaceused sysfulltextnotify
exec sp_spaceused sysfiles
exec sp_spaceused sysfilegroups

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-11-10 : 21:04:31

Okay. That makes sense. So does that mean if I take the full size of the pubs database and subtract the unallocated space and further subtract the reserved space of the user tables and system tables, I would wind up with the amount of space taken by the remaining database objects (views, stored procedures, UDF, etc.)?

Bill

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-11-11 : 07:38:24

Hmmm... just thinking about my question... The definition of views, stored procedures, UDFs, etc. are all stored in system tables so that wouldn't explain the space difference. I'm confused.

Bill

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-11-11 : 07:56:59

I'm such an idiot. Apparently, I've never heard of a log file. Sorry to bother you with my confusion.

Bill

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 09:09:18

"I'm such an idiot"