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.

Patching a Punctured Aerobed

Aerobed is a great invention. It is one of those "great to have" gizmo in the closet. If you have family members/guests coming over for the night, all you need to do is to take the Aerobed out from the closet, plug it into the power outlet, flick a switch ...lo and behold in less than 5 minutes you have an extra bed. It is not as comfortable as a spring bed (although some people might argue that), but it is definitely much more comfortable than sleeping on a sleeping bag, or a couch (in some cases).

Air Leak

One of the worse things that can happen to an Aerobed is a puncture hole. Even the smallest hole can render the Aerobed useless. This actually happened to my Aerobed. Every time I inflate the Aerobed, within a few hours it would sagged so badly that I can not use it comfortably.

Fixing the Leak

Normally, Aerobed comes with a repair kit. But it seemed that I have misplaced mine. So I ended up using:

• Kwik Tek Vinyl Repair Kit (the kit contains a tube of glue like substance and several vinyl patches)
• Eclectic Shoe Goo Clear

As for patching the small hole that was on the Aerobed, I follow the following steps:

• Clean the area around the small hole on the Aerobed
• Apply generous amount of the glue from the Kwik Tek Vinyl Repair Kit to one of the vinyl patch. Then cover the small hole with the vinyl patch.
• Put some weight on the patch and let it sit for about 24 hours. I used the weight of several books. (Notes: After 24 hours, I tried to inflate the Aerobed, however, I still notice some air leak around the edges of the vinyl patch).
• So, I applied generous amount of Eclectic Shoe Goo around the edges of the vinyl patch, and let it sit for another 24 hours.

After I allow the Eclectic Shoe Goo to dry, I inflated the Aerobed and use it for several days. I must say that so far things are looking good. I did not notice any air leak coming from the area of the hole. Also for those several days, the Aerobed stayed inflated and firm.

Some Notes:

• I must say, if the hole on the Aerobed is really small, I think we can fix the Aerobed by just applying a generous portion of Shoe Goo.
• The glue from the Kwik Tek Vinyl Repair Kit and the Shoe Goo have a strong odor, so you might want to use them in well ventilated room.