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

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 🙂

Advertisements
  1. August 31, 2014 at 5:32 pm

    Hi there! I know this is kinda off topic however
    , I’d figured I’d ask. Would you be interested in trading links or maybe guest writing a blog article or vice-versa?
    My blog addresses a lot of the same subjects as yours and I feel we could greatly benefit
    from each other. If you happen to be interested feel free
    to send me an e-mail. I look forward to hearing from you!
    Excellent blog by the way!

  2. Anonymous
    September 11, 2014 at 2:24 pm

    Select Tasks–> Shrink –> ”re-organize pages before releasing unused space” and shrink in chunks instead of giving the target size at once.

  3. Anonymous
    September 21, 2015 at 9:07 am

    this solution really works!!thanks sohal!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: