Archive for the 'Microsoft SQL Server' Category

How large is an individual table in Microsoft SQL Server

Monday, January 5th, 2009 By: james

Some 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.