AutoGrow Apparently Means "I Don’t Care"

Well, at least that is the impression I have from Operations Manager. Let me explain why.

It all started around the time we were making some adjustments to one of our servers that was not performing as well as our end users would have liked. The short end of that story is that we ended up making some changes to our tempdb. We split the tempdb into separate data files, one per physical processor. So far, so good. We also went one step further and we set the size of tempdb to be fixed, turning off the Autogrow option.

[Why? Well, we believe that there is a chance, should your database become full, that autogrow will only increase the first file and not all files at once. If that happens, then you will start to fill only that first file, which essentially means you have lost the benefits of striping across all files and using multiple processors. So, you can avoid this by either disabling autogrow or by rigorous monitoring of your filesize and free space and manually growing your files. Of course, being tempdb, this really doesn’t matter if we assign all space on the disk to the database files not to mention the fact that tempdb gets rebuilt the next time the service is recycled.]

I bounced this question around Twitter recently, asking people if they consider fixing the size of their database and disable Autogrow. The majority of results are that you should size your database as best as possible upon initial creation, but let the data files grow automatically if necessary just to be safe.

Putting that debate aside for now, let me focus my ire back on OpsMgr.

So, one of the monitors you get out of the box with OpsMgr is the ‘DB Space Free (%)’ monitor, which is a wonderful idea because if the percentage of free space dips below a certain threshold you would want to be notified. I mean, who wouldn’t want to know if there was only five percent of free space left in their database? Sure, if it was a 10Tb database then that five percent would be 500Gb (which is a lot of flippin’ space if you ask me), but still, the idea is sound. And we have never seen any alerts for this monitor, despite occasionally filling up a data drive every now and then, so our shop must be humming along quite nicely.

And we were. Until there was an incident. And by ‘incident’ I mean that a member of my team (let’s call him Joe the DBA) decided that when it comes to striping files it is not necessarily the best thing to ever let them autogrow. And he has a valid point to consider (see above), so he created the multiple data files for the tempdb and turned off autogrow.

And that’s when OpsMgr started beeping at me. Well, not beeping, but it sent me a nasty email saying that the tempdb was running out of space. Actually, it said that there was 0% free space left. The database state view was critical, which means I had a red circle staring at me next to the tempdb for that server instance. Naturally I went and checked on the space and found that out of 24Gb of space allocated to tempdb about 23.999Gb were free.

Dumbfounded, I reset the health of the monitor. It went to green, then five minutes later back to red. Another quick check of the database shows that nothing has changed, it is still very much empty, and not full. So, what is going on? I decided to dive into the details and picked apart the SQL 2005 Monitoring management pack. I found the section of code that was collecting the information for the free space and stepped through in order to find the problem.

After a few iterations of the code I found a curious section:

if ((currentDB.Name == "tempdb") || (currentDB.Name == "master") || (currentDB.Name == "msdb"))
 {
 propertyBag.AddValue("DBSize", dbSize);
 propertyBag.AddValue("DBFree", 0);
 propertyBag.AddValue("DBPercentFree", 0);

Um…ok…so, if the database is master, msdb, or tempdb, the code sets the “DBPercentFree” variable to zero, regardless of what the actual amount would be. And why no love for model?

So, now I knew where the calculation was coming from. For some reason OpsMgr has decided to simply set the free space to be zero, but why am I not getting alerts for master and msdb? Well, I stumbled across this little tidbit of information, something I had known before but forgotten:

Databases that have autogrow set will never generate an alert regardless of the reported free space.

Wow.

Let me see if I can understand what you are doing.

If I have a database set to autogrow then you won’t bother to tell me if it is filling up? I mean, not even if I want to know if it is 50% full? You just won’t even entertain the notion that I could possibly want to know that my database is getting full, simply because I enabled the data files to autogrow? Really? That makes sense to someone? Because to me, an actual production DBA, it doesn’t. In fact, it is silly to think that because I have a database set to autogrow that I would not care to get an alert. I haven’t seen anything this stupid since I found out BizTalk doesn’t bother to clean up the database backups they force you to enable through their jobs.

Now, on top of that, you have decided that if I did disable autogrow on a database, say, oh I don’t know, how about tempdb, if I did disable autogrow on tempdb that I should be spammed with alerts. Because that is what you have done. I guess I hit the sweet spot with tempdb and this monitor, seems like a perfect storm of issues.

So, to recap:

1. Operations Manager will not monitor the free space available in a database that is set to autogrow.
2. If you want to know your free space available, you need to disable autogrow.
3. Disabling autogrow on tempdb will always generate an alert, regardless of the actual free space available.

Brilliant. Unfortunately, not very helpful. My current workaround is to disable the monitor for the tempdb database, which makes no sense since it is a fixed size and is EXACTLY the type of database I should be able to monitor with Operations Manager, but can’t.

Or I can go back and tell Joe the DBA to set the tempdb to autogrow. And then get another lesson in why allowing striped files to autogrow essentially has the potential to erase the benefits of striping.

Can’t we all just get along?