October 8, 2012 at 6:02 pm
We've moved most of the large indexes out of our primary filegroup, out into new files/luns in other filegroups. As others have found, the large primary file refuses to shrink despite what appears to be 300GB of free space in the file. We're pretty sure the LOB fields like varchar(max) in this file are still there.
Short of moving everything out of this file, I've looked at a confusing array of options and undocumented stored procedures like dbcc ind and dbcc extentinfo.
One article suggested repeatedly reorganizing indexes ( LOB compaction is the default for reorganize ) in an attempt to get the file to shrink.
Any new ideas?
SQL 2005 SP4 Enterprise
October 9, 2012 at 4:44 am
Shrinkfile is not recommended and after repeated attempts its not shrinking means SQL server is using it or need that much space.
October 9, 2012 at 5:57 am
We know its not recommended in general, and would need to rebuild the indexes after shrinking. But since the file now has 300GB showing as free space inside the file, we think it's worth the effort. I believe it has something to do with LOB data occupying extents near the end of the file.
October 9, 2012 at 7:01 am
Query that checks for LOB columns:
SELECT t.name AS
,
c.name AS [column],
c.system_type_id,
typ.name AS [type],
c.max_length,
fg.name AS [filegroup]
FROM sys.columns c
JOIN sys.types typ ON c.system_type_id = typ.user_type_id
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.data_spaces ds ON t.lob_data_space_id = ds.data_space_id
JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.name LIKE 'Primary%'
AND (
(
-- nvarchar, varbinary, varchar
typ.system_type_id IN (231, 165, 167)
-- (MAX)
AND c.max_length = -1
)
-- text, image, xml, ntext
OR typ.system_type_id IN (35, 34, 241, 99)
)
ORDER BY t.name,
fg.name;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 9, 2012 at 7:11 am
October 9, 2012 at 7:48 am
Yes, in fact if you reduce the initial size in the GUI ( management studio ), then script that action out to a query window you get something like this. DBCC SHRINKFILE (N'DBFile1' , 690000)
My understanding is that as of sql 2005 or newer, this command will update the initial size and not be blocked by it. But just for grins, I could try lowering the initial size in the GUI. If the problem is LOB data stored out near the end of the file, then I've read of 2 approaches:
1) move all tables containing LOB out of the file into another file. We have moved the indexes out, but the LOB seems to remain.
2) run index reorganize repeatedly in an attempt to get LOB compaction
3) run the shrinkfile and let it run for days or weeks to complete
USE [Mydatabase]
GO
DBCC SHRINKFILE (N'DBFile1' , 690000)
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply