Automating Transaction Log Dumps

[Kevin G.]

I am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor&#34. The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'

I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.

I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.

[Cindy]

Try putting the full path for isql and adding an output file (-o), also with the full path. Maybe the output file will give you more information. Also, the log full alert is not completely reliable unless you first run dbcc checktable (syslogs) to update the system tables with the current size of the used log.

------------
Kevin G. at 5/5/99 2:44:23 PM

I am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor&#34. The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'

I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.

I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.

[Kevin G]

Cindy,
Thanks for you reply. I tried the dbcc checktable (syslogs) and adding the output file. The NT Performance Monitor's alert is still kicking off but it is not executing the isql command file (dump.sql). This should work, but it is something that is keeping the command from executing. Do you know of anything else that I can try? Thanks, Kevin


------------
Cindy at 5/10/99 10:48:09 AM

Try putting the full path for isql and adding an output file (-o), also with the full path. Maybe the output file will give you more information. Also, the log full alert is not completely reliable unless you first run dbcc checktable (syslogs) to update the system tables with the current size of the used log.

------------
Kevin G. at 5/5/99 2:44:23 PM

I am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor&#34. The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'

I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.

I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.