SQL 2000 Alerts and Jobs

Make it your goal to automate boring, repetitive tasks.  I would always choose a morning of to automate followed by and afternoon testing, rather than spending the whole day doing the same monotonous chore.

SQL Server 2000 provides a separate SQL Server Agent Service to handle Jobs and Alerts.  The Jobs themselves are stored in the MSDB system database.  You can check the configuration by going to Services, SQLServerAgent.  Like the main SQL Server service, you need an account with local administration rights.  Configure the account so that it never expires and never has to change its password.  I would also choose to have the service start automatically.

Once again there are two ways of creating Jobs, Transact SQL or the Enterprise Manager, in the case of Alerts, I would strongly favour the latter's GUI interface to scripting.  Having recommended the Enterprise Manager, there will still be scope for good scripts once we start to configure the Job.

There is also a second use of the SQLServerAgent and that is to notify you if there is something wrong.  The skill is in anticipating what could go harm the server, foresee what could go wrong in a table or query.  Once you have identified a potential problem, you can combine business with pleasure and devise a response to the potential problem.

Examples of Maintenance Jobs

• Backup of Database
• Backup of Logs
• Importing Sales Data
• Updating Parts Data

Getting Started

Enterprise Manager, Management, SQL Server Agent

Components you need to configure

• Job
• Operator
• Exchange 2000 for the operator

Two key concepts

• raise alert
• xp_sendmail

 *