Sql-server – Why a SQL Server Table takes 1GB more after adding 55MB of data

sql server

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
Yesterday

Today
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

Best Answer

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:

Screenshot

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.