Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
greg.huxley
greg.huxley
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 130
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
greg.huxley
greg.huxley
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 130
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
greg.huxley
greg.huxley
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 130
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,
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
greg.huxley
greg.huxley
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 130
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
greg.huxley
greg.huxley
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 130
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search