shrinkfile and LOB data

  • 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

  • Shrinkfile is not recommended and after repeated attempts its not shrinking means SQL server is using it or need that much space.

  • 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.

  • 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

  • Have you checked the initial file size value?

    Jayanth Kurup[/url]

  • 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