Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC SHRINKFILE failing with error Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 9:56 AM
Points: 13, Visits: 108
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" 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:

FileSizeMB UsedSpaceMB UnusedSpaceMB DBFileName
500.00 340.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
Post #1349158
Posted Thursday, August 23, 2012 2:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
The obligatory response: are you sure you want to shrink your data file? Stop Shrinking Your Database Files. Seriously. Now. by Brent Ozar

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
Post #1349354
Posted Friday, August 24, 2012 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 9:56 AM
Points: 13, Visits: 108
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
Post #1349601
Posted Friday, August 24, 2012 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
Can you please check again. Yuor query is not checking for any of the MAX types. Try this one instead:

SELECT  t.name AS [table],
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
Post #1349782
Posted Tuesday, August 28, 2012 5:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 9:56 AM
Points: 13, Visits: 108
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,
Post #1350836
Posted Tuesday, August 28, 2012 3:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
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
Post #1351296
Posted Wednesday, August 29, 2012 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 9:56 AM
Points: 13, Visits: 108
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.
Post #1351595
Posted Wednesday, August 29, 2012 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 7,094, Visits: 12,579
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
Post #1351676
Posted Wednesday, September 19, 2012 4:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 9:56 AM
Points: 13, Visits: 108
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
Post #1361245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse