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 :
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:
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 toVARCHAR
fields with the same end result?See
DATALENGTH
function, for instance, to check the length of your variable length columns.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
Etc. Don't be afraid to test and get creative.