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

Alexzander Nepomnjashiy
Alexzander Nepomnjashiy
I am a Microsoft SQL Server Database Designer for Neo-Systems North-West - a security services, consulting, and training company. I have over eight years of experience in the IT field. I am currently working on several projects which involve the deployment of Microsoft Windows NT Server/Microsoft SQL Server within an enterprise business/financial environment. My typical role in these projects includes extending and improving our clients' corporate ERP systems to manage retail sales data, predict market changes and calculate trends for future market situations (DSS, OLAP). Also among my responsibilities are the design and administration of Microsoft SQL Server 7.0/2000 databases. I am available to work on a contract basis for the following types of projects: - Technical authoring, including books, articles, and white papers; - Network and systems design and analysis; - Database and software development and analysis; - Short-term consulting projects. I hope you find these articles useful. If you have any ideas for future articles (in a field of Microsoft SQL Server databases design, administration, performance optimization), or if you have anything to say about the ones below, please do not hesitate to contact me! Feel free to forward these articles to all interested associates. Thank You!

Latest Articles