How Do I Prevent SQL 2000 From Posting This Message To The Event Viewer - Application Log

[Mike]

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

[Erland Sommarskog]

Mike (mj*******@campsystems.com) writes:

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

Look in msdb..sysalerts. You should find the message there. If you don't,
I suppose that sp_update_alert is not your guy after all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mike]

I agree, sp_update_alert is not the answer. I actually checked the
sysalerts table and only found 10 rows in this table. None matched the
message which I'm trying to suppress. Since I'm looking for a
message_id, I also tried calling sp_altermessage 17055, 'WITH_LOG',
'false' using the event ID 17055 found in the Windows Event Viewer and
also tried with 18265, which is the first part of the event
description, but no luck. Any other suggestions? Thanks.

[Erland Sommarskog]

Mike (mj*******@campsystems.com) writes:

I agree, sp_update_alert is not the answer. I actually checked the
sysalerts table and only found 10 rows in this table. None matched the
message which I'm trying to suppress. Since I'm looking for a
message_id, I also tried calling sp_altermessage 17055, 'WITH_LOG',
'false' using the event ID 17055 found in the Windows Event Viewer and
also tried with 18265, which is the first part of the event
description, but no luck. Any other suggestions? Thanks.

Since I don't know this stuff myself very well, I asked in our internal MVP
forum, and Jasper Smith who I trust to know this very well told me that
there is no way to prevent the logging to event log.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[GP]

Erland Sommarskog wrote:

Mike (mj*******@campsystems.com) writes:

I agree, sp_update_alert is not the answer. I actually checked the
sysalerts table and only found 10 rows in this table. None matched the
message which I'm trying to suppress. Since I'm looking for a
message_id, I also tried calling sp_altermessage 17055, 'WITH_LOG',
'false' using the event ID 17055 found in the Windows Event Viewer and
also tried with 18265, which is the first part of the event
description, but no luck. Any other suggestions? Thanks.

Since I don't know this stuff myself very well, I asked in our internal MVP
forum, and Jasper Smith who I trust to know this very well told me that
there is no way to prevent the logging to event log.

This might be an obvious thing to look at, but have you tried this..?

1. Open the properties of the job.
2. Go to the Notifications tab.
3. Make sure that the "Write to the Windows Application Event Log"
option is un-checked.

[Erland Sommarskog]

[Posted and mailed]

"Mike" <mj*******@campsystems.com> writes:

I agree, sp_update_alert is not the answer. I actually checked the
sysalerts table and only found 10 rows in this table. None matched the
message which I'm trying to suppress. Since I'm looking for a
message_id, I also tried calling sp_altermessage 17055, 'WITH_LOG',
'false' using the event ID 17055 found in the Windows Event Viewer and
also tried with 18265, which is the first part of the event
description, but no luck. Any other suggestions? Thanks.

I spoke too soon when I said that it is not possible.

You can use trace flag 3226 to suppress these messages.

There is also a related trace flag 3001 that suppresses logging
to msdb.backuphistory.

These flags are undocumented with all that means. On the other hand,
I got them from a Microsoft engineer who said it was OK to share them.

--
Erland Sommarskog, Stockholm, es****@sommarskog.se

[Mike]

Thanks GP, but when I uncheck the "Write to the Windows Application
Event Log"
option the job still writes to the Application Event Log. I noticed
some strange behavior when unchecking this option. When it is checked
the drop down that's associated with it is showing "When the job fails"
so before unchecking it I changed the drop down to "When the job
succeeds" and then unchecked the box. After applying the changes and
running the job, the Application Event Log still gets written to, and
when I go back to the job I just changed, I see the disabled drop down
showing "When the job fails" again. I want to see the messages getting
written if the job fails, but only suppress the informational messages
upon success, but either way this method fails to suppress the
successful informational messages. FYI, I have never seen the
Transaction Log Backup job fail.

[Mike]

Thanks Erland but I'm still not achieving my objective. Neither DBCC
TRACEOFF (3226) nor DBCC TRACEOFF (3001) stops the logging. I tried it
on the Master and the msdb databases and nothing changed. Should this
command be run against a different database? Mike

[Erland Sommarskog]

Mike (mj*******@campsystems.com) writes:

Thanks Erland but I'm still not achieving my objective. Neither DBCC
TRACEOFF (3226) nor DBCC TRACEOFF (3001) stops the logging. I tried it
on the Master and the msdb databases and nothing changed. Should this
command be run against a different database? Mike

Of course not. TRACEOFF turns off the trace flag! :-)

I didn't specify how to should activate the flag, because I was not
given any details on that. The simplest might be to add to the command
line (you can do this from Enterprise Manager), but it requires a server
restart).

I believe that DBCC TRACEON from a query window affects that process only,
nut adding -1 affects the server level. But I am very foggy on that point.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[monkeybrains]

Just wanted to add a usage note here. I was pestered by the same
situation (event log overload from transaction replication on 40+ DBs).

I was able to suppress the backup / restore events in the Application
Event Log, resulting from SQL Log Shipping with these steps:

1) Go to the SQL Server Properties via Enterprise Manager (i.e. right
click the server and go to properties)
2) General Tab
3) Startup Parameters
4) Add new startup parameter--> /T3226
5) Apply / close dialog
6) Restart SQL Server instance

I am still able to view my backup / copy / restore history from the Log
Shipping Monitor or from the histories of each individual SQL Agent
job. That is much cleaner for my purposes.

Good luck -
Dave
Erland Sommarskog wrote:

Mike (mj*******@campsystems.com) writes:

Thanks Erland but I'm still not achieving my objective. Neither DBCC
TRACEOFF (3226) nor DBCC TRACEOFF (3001) stops the logging. I tried it
on the Master and the msdb databases and nothing changed. Should this
command be run against a different database? Mike

Of course not. TRACEOFF turns off the trace flag! :-)

I didn't specify how to should activate the flag, because I was not
given any details on that. The simplest might be to add to the command
line (you can do this from Enterprise Manager), but it requires a server
restart).

I believe that DBCC TRACEON from a query window affects that process only,
nut adding -1 affects the server level. But I am very foggy on that point.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp