Reader Challenge: Handling File Growth

Test your SQL Server savvy

[Editor's Note: Subscribe to the free newsletter SQL Server Magazine UPDATE (http://www.sqlmag.com/resources/ email/update/updatesub/cfm) to get next month's SQL Server problem. Winners receive $100 or a $50 gift certificate to Fatbrain.com, an online high-tech bookstore.]

AND THE WINNERS ARE . . .

Congratulations to Claudia Resendez of Sungard Data Trust, Charlotte, North Carolina, and to Brian Andrews of Watson Wyatt Worldwide, Washington, D.C. Claudia won first prize of $100 for the best solution to the Reader Challenge. Brian won second prize of a $50 gift certificate to Fatbrain.com.

Problem

Eric has managed SQL Server installations since version 6.0 and is happy as he upgrades his company's SQL Server machines to 7.0. He knows that the new release contains a lot of good stuff, so he upgrades the servers as soon as the developers give him the go-ahead. While he's at it, Eric tries to consolidate the databases onto fewer servers.

Eric quickly realizes that he needs a way to manage disk space. He prefers to allocate storage space in advance, which gives him control over disk space usage. But he dreads the 1105 error message Filegroup full. Eric wants to be notified each time automatic growth occurs. The notification doesn't have to be in realtime, but he wants to receive email notification within the day. How can Eric achieve this?

Solution

Unfortunately, no alert fires when automatic growth occurs. Message 3129 seems to fit the bill, but it doesn't fire, even when you specify Always write to NT Eventlog. So Eric will have to poll regularly to find out whether autogrowth occurred and generate tailored alerts. Eric needs to: