I often need to shrink database files. I know, ‘shrinking is bad’, however there are situations where it is very much needed. In my line of work, I come across databases that have been collecting data for year after year after year. One of the things I check for is the largest tables in databases. I then review that data to see how old the oldest data is. Often this data is past the required retention period and the business is able to purge a sizeable amount of data out of the database.
When that happens, I am able to reclaim that space by shrinking the database. Since I want to do that during non peak times, I’m usually doing this after normal business hours and occasionally run into an error.
Error Msg 3140, Level 16, State 5 is nearly always due to another process moving data on that file while your shrink is running. In my case, this has always been a backup job.
So no need to stress if you see this error, just check to see if your backup jobs are conflicting.
The post Msg 3140 – Could Not Adjust the Space Allocation for File appeared first on Tim Radney.