I have a database that has grown larger than I had expected. I think
there is one table that is at the root of the problem. The table is
defined as follows:
CREATE TABLE [dbo].[UserAudit] (
[UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,
[UserAuditAction_id] [int] NOT NULL ,
[Dataset_id] [int] NOT NULL ,
[UserName] [char] (64) NOT NULL ,
[TableName] [char] (64) NOT NULL ,
[Detail] [varchar] (4000) NOT NULL ,
[DateRecorded] [smalldatetime] NOT NULL ,
[Dsc] [char] (256) NULL
)
There are 14919 records in this table. When I do the calculations i
approximate that space used by this table should be in the region of
10mb (4+4+4+64+64+290(average length of Detail column)+4+256) * 14919.
When I execute "sp_spaceused 'UserAudit'" i see that 119MB are being
used by this table.
name rows reserved data index_size unused
UserAudit 14919 119808 KB 119352 KB 400 KB 56 KB
Even if I use 4000 for the Detail column in my calculations I still
come up with about only 64mb.
Any ideas on whats going on here?
Thanks in advance.