Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rpeoplesjr
Starting Member
1 Post |
 Posted - 2002-06-21 : 12:25:58
|
| Is there a way to set up an alert that will tell me when a scheduled maintenance job (Like transaction log backup) is running too long or that it has not completed. I had a transaction log backup run for over 15 hours one night, thus all my other jobs would not run. Any suggestions would be great. |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-21 : 14:24:28
|
| Add two steps to your job -- one before the step that actually executes your backup, and one after. The first step would insert a GETDATE() value and the name of the job into JOB_MONITOR table that you can set up somewhere. The other step would delete it when the job is done. Then you can setup another job to check JOB_MONITOR table periodically and e-mail you if the datestamp value in that table starts to get old...Edited by - izaltsman on 06/21/2002 14:25:29 |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-21 : 14:44:04
|
| Here's one way (you need to register but its very quick)[url]http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=144&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1[/url]You can easily modify it to use SMTP mail or net send if SQLMail is not configured.Say you want to know when your job runs more than 1 hour you could1) Create a new job called maintenance monitor calling the stored procedure with the required parameters2) Set it to run at a suitable interval or if you don't want it running that often then1) Create a new job called maintenance monitor (no schedule)2) For its first step add a TSQL step (this would wait for 30 minutes) WAITFOR DELAY '00:30:00'3) For its second step call the stored procedure (with 30 as the second parameter)4) In your maintenance plan job insert a new TSQL first step exec msdb.dbo.sp_start_job 'maintenance monitor'Thus when 30 minutes is up the stored procedure will be called and alert you if your maintenance plan is still runningHTHJasper Smith |
|
|
|
|
|
|
SQL Server Maintenance Jobs