The only thing we have done is replaced ScanImage
on every row with a
much smaller image (this is how so much unused space is there)
From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).
Example code that achieved the best space reduction for me with the table structure in the question is:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
BEGIN TRAN
SELECT [ImageID],
[ScanImage]
INTO #Temp
FROM [dbo].[MyTableName]
ALTER TABLE [dbo].[MyTableName]
DROP COLUMN [ScanImage]
/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD
ALTER TABLE [dbo].[MyTableName]
ADD [ScanImage] IMAGE NULL
UPDATE [dbo].[MyTableName]
SET [ScanImage] = T.[ScanImage]
FROM [dbo].[MyTableName] M
JOIN #Temp T
ON M.ImageID = T.[ImageID]
DROP TABLE #Temp
COMMIT
Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON
. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
to prevent any concurrent modifications from happening during or after the copy and being lost.
The number of LOB pages reserved after reducing the size of an image
in all rows was as follows:
+--------------------------------------------------+---------------------+-------------------------+
| Event | lob_used_page_count | lob_reserved_page_count |
+--------------------------------------------------+---------------------+-------------------------+
| Inserted 10,000 rows with 100,000 byte data each | 135005 | 135017 |
| Updated all rows to 10,000 byte image data | 31251 | 135012 |
| Reorganize | 23687 | 25629 |
| Drop and re-add image data | 13485 | 13489 |
+--------------------------------------------------+---------------------+-------------------------+
Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data.
The following is an adaptation of the code I posted to an answer on StackOverflow ( sp_spaceused - How to measure the size in GB in all the tables in SQL ) that handles all of the cases that sp_spaceused
handles. That S.O. question was only concerned with per-object stats, not per index, so I have adjusted the code to handle things at the index level.
;WITH agg AS
( -- Get info for Tables, Indexed Views, etc
SELECT ps.[object_id] AS [ObjectID],
ps.index_id AS [IndexID],
NULL AS [ParentIndexID],
NULL AS [PassThroughIndexName],
NULL AS [PassThroughIndexType],
SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
SUM(ps.used_page_count) AS [UsedPageCount],
SUM(ps.reserved_page_count) AS [ReservedPageCount],
SUM(ps.row_count) AS [RowCount],
SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
AS [LobAndRowOverflowUsedPageCount]
FROM sys.dm_db_partition_stats ps
GROUP BY ps.[object_id],
ps.[index_id]
UNION ALL
-- Get info for FullText indexes, XML indexes, Spatial indexes, etc
SELECT sit.[parent_id] AS [ObjectID],
sit.[object_id] AS [IndexID],
sit.[parent_minor_id] AS [ParentIndexID],
sit.[name] AS [PassThroughIndexName],
sit.[internal_type_desc] AS [PassThroughIndexType],
0 AS [InRowDataPageCount],
SUM(ps.used_page_count) AS [UsedPageCount],
SUM(ps.reserved_page_count) AS [ReservedPageCount],
0 AS [RowCount],
0 AS [LobAndRowOverflowUsedPageCount]
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables sit
ON sit.[object_id] = ps.[object_id]
WHERE sit.internal_type IN
(202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
GROUP BY sit.[parent_id],
sit.[object_id],
sit.[parent_minor_id],
sit.[name],
sit.[internal_type_desc]
), spaceused AS
(
SELECT agg.[ObjectID],
agg.[IndexID],
agg.[ParentIndexID],
agg.[PassThroughIndexName],
agg.[PassThroughIndexType],
OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
OBJECT_NAME(agg.[ObjectID]) AS [TableName],
SUM(CASE
WHEN (agg.IndexID < 2) THEN agg.[RowCount]
ELSE 0
END) AS [Rows],
SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
SUM(agg.LobAndRowOverflowUsedPageCount +
CASE
WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
ELSE 0
END) * 8 AS [DataKB],
SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
CASE
WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
ELSE 0
END) * 8 AS [IndexKB],
SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM agg
GROUP BY agg.[ObjectID],
agg.[IndexID],
agg.[ParentIndexID],
agg.[PassThroughIndexName],
agg.[PassThroughIndexType],
OBJECT_SCHEMA_NAME(agg.[ObjectID]),
OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
sp.TableName,
sp.IndexID,
CASE
WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
ELSE N'<Heap>'
END AS [IndexName],
sp.[PassThroughIndexName] AS [InternalTableName],
sp.[Rows],
sp.ReservedKB,
(sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
sp.DataKB,
(sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
sp.IndexKB,
(sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
sp.UsedKB AS [UsedKB],
(sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
sp.UnusedKB,
(sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
so.[type_desc] AS [ObjectType],
COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
sp.[PassThroughIndexType] AS [IndexSecondaryType],
SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
sp.ObjectID
--,sp.ParentIndexID
FROM spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
ON so.[object_id] = sp.ObjectID
LEFT JOIN sys.indexes si
ON si.[object_id] = sp.ObjectID
AND (si.[index_id] = sp.IndexID
OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N'' -- optional name filter
--ORDER BY ????
Best Answer
Likely, there is an IAM page and extent reserved for this table.
TRUNCATE TABLE
will remove these. When all data is just deleted, a bare minimum structure will still exist for the table.Have a demo!
If you want to actually see the pages and where they are, use this:
This shows one IAM page and one reserved extent for me, even when all rows have been deleted.