Why a SQL Server Table takes 1GB more after adding 55MB of data?

Question

I've found that one customer database size exploded between yesterday and today. After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data have been added to the table but the table size is now 1GB while the original size of the table was 70MB.

For additional information, 55MB of data was added by adding more than 2000 rows with a varchar(max) field filled with the data.

Here is the before/after analysis :

Yesterday

Today

Can you explain to me why a table took 70MB now takes 1GB after adding 55MB of data ?

There is not any index on the varchar(max)

The free space is very low, it is not related to the autogrowth. And the autogrowth is not configured with a percentage but with a fixed growth size.

Here is the creation script of the table :

USE [DuoClient20140003]
GO

/****** Object:  Table [dbo].[tContactHistory]    Script Date: 23/05/2016 10:53:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tContactHistory](
    [KeyCH] [int] IDENTITY(1,1) NOT NULL,
    [DateCH] [datetime] NULL,
    [KeyType] [int] NULL,
    [KeyF] [int] NULL,
    [KeyI] [int] NULL,
    [KeyCA] [int] NULL,
    [Content] [varchar](max) NULL,
    [KeyCom] [int] NULL,
    [KeyMH] [bigint] NULL,
    [KeyH] [bigint] NULL,
 CONSTRAINT [tContactHistory_PK] PRIMARY KEY CLUSTERED 
(
    [KeyCH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tAddrCarnet_tContactHistory_FK1] FOREIGN KEY([KeyCA])
REFERENCES [dbo].[tAddrCarnet] ([KeyCA])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tAddrCarnet_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tComments_tContactHistory_FK1] FOREIGN KEY([KeyCom])
REFERENCES [dbo].[tComments] ([KeyCom])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tComments_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tFoyers_tContactHistory_FK1] FOREIGN KEY([KeyF])
REFERENCES [dbo].[tFoyers] ([KeyF])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tFoyers_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tIndivs_tContactHistory_FK1] FOREIGN KEY([KeyI])
REFERENCES [dbo].[tIndivs] ([KeyI])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tIndivs_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tMailHistory_tContactHistory] FOREIGN KEY([KeyH])
REFERENCES [dbo].[tMailHistory] ([KeyH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tMailHistory_tContactHistory]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tModifsHistory_tContactHistory_FK1] FOREIGN KEY([KeyMH])
REFERENCES [dbo].[tModifsHistory] ([KeyMH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tModifsHistory_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tTabShort_tContactHistory_FK1] FOREIGN KEY([KeyType])
REFERENCES [dbo].[tTabShort] ([KeyT])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tTabShort_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tContactHistoryKeyI_Chk] CHECK  (([KeyI]>=(1)))
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tContactHistoryKeyI_Chk]
GO

Answer 1

System procedure sp_spaceused tells you how the data is distributed, and is usually more than accurate enough. Run the following simple TSQL:

DECLARE @R TABLE (TabName sysname, [Row Count] bigint, Reserved varchar(128), Data varchar(128), [Idx Size] varchar(128), Unused varchar(128))

INSERT INTO @R
exec sp_spaceused 'tContactHistory'

SELECT TabName
    , [Row Count]
    , cast(round(cast(replace(Reserved, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Reserved MB]
    , cast(round(cast(replace(Data, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Data MB]
    , cast(round(cast(replace([Idx Size], ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Idx Size MB]
    , cast(round(cast(replace(Unused, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Unused MB]
FROM @R

Tell us what you find. If there's a problem with fillfactor, datatypes, or just plain old overzealous indexing, this should give enough of a clue as to where to go next, at least.

Update: the result was:

Well your results are odd. It implies the data is actually used. I also see someone already asked you before how you verified the size of the data, and you replied that you did so by calculating it from the field size. Are you sure you did so correctly? Is it possible that empty strings or special characters were introduced that you can't see, but still take up space? Or that you're dealing with CHAR as opposed to VARCHAR fields with the same end result?

See DATALENGTH function, for instance, to check the length of your variable length columns.

-- For example:
SELECT DATALENGTH(myVarCharColumnName) DLen 
FROM tContactHistory 
ORDER BY DLen desc
-- Note by checking the column type and storage size from MSDN, 
-- you can now use this information to calculate the storage size.

Also, have you tested this hypothesis by for instance copying over part of that data to a new, dummy table and see what happens? Like

SELECT TOP 1000 *
INTO myStorageSizeTest
FROM tContactHistory
WHERE ... -- Insert whatever condition here to only select the new rows where this space problem appeared

Etc. Don't be afraid to test and get creative.

Answer 2

Perhaps a big operation that was rolled back.

If you really want to know, you can inspect the log and see exactly how the growth occurred. See How to read and interpret the SQL Server log. Simply pick one current unused page and then find the transaction that allocated it to the table. From the context of that transaction you will be able to tell exactly what operation it was, by looking at the LOP_BEGIN_XACT (read the blog linked).

Of course I assume you still have the log (even if is backed up). That is, the database is not in simple recovery mode.

After edit:

[Content] [varchar](max) NULL

You have BLOB columns. All it takes is few rows with a very large value to reach 10GB. I suggest you search for large content rows:

SELECT COUNT(*), DATALENGTH(Content), SUM(DATALENGTH(Content)) as SumContentSize
FROM tContactHistory
WHERE DATALENGTH(Content) > 1000
GROUP BY DATALENGTH(Content)
ORDER BY DATALENGTH(Content) DESC;