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:

  1. Add a user-defined error message stating that autogrowth occurred, then add an alert for that message, which will send email notification to Eric.
  2. Create a table to hold database ID, file ID (within the database), and file size at previous check.
  3. Create a stored procedure that uses a cursor to loop through each database, then loops through each file in the sysfile table, and compares the current size against the stored file size. If the current file is larger than it was at the previous execution, autogrowth has occurred. Eric can use RAISERROR to trigger the user-defined error message. He also needs some housekeeping code to refresh the current file size, add entries for new databases, etc.
  4. Schedule a job to execute the procedure as often as he deems appropriate.