SQL Server Agent – Creating an Alert for ‘Could Not Allocate Space’ 1105 Errors

This article is the third in my new series that focuses on alerts and errors within the SQL Server Agent subsystem. In this article I am going to describe how to create a specific alert for “Could Not Allocate Space” 1105 errors. I will be making reference to my previous article, which described creating custom alert procedures.

Let’s begin. First, complete Steps 1 through 4 from my previous article, “SQL Server Agent: Creating Alert definition”, making the following changes for step 4:

1. Start the SQL Server administrative console – Enterprise Manager.
2. Make a connection to a database server, drill down the SQL server
   objects to Alerts (your_database_server_name -> Management
   -> SQL Server Agent -> Alerts) and place a mouse cursor on it.
3. Select “New Alert…” in the context-dependent menu (accessed by right-clicking
   on the details pane).
4. In the “New Alert Properties – (your_database_server_name)” dialog window,
   define the following options:

On “General” bookmark:

• 
  Name = “Not enough free space for Data”
• 
  Type = “SQL Server event alert”
• 
  Error number = “1105”
• 
  Database name = “(All Databases)”

On “Response” bookmark:

• 
  Click “New operator…” button (we will discuss Operators in future articles)
• 
  In “New Operator Properties – (your_database_server_name)” dialog window, input
  DBA in the “Name” field and specify in the “Net send address” field the net send address of the newly created operator (in our case
  it should be the domain account name for the database administrator or his/her personal computer NetBIOS-name);
• 
  Test the notification functionality by pressing the “Test” button…shortly afterwards your
  database administrator should see a very strange message :-).
• 
  Press the “OK” button 2 times.

ATTENTION: The alert you have just created (i.e. an alert to notify you when free database space is less then 90%) will not fire unless the SQL Server Agent service has been started. The best practice is to always start SQL Server Agent with your database server. To ensure this, enable the “Auto-start service when OS starts” option for services SQLServerAgent in SQL Server Service Manager window (double click on the corresponding icon in the system tray).

See All Articles by Columnist Alexzander Nepomnjashiy