Taking a Proactive Approach with SQL Server Agent

Whether creating Jobs and/or Alerts, Operators is the component which allows a DBA to configure SQL Server to be able to contact anyone when an alert occurs.

Note: In order to configure notifications from a job or an alert, mail support must be configured at the server. The SQLServerAgent service collaborates with the SQLAgentMail agent, which is used to send e-mail to assigned operators when an alert has fired. It is necessary to have a mail account, either through Exchange or possibly via an Internet e-mail account, to configure mail support. There are topics that can be found in SQL Server Books Online that detail the steps in configuring mail support.

Once mail support is configured on your SQL Server, you can configure the SQLServerAgent and MSSQL Server services to use a mail profile to send and receive e-mail.

1. Launch Enterprise Manger.
    
2. Expand the tree objects until you get to Management.
    
3. Right-click the SQL Agent and select properties.
    
4. In the properties you can select the mail profile configured to receive e-mail.
    
5. Use the Test button to verify the profile.
    
6. Apply all changes and restart the SQLServerAgent service.

You can now configure your alerts to notify operators when an event has occurred.

In order to keep your database performance consistent, there are specific tasks that need to be accomplished. T-SQL gives you database consistency statements to verify the physical and logical state of a database - in turn keeping its consistency. Since numerous tasks need to be performed, we can avoid creating multiple jobs for each database by using the Database Maintenance Plan Wizard. The Database Maintenance Plan Wizard will not be covered in this article; however, this article will point out the maintenance commands briefly.

There is a great Database Maintenance Plan article on this site entitled Inside SQL Server Maintenance Plans by Andrzej Kozlowski (http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp) for your reading.

Database console commands are grouped into specific categories. The statement categories are as follows:

• Maintenance Statements: Maintenance tasks on a database, index or filegroup. (for example: DBCC DBREINDEX, DBCC SHRINKFILE)
   
• Misc. Statements: Enabling row-level locking or removing a DLL from memory. (for example: DBCC [dllname] (FREE), DBCC TRACEON, DBCC ROWLOCK)
   
• Status Statements: Status Checks (for example: DBCC SHOWCONTIG, DBCC SHOW STATISTICS)
   
• Validation Statements: Validation operations on a db, table, index, catalog, filegroup, systables or allocation of db pages. (for example: DBCC CHECKDB, DBCC NEWALLOC, DBCC CHECKCATALOG)

These commands can be processed separately within the SQL Agent Jobs component, or packaged into one plan by using the Database Maintenance Plan Wizard. Nonetheless, after going through the action menu and selecting the console commands specific to your environment; you would need to schedule the plan accordingly. The maintenance wizard works in collaboration with SQL Agent by utilizing the Job's scheduling utility.