I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
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:
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:
- 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:
- 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.
- 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:
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 Hannah 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 Hannah 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:
And the overall disk usage:
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!
