4

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.

Thanks

2
  • 2
    Have you checked index fragmentation? Does this table have lots of inserts and deletes? If you are not maintaining the indexes and lots of data gets inserted and later deleted, the index structure can become rather lopsided. Rebuilding the indexes as a whole to properly distribute the data could be helpful. I don't see any index information in your post so I didn't post this as an answer in case this is not your problem. – G.Smith Sep 9 '19 at 13:00
  • 1
    Which nvarchar field has 8000 characters? And why? – Pieter B Sep 10 '19 at 8:33
17

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

  • data in other columns I didn't account for in the above calculation
  • additional indexes
  • fill factor > 0 and < 100
  • fragmentation and space still occupied by deleted rows or page splits
0
0

In SQL Server nvarchar columns are two bytes per character so if you have 12k characters then yes you could easily have a very large table. If the column doesn't store unicode data then it could be converted to varchar which would free up some storage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.