15

I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:

Unused Space

I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some strategies on an identical table in a test DB with less data. This table has a similar problem:

Unused Space

Some information about the table:

  • The fill factor is set to 0
  • There are about 30 columns
  • One of the columns is a LOB of type image, and it's storing files that range in size from a few KB to several hundred MB
  • The table does not have any hypothetical indexes associated with it

The Server is running SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64). The database is using the SIMPLE recovery model.

Some things I've tried:

  • Rebuilding the indexes: ALTER INDEX ALL ON dbo.MyTable REBUILD. This had a negligible impact.
  • Reorganizing the indexes: ALTER INDEX ALL ON dbo.MyTable REORGANIZE WITH(LOB_COMPACTION = ON). This had a negligible impact.
  • Copied the LOB column to another table, dropped the column, re-created the column, and copied the data back (as outlined in this post: Freeing Unused Space SQL Server Table). This decreased the unused space, but it seemed to just convert it into used space:

    Unused Space

  • Used the bcp utility to export the table, truncate it, and reload it (as outlined in this post: How to free the unused space for a table). This also reduced the unused space and increased the used space to a similar extent as the above image.

  • Even though it's not recommended, I tried the DBCC SHRINKFILE and DBCC SHRINKDATABASE commands, but they didn't have any impact on the unused space.
  • Running DBCC CLEANTABLE('myDB', 'dbo.myTable') didn't make a difference
  • I've tried all of the above both while maintaining the image and text datatypes and after changing the datatypes to varbinary(max) and varchar(max).
  • I tried importing the data into a new table in a fresh database, and this also only converted the unused space into used space. I outlined the details of this attempt in this post.

I don't want to make these attempts on the production DB if these are the results I can expect, so:

  1. Why is the unused space just being converted to used space after some of these attempts? I feel like I don't have a good understanding of what's happening under the hood.
  2. Is there anything else I can do to decrease the unused space without increasing the used space?

EDIT: Here's the Disk Usage report and script for the table:

Disk Usage

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
    [Column1]  [int] NOT NULL,
    [Column2]  [int] NOT NULL,
    [Column3]  [int] NOT NULL,
    [Column4]  [bit] NOT NULL,
    [Column5]  [tinyint] NOT NULL,
    [Column6]  [datetime] NULL,
    [Column7]  [int] NOT NULL,
    [Column8]  [varchar](100) NULL,
    [Column9]  [varchar](256) NULL,
    [Column10] [int] NULL,
    [Column11] [image] NULL,
    [Column12] [text] NULL,
    [Column13] [varchar](100) NULL,
    [Column14] [varchar](6) NULL,
    [Column15] [int] NOT NULL,
    [Column16] [bit] NOT NULL,
    [Column17] [datetime] NULL,
    [Column18] [varchar](50) NULL,
    [Column19] [varchar](50) NULL,
    [Column20] [varchar](60) NULL,
    [Column21] [varchar](20) NULL,
    [Column22] [varchar](120) NULL,
    [Column23] [varchar](4) NULL,
    [Column24] [varchar](75) NULL,
    [Column25] [char](1) NULL,
    [Column26] [varchar](50) NULL,
    [Column27] [varchar](128) NULL,
    [Column28] [varchar](50) NULL,
    [Column29] [int] NULL,
    [Column30] [text] NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] 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
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO

Here are the results of executing the commands in Max Vernon's answer:

╔════════════╦═══════════╦════════════╦═════════════════╦══════════════════════╦════════════════════╗
║ TotalBytes ║ FreeBytes ║ TotalPages ║ TotalEmptyPages ║ PageBytesFreePercent ║ UnusedPagesPercent ║
╠════════════╬═══════════╬════════════╬═════════════════╬══════════════════════╬════════════════════╣
║  9014280192║ 8653594624║     1100376║          997178 ║            95.998700 ║          90.621500 ║
╚════════════╩═══════════╩════════════╩═════════════════╩══════════════════════╩════════════════════╝
╔═════════════╦═══════════════════╦════════════════════╗
║ ObjectName  ║ ReservedPageCount ║      UsedPageCount ║
╠═════════════╬═══════════════════╬════════════════════╣
║ dbo.MyTable ║           5109090 ║            2850245 ║
╚═════════════╩═══════════════════╩════════════════════╝

UPDATE:

I ran the following as suggested by Max Vernon:

DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');

And here was the output:

DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'PK_MyTable', partition 1):
        USED pages (LOB Data): changed from (568025) to (1019641) pages.
        RSVD pages (LOB Data): changed from (1019761) to (1019763) pages.

This updated the disk usage for the table:

enter image description here

And the overall disk usage:

enter image description here

So, it looks like the problem was that the disk usage as tracked by SQL Server became wildly out-of-sync with the actual disk usage. I'll consider this issue resolved, but I'd be interested to know why this would have happened in the first place!

  • Were the shrinks tried after changing the data types? – LowlyDBA Aug 1 at 14:51
  • 1
    Any chance you can share the repro you've made with us or are you just using a backup of your production database as a copy to test against? – John Eisbrener Aug 1 at 14:51
  • @LowlyDBA Yes, I tried the shrinks after changing the data types. – Ken Aug 6 at 20:23
  • I ran the script and confirmed that there are no hypothetical indexes. – Ken Aug 6 at 20:24
  • 1
    How long have you had this database in production? How often do you run DBCC CHECKDB? Have you considered moving away from the deprecated data types, text and image? They might be contributing to the improper stats. – Max Vernon Aug 7 at 16:41
9
+50

I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.

DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

Syntax is:

DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');

After you run that, I'd run EXEC sys.sp_spaceused against the table:

EXEC sys.sp_spaceused @objname = N'dbo.MyTable'
    , @updateusage = 'false' --true or false
    , @mode = 'ALL' --ALL, LOCAL_ONLY, REMOTE_ONLY
    , @oneresultset = 1;

The above command has the option to update usage, but since you ran DBCC UPDATEUSAGE manually first, just leave that set to false. Running DBCC UPDATEUSAGE manually allows you to see if anything was corrected.

The following query should show the percentage of bytes free in the table and the percentage of free pages in the table. Since the query uses an undocumented feature, it's unwise to count on the results, but it seems accurate when compared with the output from sys.sp_spaceused, at a high-level.

If the percent of free bytes is significantly higher than the percent of free pages, then you have a lot of partially empty pages.

Partially empty pages can stem from a number of causes, including:

  1. Page splits, where the page must be split to accommodate new inserts into the clustered index

  2. An inability to fill the page with columns due to column size.

The query uses the undocumented sys.dm_db_database_page_allocations dynamic management function:

;WITH dpa AS 
(
    SELECT dpa.*
        , page_free_space_percent_corrected = 
          CASE COALESCE(dpa.page_type_desc, N'')
            WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            ELSE COALESCE(dpa.page_free_space_percent, 100)
          END
    FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
)
, src AS
(
SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024
    , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024
    , TotalPages = COUNT_BIG(1)
    , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages
FROM dpa
)
SELECT *
    , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100
    , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100
FROM src

Output looks like:

╔═════════╦════════╦════════════╦═════════════════╦══════════════════╦════════════════════╗
║ TotalKB ║ FreeKB ║ TotalPages ║ TotalEmptyPages ║ BytesFreePercent ║ UnusedPagesPercent ║
╠═════════╬════════╬════════════╬═════════════════╬══════════════════╬════════════════════╣
║     208 ║     96 ║         26 ║              12 ║        46.153800 ║          46.153800 ║
╚═════════╩════════╩════════════╩═════════════════╩══════════════════╩════════════════════╝

I wrote a blog post describing the function here.

In your scenario, since you've executed ALTER TABLE ... REBUILD, you should see a very low number for TotalEmptyPages, but I'm guessing you'll still have around 72% in BytesFreePercent.

I've used your CREATE TABLE script to attempt to recreate your scenario.

This is the MCVE I'm using:

DROP TABLE IF EXISTS dbo.MyTable;

CREATE TABLE [dbo].[MyTable](
    [Column1]  [int]            NOT NULL IDENTITY(1,1),
    [Column2]  [int]            NOT NULL,
    [Column3]  [int]            NOT NULL,
    [Column4]  [bit]            NOT NULL,
    [Column5]  [tinyint]        NOT NULL,
    [Column6]  [datetime]       NULL,
    [Column7]  [int]            NOT NULL,
    [Column8]  [varchar](100)   NULL,
    [Column9]  [varchar](256)   NULL,
    [Column10] [int]            NULL,
    [Column11] [image]          NULL,
    [Column12] [text]           NULL,
    [Column13] [varchar](100)   NULL,
    [Column14] [varchar](6)     NULL,
    [Column15] [int]            NOT NULL,
    [Column16] [bit]            NOT NULL,
    [Column17] [datetime]       NULL,
    [Column18] [varchar](50)    NULL,
    [Column19] [varchar](50)    NULL,
    [Column20] [varchar](60)    NULL,
    [Column21] [varchar](20)    NULL,
    [Column22] [varchar](120)   NULL,
    [Column23] [varchar](4)     NULL,
    [Column24] [varchar](75)    NULL,
    [Column25] [char](1)        NULL,
    [Column26] [varchar](50)    NULL,
    [Column27] [varchar](128)   NULL,
    [Column28] [varchar](50)    NULL,
    [Column29] [int]            NULL,
    [Column30] [text]           NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] 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]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO

