Auto Shrink Problem

A few months back, on one of our SQL Server 2005 system, our ETL process would failed and in the application log, we would get the following error:

Error: 1105, Severity: 17, State: 2

Could not allocate space for object ‘[Table_Name]’ in database ‘[Database_Name]’ because the ‘PRIMARY’ filegroup is full.

When seeing this error, my immediate thought was that we might have run out of disk space or the maximum file size that was set on the database Autogrowth setting has been reached. But then when I check those things, those were not the case. We seemed to have more than enough disk space to accommodate the new data, and both the data and log current file size were far below the specified maximum file size.

What could cause this error? So I decided to re-run the ETL process and monitor it. During one of the ETL process, one weird thing that I noticed is that in the middle of the ETL process, the target database data file size suddenly reduced in size, and not long after that, the ETL process would stopped with the above error. Granted that in one of the ETL steps, we truncate data from several of our existing tables, but I was not expecting to see the database data file shrink. Seeing this behavior, I quickly looked at the target database options and sure enough the Auto Shrink option for the database is turned on.

Auto Shrink feature could help to release free space that you have on your database file automatically. The problem is that there is no additional settings that allow you to control on when the Auto Shrink feature should run. In my case, the Auto Shrink shrank the database file during the ETL run and that was not good. So I turned off the Auto Shrink option on the database, and since then I have not gotten the above errors on subsequent ETL runs that we have.

Some Notes:

• Paul Randall (blog | twitter) explains it best on this article in regards to why we should turn off Auto Shrink option from our database.
• Microsoft best practices recommendation is to set the Auto Shrink option off, based on this article.