Saturday, April 7, 2012

Why shrink sql server data file failed?

Today I need to shrink a big data file, I know shrink file is not a best practise, and it will cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file, it should be fine as long as you run rebuild index after shrink file.

DBCC SHRINKFILE(1, 230000)
GO

the command run about 2 hours, then gave me an error message:
Msg 3140, Sev 16:
Could not adjust the space allocation for file 'xxxx'

What happend? this error always means when you shrink the file, there might be other processes moving the data on that file at the same time. But I remember I disabled the full backup job and diff backup job before I start shrink file. so I searched if there was any other job running during my shrink.

Suddenly I remember I have logshipping setup on this database, that should be the problem, I checked the logshipping share folder, there were many log backup file generated during my shrink. I think it was the cause.

I checked the file size, it didn't change, still the same size before shrink, does it mean I need to spend another 2 hours to shrink te file again? Fortunately, We don't need to.

I run the shrink command with TRUNCATEONLYagain, TRUNCATEONLY doesn't move data, only release the unused space from the end of the file.
DBCC SHRINKFILE (1, TRUNCATEONLY);

it returned very quickly, then I found the file size got smaller :)


5 comments:

  1. Good efforts. All the best for future posts. I have bookmarked you. Well done. I read and like this post. Thanks.
    1995 Subaru Legacy AC Compressor

    ReplyDelete
  2. Thanks Sheena, I am glad you like my post:)

    ReplyDelete
  3. Thanks for sharing your innovative ideas to our vision. I have read your blog and I gathered some new information through your blog. Your blog is really very informative and unique. Keep posting like this. Awaiting for your further update.If you are looking for any Big Data related information, please visit our website Big Data training institute in Bangalore.

    ReplyDelete
  4. Good Blog, well descrided, Thanks for sharing this information.
    Big Data and Hadoop Online Training

    ReplyDelete
  5. The information that you have shared is really useful for everyone.
    python Online Training

    ReplyDelete