Scheduling and Notification in MSDE
Scheduling Tasks
One of the most powerful tools of the MSDE Manager combined with MSDE for SQL Server 2000 is the ability to schedule jobs and tasks. This article discusses how the MSDE Manager utilises the SQL Server Agent. We will assume in this article you have setup and configured SQL Server Agent and it is working correctly on your server. We have also assumed the SQL Server agent users has appropriate permission to carry out these tasks. We will look at how we can schedule essential maintenance jobs to run out side of peak periods and we can be notified of these jobs completing successfully or failing.
Scheduling a Database Backup
You can schedule a database backup to take place outside of peak periods. The main advantage of this, is the reduced overhead on the server, users are less likely to experience performance issues if the backup is taken when few users are connected.
To schedule a backup job using MSDE Manager
In MSDE Manager expand your database instance and expand the <Database> folder. Right click on the database you want to backup and select backup database
Fill in the necessary details and setup the job as you would want it to run and the select <Schedule>
This takes you to screen that will allow you to schedule your backup job.
In this example we have scheduled our backup job to run everyday at 00:00:00 (midnight) this is when my database is used the least.
Clicking okay creates a new job that will execute the backup. The job will appear in the <Jobs> folder under the <Job Server>
Scheduling Maintenance Tasks
In this section we will discuss how to schedule the DBCC CHECKDB statement, this process can be followed for any maintenance statement.
To create the maintenance job to run the DBCC CHECKDB statements expand your SQL Server instance. Expand the <Job Server> and right click on the <jobs> folder and select <Create New Job>
From the New Job screen select <Add> to add a new step
Give the job a name and select the language as being “Transact-SQL Subsystem (TSQL)” and type DBCC CHECKDB this will run this statement against the current database.
Then select the schedule tab and click and fill in when you want the DBCC statement to run. I have chosen once a week on Sunday. The day the database is used the least in my organisation.
Click <OK> and <OK> and the job will be created and it will appear in the “Jobs Server” under the <jobs> folder.
The jobs will run as long as the SQL Server Agent service is running on your server.
In conclusion, the MSDE Manager can be used to create scheduled jobs that can be used to maintain your database and allow you to run jobs outside of normal office hours, when the database is not supporting a peak load. This can be a key factor in the administration of large production databases.
Click here for Product Information on MSDE Manager
Click here for Product Information on Express Agent
View Screenshots >> Download free trial >> Buy online >>