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 🙂