INSERT INTO dbo.MyTable (
      Column2
    , Column3
    , Column4
    , Column5
    , Column6
    , Column7
    , Column8
    , Column9
    , Column10
    , Column11
    , Column12
    , Column13
    , Column14
    , Column15
    , Column16
    , Column17
    , Column18
    , Column19
    , Column20
    , Column21
    , Column22
    , Column23
    , Column24
    , Column25
    , Column26
    , Column27
    , Column28
    , Column29
    , Column30
)
VALUES (
          0
        , 0
        , 0
        , 0
        , '2019-07-09 00:00:00'
        , 1
        , REPLICATE('A', 50)    
        , REPLICATE('B', 128)   
        , 0
        , REPLICATE(CONVERT(varchar(max), 'a'), 1)
        , REPLICATE(CONVERT(varchar(max), 'b'), 9000)
        , REPLICATE('C', 50)    
        , REPLICATE('D', 3)     
        , 0
        , 0
        , '2019-07-10 00:00:00'
        , REPLICATE('E', 25)    
        , REPLICATE('F', 25)    
        , REPLICATE('G', 30)    
        , REPLICATE('H', 10)    
        , REPLICATE('I', 120)   
        , REPLICATE('J', 4)     
        , REPLICATE('K', 75)    
        , 'L'       
        , REPLICATE('M', 50)    
        , REPLICATE('N', 128)   
        , REPLICATE('O', 50)    
        , 0
        , REPLICATE(CONVERT(varchar(max), 'c'), 90000)
);
--GO 100

;WITH dpa AS 
(
    SELECT dpa.*
        , page_free_space_percent_corrected = 
          CASE COALESCE(dpa.page_type_desc, N'')
            WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            ELSE COALESCE(dpa.page_free_space_percent, 100)
          END
    FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
)
, src AS
(
SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024
    , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024
    , TotalPages = COUNT_BIG(1)
    , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages
FROM dpa
)
SELECT *
    , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100
    , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100
FROM src

The following query shows a single line for each page allocated to the table, and uses that same undocumented DMV:

SELECT DatabaseName = d.name
    , ObjectName = o.name
    , IndexName = i.name
    , PartitionID = dpa.partition_id
    , dpa.allocation_unit_type_desc
    , dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
    , dpa.is_allocated
    , dpa.page_free_space_percent --this seems unreliable
    , page_free_space_percent_corrected = 
        CASE COALESCE(dpa.page_type_desc, N'')
        WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
        WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
        ELSE COALESCE(dpa.page_free_space_percent, 100)
        END
    , dpa.page_type_desc
    , dpa.is_page_compressed
    , dpa.has_ghost_records
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
    LEFT JOIN sys.databases d ON dpa.database_id = d.database_id
    LEFT JOIN sys.objects o ON dpa.object_id = o.object_id
    LEFT JOIN sys.indexes i ON dpa.object_id = i.object_id AND dpa.index_id = i.index_id
WHERE dpa.database_id = DB_ID() --sanity check for sys.objects and sys.indexes

The output will show a lot of rows if you run it against your real table in your test environment, but it may allow you see where the problem is.

Can you run the following script and post the results in your question? I'm just trying to make sure we're on the same page.

SELECT ObjectName = s.name + N'.' + o.name
    , ReservedPageCount = SUM(dps.reserved_page_count)
    , UsePageCount = SUM(dps.used_page_count)
FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    INNER JOIN sys.partitions p ON o.object_id = p.object_id
    INNER JOIN sys.dm_db_partition_stats dps ON p.object_id = dps.object_id
WHERE s.name = N'dbo'
    AND o.name = N'MyTable'
