I'd like to reduce database size by removing some data that is not useful anymore from a SQL Server Express database - 10 GB database size limit.
So I have a table that is 5783 MB and has 4 columns with the data type
I update about 600k rows where the binary data is not null. After changing the first of the binary columns to null the table data usage grows to 6315 MB. Changing the other 3 columns to null doesn't increase the table data size further.
What is happening here? From what I understand size should have stayed the same.
CREATE TABLE [dbo].[t1]( [Id] [int] IDENTITY(1,1) NOT NULL, [c2] [datetime] NOT NULL, [c3] [int] NOT NULL, [c4] [int] NOT NULL, [c5] [int] NOT NULL, [c6] [int] NOT NULL, [c7] [int] NOT NULL, [c8] [bigint] NOT NULL, [c9] [int] NOT NULL, [c10] [int] NOT NULL, [c11] [int] NOT NULL, [c12] [bigint] NOT NULL, [c13] [binary](200) NULL, [c14] [binary](200) NULL, [c15] [binary](200) NULL, [c16] [binary](200) NULL, [c17] [int] NOT NULL CONSTRAINT [DF_t1_c17] DEFAULT ((1)), [c18] [int] NULL, [c19] [uniqueidentifier] NULL, [c20] [int] NULL, [c21] [float] NULL, CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
So nothing obvious it seems. I'll look at creating a set of scripts for a minimum repro, but I won't have time before next week sometime.
Also this is happening on a 2014 SP1 Express, but the behavior is normal (data size doesn't change) on a 2012 SP1 Standard with the same data.