Msg 3140, Level 16, State 5, Line 1 Could not adjust the space allocation for file ‘DBfilename’

We had database which had few tables with image/text (LOB) datatypes .. and after some archeiving of older data … we wanted to reclaim the free space in the database ..

normal Shrink command was not able to reclaim any of free space in the database .. so i decided to go with notruncate and truncate_only options .. but the second part was failing with an error .. Could not adjust the space allocation for file

SCRIPT

go

print ‘start time’
print getdate()
go
DBCC shrinkfile (1,notruncate)
go
print ‘completed first step’
print getdate()
go
DBCC shrinkfile (1,truncateonly)
print ‘completed 2nd step’
print getdate()

out put :: 

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed first step
Oct 1 2013 2:17AM
Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file ‘DBfilename’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed 2nd step
Oct 1 2013 2:17AM

Solution :

after aboveerror i tried to shrink the DB with DBCC Shrinkfile (1,targetSize)  (which had initially failed to shrink any free space)

but this time i was able to shrink the DB to required size …  so solution to error such as this is

just Shrink the DB with t-sql script  DBCC Shrinkfile (1,targetSize)  or use GUI to shrink the DB … 

it should complete real fast as all the shrinking has been already done by DBCC shrinkfile (1,truncateonly) …

ue below script to confirm the reduction is the overall DB files ..

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

refer below link for internals of shrink operation

http://blogs.msdn.com/b/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

ENJOY 🙂