Last week we had an issue with a database growing unexpectedly. The
database in question supported an infrastructure type 3rd party
application and the administrators over said application were
scratching their head as to why it could have grown. I threw together a
quick script using sp_spaceused to figure out what the table sizes were
in the database. However, I ended up using a temporary table to dump
all the information from sp_spaceused for each table and then had to
parse out the KB. That solution didn't satisfy me very much. I've
tinkered with the script and threw together a simple stored procedure
that does the same thing, only it has options so I can get the numbers
back in byes, KB, MB, or GB as necessary.
This script only works on SQL Server 2000, not SQL Server 2005. Let me
rephrase that. It'll work, but it may not be accurate because of some
changes in physical structure in the new version. The script is
designed to be placed in the master database though if you have a work
database, it could work there, too. If in a work database, change it so
the stored procedure doesn't begin with sp_ as SQL Server will look in
the master database first, even if the three part naming convention is
used.
USE master
GO
IF EXISTS (SELECT id FROM sysobjects WHERE name = 'sp_ReportTableSizes' AND xtype = 'P')
DROP PROC sp_ReportTableSizes
GO
CREATE PROC sp_ReportTableSizes
@ConversionType char(1) = 'K'
AS
BEGIN
DECLARE @conversion float
IF @ConversionType = 'B' SET @conversion = 1.0
ELSE IF @ConversionType = 'M' SET @conversion = (1024.0 * 1024.0)
ELSE IF @ConversionType = 'G' SET @conversion = (1024.0 * 1024.0 * 1024.0)
ELSE SET @conversion = 1024.0
SELECT su.tablename, (su.tablesize * spt.low / @conversion) totalsize
FROM master..spt_values spt,
(SELECT so.name tablename, SUM(si.reserved) tablesize
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE si.indid IN (0, 1, 255)
AND so.xtype = 'U'
GROUP BY so.name) su
WHERE spt.number = 1
AND spt.type = 'E'
ORDER BY su.tablesize DESC, su.tablename ASC
END
GO
