How large is an individual table in Microsoft SQL Server
January 5th, 2009 By: jamesSome time ago I had a situation where a clients SQL database was about 70GB larger than they expected it to be. That is like 10 times larger than they expected.
They asked me to trouble shoot the problem; with no clue as to what the problem should be.
To begin with I decided to find out the size of each individual table to determine if it was any particular table that was the problem.
To calculate the table sizes I wrote a query to put all of the table sizes into a table that could easily be read and shared with the client as follows:
create table temp_tablesizes (
name varchar(250),
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused varchar(20)
)
DECLARE @table_name varchar(250)
declare @res_name varchar(250)
DECLARE table_list CURSOR read_only
FOR SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS Expr1
FROM information_schema.tables WHERE (table_type = 'BASE TABLE')
ORDER BY TABLE_NAME OPEN table_list FETCH next
FROM table_list
INTO @table_name
WHILE @@fetch_status = 0
BEGIN
insert into temp_tablesizes EXEC sp_spaceused @table_name
PRINT @table_name
FETCH next
FROM table_list
INTO @table_name END CLOSE table_list DEALLOCATE table_list
Looking at the table sizes in the temp_tablesizes database we discovered that 90% of the size was allocated to a table which was suppose to have a maximum of two records per account each record being a large sound file. We discovered that the tool which deleted old recordings wasn’t working and where able to resolve the problem of an excessively large Microsoft SQL Server database.
The Sevier River Water Users Association (SRWUA) in Central Utah are pioneers in the use of the Real-Time Web. For the past 10 years SRWUA has been delivering real-time water flow data to the public via the website www.SevierRiver.org.
More that 100 remote, solar-powered, stations record wate...