I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this. It has 4,000,000 rows with the following structure...
Name Type Nullable AuditLogId bigint no UserName nvarchar no TimeOfChange datetime no ObjectName nvarchar no ChangeName nvarchar no RecordId int yes OriginalValues nvarchar yes ResultingValues nvarchar no
With a primary key on the AuditLogId
ALTER TABLE [dbo].[AuditLog] ADD CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED ( [AuditLogId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] GO
There is also a foreign key to the record that was updated on RecordId. The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters. The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.