Posted by Mike on 10/19/69 11:26

How do I prevent SQL Server 2000 from posting successful backup
completion messages to the Windows 2000 Application Event Log?
I have scheduled jobs which backup my transaction logs on 50+ databases
and it always writes to the Windows 2000 application event log upon
completion.
Due to the frequency of the jobs it only takes a day for the
Application Log to fill up, which is causing other jobs to get hung up
when trying to write to it. On my Windows 2000 server, I have the
application log event viewer setting correctly set as:
"When maximum log size is reached - Overwrite events as needed" but for
some reason this setting no longer applies like it did for the past
three years. SQLServerAgent and MSSQLSERVER both run under a local
Admin account, without a domain.
When I researched how prevent SQL Server from logging this type of
message, I found that I can use sp_update_alert to disable this
message, but I cannot find
the message_id to correctly disable this message. In sysmessages, the
message I am trying to suppress is
error:18265 severity:10 dlevel:128 description:Log backed up: Database:
%1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of
dump devices: %7!d!, device information: (%8). mslangid:1033

I tried calling sp_update_alert as follows:

exec sp_update_alert @name = 'Log backed up: Database: %1, creation
date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump
devices: %7!d!, device information: (%8).', @enabled = 0

but got the error message:

Server: Msg 14262, Level 16, State 1, Procedure sp_update_alert, Line
105
The specified @name ('Log backed up: Database: %1, creation date(time):
%2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!,
device inf') does not exist.

Looks like it can only handle 128 characters.

How can I disable this message from being logged in the Application
Log? Or alternatively, how can I get the event viewer to behave as
expected and
"Overwrite events as needed"?

Thanks,

Mike Orlando

[Back to original message]