3

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 [binary](200) NULL.

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.

Table definition:

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]

Edit:

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.

  • Where are you getting the data table usage from? Could it be possible that there are more pages due to the update, but more whitespace overall? – George.Palacios May 25 '17 at 9:49
  • SQL Server Management Studio. Right click > Properties > Storage on the table. Size is really increasing it reaches 10 GB and throws an error if I set these columns to null in plenty more rows. – Sacha K May 25 '17 at 9:53
  • 3
    @SachaK, please add DDL for the table in question. binary(200) is a fixed-length type so setting the value to NULL should not change the size at all.. – Dan Guzman May 25 '17 at 10:16
  • 1
    It's not reproducible. The table does not change in size after update – sepupic May 25 '17 at 12:14
  • 1
    To be clear, are you issuing an UPDATE query or changing the column definition from NOT NULL to NULL? – Joe Obbish May 25 '17 at 23:20
0

I don't know why the table is growing (is there an index on this column??), but if binary data type behaves like char and nchar - which I was just looking at - then assigning NULL in a fixed-size column will not release space from data rows. There will still be 200 bytes reserved in the row for the binary data. It's just how it works (if it is how it works).

That the column is NULL, is stored separately, in one or more bytes per row for this purpose, whether any column actually is nullable or not - unless none are. If you had no nullable columns, then no bytes were used to hold NULLs. If you just changed a column to receive null, then one byte for each 8 columns, in each row, is used to say whether each column is NULL or not in this row. So maybe that's your growth.

varchar(X) storing Y bytes occupied Y+2 bytes in the row; the data, plus 2 bytes storing the length of the data. varbinary probably is the same.

varchar(X) which is NULL occupies - some say - no space at all, except for the flag that says "This column is NULL", in the bytes which hold your NULLs.

So maybe if you change your data type to varbinary(200) NULL, then the rows containing NULL will get 200 bytes smaller.

I believe this won't immediately release space from the database, though - there will be unused space on each data page. You'll need to reorganise or rebuild the clustered index to get whole empty pages in the database. Then you might want to shrink the database file so that empty pages are released to the operating system - but this is strongly discouraged. And having the database file be at its size limit means that you can see how much space there is to use.

  • I don't know that much about SQL Server, but I've searched for information and found all of that already. The thing is that data size should have stayed the same from what I understand - but it increased. – Sacha K May 25 '17 at 10:18
  • No index on these columns, index storage size doesn't change. – Sacha K May 25 '17 at 10:20
  • 1
    @Robert Carnegie, you said >>>If you just changed a column to receive null, then one byte for each 8 columns, in each row, is used to say whether each column is NULL or not in this row. So maybe that's your growth.<<< updating a not null value to null does not add anything to row, the null bitmap array uses 1 bit for every column even for not nullable column, in the case above the corresponding bit is just changed from 0 to 1 – sepupic May 25 '17 at 12:12
  • If no table columns are created NULL (nullable) then I think there is no null bitmap array. When the table is altered to include a nullable column, then a null bitmap array is added in all rows and for all columns, so, 1 byte for the first 8 columns, etc. If that was done, on its own this doesn't seem likely to expand the table page count by as much as Sacha said, but maybe it caused page splits, i.e. very full page -> two half-full pages. I once saw a corrupted database where a column value was mapped NULL although the column wasn't nullable, but the bitmap said that the value was NULL – Robert Carnegie May 29 '17 at 12:55

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.