GROUP BY s.name + N'.' + o.name;
  • 2
    Running DBCC UPDATEUSAGE updated the unused space and unused page count. It looks like the disk usage and page information being reported by SQL Server was extremely out of sync - I updated my post with the details. I'm curious about how this would have happened in the first place, but at least the problem was found. Thank you for all your help, I really appreciate it! – Ken Aug 7 at 16:30
0

One of the columns is a LOB of type image, and it's storing files that range in size from a few KB to several hundred MB

You could be experiencing internal fragmentation.
What is the page fragmentation for this table?
And is the fragmentation for the in-row different from the off-row pages?

You say you have files that are a few KB.
SQL Server stores everything in 8060 Byte pages. Meaning, if you have a row (or off-row data) that is 4040 bytes and the next one is similar, it cannot fit both in the same page and you will waste half your space. Try changing your row size by storing variable length columns (start with the image for example) in a different table.

  • I don't think fragmentation is the issue. After rebuilding the indexes, the fragmentation for the clustered index is 0.45% and its page fullness is 98.93%. – Ken Aug 6 at 20:49
  • Rebuilding a table or index will not help if you suffer from very big rows or LOB data that doesn't fit into 8KB pages well. It's what Max Vernon explained in more detail: "you have a lot of partially empty pages." also called internal fragmentation – DrTrunks Bell Aug 8 at 7:07
-3

Is the database in full recovery mode? If so, when you perform a shrink, it's logging all of the changes and won't shrink it the way you're expecting. Depending on your hours of operation, you could take a backup, switch to bulk-shipping recovery mode and then run the shrink on the data file. After that, you'd want to run index scripts to repair/rebuild and switch back to full recovery. That is what I would try anyways but again, it depends on your hours of operation for all of this.

  • 4
    Bring up the recovery model is interesting. I think it would be more applicable if the OP were having problems with the size of their log file. As it stands, they are having trouble with the size of the data file, so I'd be surprised if the recovery model was causing the issue described. – Josh Darnell Jul 23 at 16:23
  • True but the only times I've run a shrink and it didn't really impact space was because of the recovery model so I thought it was worth bringing up in case it was misdiagnosis. – John-Henry Lochbaum Jul 23 at 16:36
-3

The only time I've been unable to shrink a DB and reclaim space is because you cannot shrink a DB beyond the initial size of the DB when it was created. So for example, if your DB is a copy of the production DB and you first created the DB at 525GB, sql server will not allow you to shrink the size below 525GB no matter how much data you delete from the DB. But if the DB was created below 383GB and then grew to be 525GB you should have no trouble reclaiming the space. I've long thought this is a stupid and arbitrary restriction by Microsoft.

Shrink the database only up to its initial size which is set after creating database

  • The question is not about shrinking a database (and if it was, the ability to shrink it depends on the used space after the initial size region) – eckes Aug 1 at 15:57
  • As long as there is unused space, it is possible to shrink the database to a couple of MB regardless of the original size. It is not necessarily a good idea but I have had many occasions to shrink databases and never run into a limit like this. – Ray Aug 7 at 16:31
-3

I've encountered this problem before on production boxes, what you need to do is rebuild tables and indexes for each table (in that order).

Here's the query I use to keep tables in check. It will help you determine which tables need to be rebuilt, and create the SQL queries you need to run. This query is limited to those with higher than 1MB unused space and a 5% unused ratio, so that you only rebuild what you really need to focus on:

SELECT  'alter table [' + t.NAME + '] rebuild;' AS SQL1, 'alter index all on [' + t.NAME + '] rebuild;' as SQL2, t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,  SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,  (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB, case when SUM(a.total_pages)=0 then 0 else (SUM(a.total_pages) - SUM(a.used_pages))*100/SUM(a.total_pages) end as Ratio  FROM     sys.tables t (nolock) INNER JOIN       sys.indexes i (nolock)  ON t.OBJECT_ID = i.object_id INNER JOIN  sys.partitions p (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN  sys.allocation_units a (nolock) ON p.partition_id = a.container_id LEFT OUTER JOIN  sys.schemas s (nolock) ON t.schema_id = s.schema_id WHERE  t.is_ms_shipped = 0 AND i.OBJECT_ID > 255  GROUP BY  t.Name, s.Name, p.Rows  
having  (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024>1
and (SUM(a.total_pages) - SUM(a.used_pages))*100/SUM(a.total_pages)>5
ORDER BY    5 desc
  • rebuilding the table, as the OP states they've done, would get rid of most of the fragmentation. I doubt doing another rebuild will help further. – Max Vernon Aug 2 at 22:19

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.