DBCC SHRINKFILE failing with error Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file

  • Hi,

    I am trying to run SHRINKFILE on one of our data files but it is failing with error "Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file"[/b] on every execution. I have checked disk space and that is OK and the file in question has a 1/3 free space which I am trying to trim back:

    FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName

    500.00340.50 159.50 EVVSGUKEVVSG01_1_1_data_002

    Can anyone help me regarding what I should try next as it just keeps on failing. The database is in Full recovery mode with 34 file groups - PRIMARY and FingerprintCatalogue_000 thru 032. The file above belongs to file group FingerprintCatalogue_002.

    Thanks for looking,

    Greg

  • The obligatory response: are you sure you want to shrink your data file? Stop Shrinking Your Database Files. Seriously. Now. by Brent Ozar[/url]

    Now that we have that out of the way...

    Are there any other files in the FingerprintCatalogue_002 filegroup?

    Does the filegroup house tables with LOB data?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Are there any other files in the FingerprintCatalogue_002 filegroup?

    No. All file groups have been created with a single data file.

    Does the filegroup house tables with LOB data?

    No I don't think so. As this is all new to me and it is a 3rd party application/database installation I used the following query, which I got from the internet, to check for LOB data, feel free to correct it or suggest an alternative method:

    select SO.Name as [Table], COL.name as [ColumnName], COL.system_type_id, TYP.name as [Type Name], SFG.groupname as 'Filegroup'

    from sysobjects as SO

    join sysindexes as SI

    on SO.Id = SI.id

    join sysfilegroups as SFG

    on SI.GroupId = SFG.GroupId

    join sys.columns as COL

    on COL.object_id = SO.id

    join sys.types as TYP

    on COL.system_type_id = TYP.system_type_id

    where sfg.groupname like 'FingerprintCatalogue%'

    and TYP.system_type_id in (35,34,241,99)

    order by SO.Name , SFG.GroupName

  • Can you please check again. Yuor query is not checking for any of the MAX types. Try this one instead:

    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;

    As an aside, views like sysobjects have been maintained in the product since SQL 2005, but only for backward compatibility with code written for SQL 2000 and before. Please start using the current catalog views instead: Mapping System Tables to System Views (SQL Server 2008 R2)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thank you for info I'm rather new to this so it's nice to be pointed in the right direction.

    I've ran your query and no rows were returned for any of the file groups.

    Cheers,

  • You're welcome. I try to pass along the latest info regarding the System and Catalog Views to keep the ball moving forward so to speak. The newer Views have more information and there are many more of them, so it's good to get to know them.

    Try DBCC SHRINKFILE with the NOTRUNCATE option, then try shrinking it with a target size.

    USE YourDatabase;

    GO

    DBCC SHRINKFILE (FingerprintCatalogue_002, NOTRUNCATE);

    GO

    DBCC SHRINKFILE (FingerprintCatalogue_002, target_size in MB);

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • The first SHRINKFILE with NOTRUNCATE executes OK, the second fails with the original message of:

    Msg 3140, Level 16, State 5, Line 1

    Could not adjust the space allocation for file 'EVVSGUKEVVSG01_1_1_data_002'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • Try shrinking by a very small amount. Can you restore the DB to a test area so you can drop into SINGLE_USER mode and try the shrink, or can you do that during a maintenance window?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Cheers opc.three will try this out when our system admin is back in the office. After reading the blogs you pointed me to I don't think it's worth doing the shrink anyway as the files are quite small.

    Thanks,

    Greg

  • Orlando Colamatteo - Friday, August 24, 2012 9:58 AM

    Can you please check again. Yuor query is not checking for any of the MAX types. Try this one instead: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_idWHERE 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;As an aside, views like sysobjects have been maintained in the product since SQL 2005, but only for backward compatibility with code written for SQL 2000 and before. Please start using the current catalog views instead: Mapping System Tables to System Views (SQL Server 2008 R2)

    Hi, 
    If I were to get results from this query, can you offer explanation?

  • I have seen this error when a backup is in progress for the database on which the shrink operation is being attempted. Wait for the backup to complete and try again.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply