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.

Posted in Database Administration, Microsoft SQL Server | No Comments »