How do I test run a alert and a back up job?

[sqlturbo]

I have set up a alert to fire when the log file gets filled up, and execute a back-up-the-log-file job.
How do I test to check if the alert and the job work or not? And also, is there way to specify in the alert to run the job, say, when the log file is 60% full. Right now, it is set to run only when the log file is full.

Thanks for any help.

 

[tlbroadbent]

Which version of SQL Server are you running? Why not schedule regular transaction log backups? That is a normal procedure. By waiting until the log is full or nearly full, you risk aborting processes.

If you are using SQL Server 7 or 2000, you can open the alert properties and view how often it has fired. You can view the job history to see if a job worked. You can have the SQLAgent send you a message on Job completion, success or failure. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sqlturbo]

I do have regular backups but wanted to check if the alert triggers the job when the log file gets full.

Is there a way to 'fool' the alert and run it even if the log file is not full. That way I can test run it without having to have a full log file.

Thanks again.

 

[tlbroadbent]

You can use Raiserror to trigger the Alert.

RAISERROR (<Error ID>, 16, 1) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sqlturbo]

Terry,

I used Raiserror(9001,10,1),which should have set off the alert: &quot;The log for database '%.*|s' is not availbale&quot;, but instead I get the following message: &quot;Error number 9001 is invalid. The number must be from 13000 through 2147483647.&quot; Likewise is the case with Raiserror(926,0,1). The message I get is &quot;Error number 926 is invalid. The number must be from 13000 through 2147483647&quot;.

If these errors are pre-defined in SQL Server, shouldn't I be able to test run them and use them? What,however, works with Raiserror is an alert created with an error number > 50000 but that too does not send mail when I trigger it off with the Raiserror command.

Is there a way to test run the alerts?

I appreciate the help.

L'Tyan.

 

[tlbroadbent]

I apologize for the misinformation. I didn't test Raiserror(926,16,1) before posting. Obviously, I should have done so.

You should be able to define your own message and alert over 50000 and use Raiserror to trigger the alert. The alert could send an appropriate message. That I have done.

What steps have you taken to create the Alert and set up the response to the alert? Are you doing this through Enterprise Manager or via system stored procedures in Query Analyzer? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sqlturbo]

I created the Alerts in EM and via stored procedures in QA. They work fine either way. Though I do backup the database on a daily basis, and don't need to monitor the growth of the log file, but could I create a user defined alert to monitor the growth of the log file. I'd like it to alert me when the data and the log file is 90% full. This is because, I don't see the 'SQL Server performance condition alert' option in the drop down for the 'Type' in the create New Alert dialog box when you right click Alerts and hit 'New Alert...'.

Based on that I have two questions. Firstly, can I create an alert like that and secondly, how could I get the 'SQL Server performance condition alert' option to appear in the drop down. (This SQL Server runs on a cluster, if that has got anything to do with it not appearing in the drop down).

Thanks again for the feedback.