I have a large data file (PRIMARY filegroup) with a total size of ~350GB, out of which ~118GB is used (only 37%).
I want to shrink this file as much as possible, since the database was turned into a read-only archive, and is therefore not expected to grow anymore.
When a big SHRINKFILE is executed, it takes too long to run, so we cancelled it (after about 8 hours), and I started shrinking in small chunks.
This actually worked well but I seem to have "hit a wall" when I reached ~314GB.
SQL Server doesn't seem to want to shrink even a tiny chunk of 10 MB.
It just "hangs" at 99.99% (based on sys.dm_exec_requests) and stays there.
I managed to make "progress" by shrinking the file in microscopic chunks of 1 MB. Every such chunk takes about 2 minutes to shrink. I got ~196000 more chunks like this to go, and if every one of them takes 2 minutes, it's gonna take me ~272 days to
finish! That's madness!
I found several sources here and there that suggest that this issue happens due to SQL trying to move LOB pages during the shrink:
http://www.sqlservercentral.com/Forums/Topic1372418-1550-1.aspx
http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e368ea43-ab0a-4d65-9bf0-2f9221192c1e/anyone-know-why-dbcc-shrinkfile-takes-so-long?forum=sqltools
However, unlike the people in those other posts, I don't have tables with LOB or Overflow-Data!
I run the following query to make sure:
SELECT p.object_id,
OBJECT_SCHEMA_NAME(p.object_id) table_schema,
OBJECT_NAME(p.object_id) table_name,
p.index_id,
p.partition_number,
au.allocation_unit_id,
au.type_desc,
au.total_pages,
au.used_pages,
au.data_pages
FROM sys.system_internals_allocation_units au
JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE type_desc <> 'IN_ROW_DATA' AND total_pages > 0
ORDER BY table_schema,
table_name,
p.index_id,
p.partition_number,
au.type;
But the only result that comes up is the system table sys.sysobjvalues with 90 total pages of LOB_DATA.
Based on this post by Paul Randal:
http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/
This puny little table shouldn't be bothering me so much, because Paul says:
When a text page is moved by data file shrink, a
table scan must be performed for each LOB value to find the ‘owning’ data record so the pointer can be updated. (If the LOB value is from an
INCLUDEd column in a nonclustered index, then a similar scan must be performed
of the nonclustered index. If the LOB value is lower down in the text tree for a >8000 byte LOB value, all text pages
for that table or index must be scanned.)
Which means that, at most, only the owning table
of a LOB page should be the one to be scanned.
Since sysobjvalues is the only one with LOB data, and it's a tiny table, even a full table scan on it shouldn't bother me.
I have a couple tables of my own with NVARCHAR(MAX) columns but none of them has overflowed data. But even if I do somehow, both of these tables are tiny as well! (less than 1MB each), so a full scan on both shouldn't be a problem either! I have several huge
tables in the database as well, but none of them have LOB or MAX columns.
So, this suggests one or more of the following:
1. Paul Randal was mistaken when he said that only the owning table/index will be scanned.
2. The issue is not LOB data, but something else entirely.
So far I managed to shrink about 10MB and I keep going, each 1MB taking around 2 minutes.
But this situation is completely absurd and unacceptable.
I'm stomped. Anyone got any ideas?
Thanks!
Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services
