How to make self-backup log in msde?

Hi.
I am trying to automatically backup transaction log when error 9002
happened. So i have created appropriate job and alert to catch this error.
I have two instances of sql server under Windows 2000. One of them is full
SQL Server, another is msde.
When transaction log is full in full SQL Server error 9002 severity 17
state 2 is logged in sql server log and in Windows Application log. My
alert firing my job. All is fine.
But when transaction log is full in MSDE error 9002 severity 17 state 6 is
logged only in sql server log. It is not logged into Windows Application
log so my alert does not work.
So here is my questions:
1. Why MSDE does not log error into Windows Application log?
2. Why error 9002 has severity 19 in sysmessages table but is generated
with severity 17?
3. Why state of error 9002 differs under sql server and msde?

Thank you.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
> Hi.
> I am trying to automatically backup transaction log when error 9002
> happened. So i have created appropriate job and alert to catch this error.
> I have two instances of sql server under Windows 2000. One of them is full
> SQL Server, another is msde.
> When transaction log is full in full SQL Server error 9002 severity 17
> state 2 is logged in sql server log and in Windows Application log. My
> alert firing my job. All is fine.
> But when transaction log is full in MSDE error 9002 severity 17 state 6 is
> logged only in sql server log. It is not logged into Windows Application
> log so my alert does not work.
> So here is my questions:
> 1. Why MSDE does not log error into Windows Application log?
> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> with severity 17?
> 3. Why state of error 9002 differs under sql server and msde?
> Thank you.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it works
correctly - error 9002 is logged in both the SQL log and the Windows event
log, and the alert executes a job. This post suggests that restarting SQL
Server may help:

http://groups.google.com/groups?hl=...%40cpmsftngxa08

If that doesn't work, then perhaps you can give more details about your
setup - which version of MSDE do you have? Have you installed the latest
servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
error, is it logged in the Windows event log?

2. No idea, but 17 seems to be more correct, since it's the severity level
for insufficient resources.

3. State is not a fixed value - the process which raises the error can use
different states with the same error, to provide extra information about
exactly how the error was caused. In this case, it could be something like 2
for system databases and 6 for user ones, or 2 in Standard Edition, but 6 in
MSDE etc. I got state 6 with a user DB in MSDE.

Simon|||On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
>> Hi.
>> I am trying to automatically backup transaction log when error 9002
>> happened. So i have created appropriate job and alert to catch this
>> error.
>> I have two instances of sql server under Windows 2000. One of them is
>> full
>> SQL Server, another is msde.
>> When transaction log is full in full SQL Server error 9002 severity 17
>> state 2 is logged in sql server log and in Windows Application log. My
>> alert firing my job. All is fine.
>> But when transaction log is full in MSDE error 9002 severity 17 state 6
>> is
>> logged only in sql server log. It is not logged into Windows Application
>> log so my alert does not work.
>> So here is my questions:
>> 1. Why MSDE does not log error into Windows Application log?
>> 2. Why error 9002 has severity 19 in sysmessages table but is generated
>> with severity 17?
>> 3. Why state of error 9002 differs under sql server and msde?
>>
>> Thank you.
>>
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> works
> correctly - error 9002 is logged in both the SQL log and the Windows
> event
> log, and the alert executes a job. This post suggests that restarting SQL
> Server may help:
> http://groups.google.com/groups?hl=...%40cpmsftngxa08
> If that doesn't work, then perhaps you can give more details about your
> setup - which version of MSDE do you have? Have you installed the latest
> servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> error, is it logged in the Windows event log?
> 2. No idea, but 17 seems to be more correct, since it's the severity
> level
> for insufficient resources.
> 3. State is not a fixed value - the process which raises the error can
> use
> different states with the same error, to provide extra information about
> exactly how the error was caused. In this case, it could be something
> like 2
> for system databases and 6 for user ones, or 2 in Standard Edition, but
> 6 in
> MSDE etc. I got state 6 with a user DB in MSDE.
> Simon

Thank you for answer. OS is Windows 2000 SP4. I have MSDE 2000 SP3a and
MSDE 2000 Release A instances. Both do not write error 9002 into Windows
error log identically. SQL 2000 instance in the same OS works fine.
I have rebooted computer few times. It does not resolved my problem. I
think rebooting computer is equivalent to restarting SQL Server. Am i
wrong?
I got state 2 overloading user DB in MSDE -> Windows error log is not
written
State 6 overloading identical user DB in SQL Server -> Windows error log
is written correct
I will try to execute RAISERROR...WITH LOG and will post its results later.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
>> Hi.
>> I am trying to automatically backup transaction log when error 9002
>> happened. So i have created appropriate job and alert to catch this
>> error.
>> I have two instances of sql server under Windows 2000. One of them is
>> full
>> SQL Server, another is msde.
>> When transaction log is full in full SQL Server error 9002 severity 17
>> state 2 is logged in sql server log and in Windows Application log. My
>> alert firing my job. All is fine.
>> But when transaction log is full in MSDE error 9002 severity 17 state 6
>> is
>> logged only in sql server log. It is not logged into Windows Application
>> log so my alert does not work.
>> So here is my questions:
>> 1. Why MSDE does not log error into Windows Application log?
>> 2. Why error 9002 has severity 19 in sysmessages table but is generated
>> with severity 17?
>> 3. Why state of error 9002 differs under sql server and msde?
>>
>> Thank you.
>>
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> works
> correctly - error 9002 is logged in both the SQL log and the Windows
> event
> log, and the alert executes a job. This post suggests that restarting SQL
> Server may help:
> http://groups.google.com/groups?hl=...%40cpmsftngxa08
> If that doesn't work, then perhaps you can give more details about your
> setup - which version of MSDE do you have? Have you installed the latest
> servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> error, is it logged in the Windows event log?
> 2. No idea, but 17 seems to be more correct, since it's the severity
> level
> for insufficient resources.
> 3. State is not a fixed value - the process which raises the error can
> use
> different states with the same error, to provide extra information about
> exactly how the error was caused. In this case, it could be something
> like 2
> for system databases and 6 for user ones, or 2 in Standard Edition, but
> 6 in
> MSDE etc. I got state 6 with a user DB in MSDE.
> Simon

I have executed RAISERROR ... WITH LOG statement in my MSDE database. It
does not write into the Windows log. But i have found some warning
messages in Application log. Here is my application for today. May be you
can give me some advice about that warnings:

03.04.2004,19:53:27,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'sqlmap70.dll'
version '2000.80.194' to execute extended stored procedure
'xp_get_mapi_profiles'.
..
03.04.2004,19:53:21,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'xpstar.dll'
version '2000.80.194' to execute extended stored procedure
'sp_MSgetversion'.
..
03.04.2004,19:33:42,EventSystem,Warning,Firing Agent,4100,N/A,W2000,The
COM+ Event System failed to create an instance of the subscriber
{6295DF2D-35EE-11D1-8707-00C04FD93327}. CoCreateInstanceEx returned
HRESULT 8000401A.
03.04.2004,19:33:37,WinMgmt,Warning,None,41,N/A,W2000,WMI ADAP was unable
to create object index 1848 for Performance Library RSVP because no value
was found in the 009 subkey
03.04.2004,19:33:32,WinMgmt,Warning,None,41,N/A,W2000,WMI ADAP was unable
to create object index 2212 for Performance Library MSDTC because no value
was found in the 009 subkey
03.04.2004,19:33:31,SQLSERVERAGENT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLSERVERAGENT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:30,SQLAgent$FAVGAME,Information,( 2),101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:22,MSSQLSERVER,Information,(2),17 055,S-1-5-21-1699048451-83243044-507081533-1009,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 8128; Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored procedure 'xp_qv'.
..
03.04.2004,19:33:14,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17052; Recovery complete.
..
03.04.2004,19:33:10,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 3455; Analysis of database
'cashbasedmir' (10) is 100% complete (approximately 0 more seconds)
..
03.04.2004,19:33:06,SQLAgent$ALERT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:33:04,EvntAgnt,Information,None,2018 ,N/A,W2000,The
description for Event ID ( 2018 ) in Source ( EvntAgnt ) cannot be found.
The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:57,SceCli,Information,None,1704,N /A,W2000,Security policy
in the Group policy objects are applied successfully.
03.04.2004,19:32:50,SQLSERVERAGENT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLSERVERAGENT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17126; SQL Server is ready for
client connections
..
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 19013; SQL server listening on
192.168.0.4:1433, 127.0.0.1:1433.
.."
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 19013; SQL server listening on
TCP, Shared Memory, Named Pipes.
.."
03.04.2004,19:32:45,MSSQLServer,Warning,(8),19011, N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQLServer ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:45,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17834; Using 'SSNETLIB.DLL'
version '8.0.194'.
..
03.04.2004,19:32:43,SQLAgent$FAVGAME,Information,( 2),101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:39,SQLAgent$ALERT,Information,(2) ,101,N/A,W2000,The
description for Event ID ( 101 ) in Source ( SQLAgent$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: The event log file is corrupt..
03.04.2004,19:32:37,MSSQL$FAVGAME,Warning,(8),1901 1,N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQL$FAVGAME ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:34,MSSQLSERVER,Information,(2),17 055,N/A,W2000,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17125; Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
.."
03.04.2004,19:32:34,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17124; SQL Server configured
for thread mode processing.
..
03.04.2004,19:32:34,MSSQL$ALERT,Warning,(8),19011, N/A,W2000,The
description for Event ID ( 19011 ) in Source ( MSSQL$ALERT ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: (SpnRegister) : Error 1355.
03.04.2004,19:32:33,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17162; SQL Server is starting
at priority class 'normal'(1 CPU detected).
..
03.04.2004,19:32:32,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17104; Server Process ID is
728.
..
03.04.2004,19:32:32,MSSQLSERVER,Information,(2),17 055,N/A,W2000,The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information
or message DLL files to display messages from a remote computer. The
following information is part of the event: 17052; Microsoft SQL Server
2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)|||"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
news:opr5wgspdvfk7eds@.124-204.dialup.alfacom.net...
> On Sat, 3 Apr 2004 12:37:08 +0200, Simon Hayes <sql@.hayes.ch> wrote:
> > "Igor Solodovnikov" <igor@.helpco.kiev> wrote in message
> > news:opr5ullxv2fk7eds@.124-225.dialup.alfacom.net...
> >> Hi.
> >> I am trying to automatically backup transaction log when error 9002
> >> happened. So i have created appropriate job and alert to catch this
> >> error.
> >> I have two instances of sql server under Windows 2000. One of them is
> >> full
> >> SQL Server, another is msde.
> >> When transaction log is full in full SQL Server error 9002 severity 17
> >> state 2 is logged in sql server log and in Windows Application log. My
> >> alert firing my job. All is fine.
> >> But when transaction log is full in MSDE error 9002 severity 17 state 6
> >> is
> >> logged only in sql server log. It is not logged into Windows
Application
> >> log so my alert does not work.
> >> So here is my questions:
> >> 1. Why MSDE does not log error into Windows Application log?
> >> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> >> with severity 17?
> >> 3. Why state of error 9002 differs under sql server and msde?
> >>
> >> Thank you.
> >>
> >> --
> >> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
> > 1. I've also set this up (MSDE2000 SP3a on Windows XP Pro SP1), and it
> > works
> > correctly - error 9002 is logged in both the SQL log and the Windows
> > event
> > log, and the alert executes a job. This post suggests that restarting
SQL
> > Server may help:
http://groups.google.com/groups?hl=...%40cpmsftngxa08
> > If that doesn't work, then perhaps you can give more details about your
> > setup - which version of MSDE do you have? Have you installed the latest
> > servicepack? Also, if you execute RAISERROR ... WITH LOG to raise a test
> > error, is it logged in the Windows event log?
> > 2. No idea, but 17 seems to be more correct, since it's the severity
> > level
> > for insufficient resources.
> > 3. State is not a fixed value - the process which raises the error can
> > use
> > different states with the same error, to provide extra information about
> > exactly how the error was caused. In this case, it could be something
> > like 2
> > for system databases and 6 for user ones, or 2 in Standard Edition, but
> > 6 in
> > MSDE etc. I got state 6 with a user DB in MSDE.
> > Simon
> I have executed RAISERROR ... WITH LOG statement in my MSDE database. It
> does not write into the Windows log. But i have found some warning
> messages in Application log. Here is my application for today. May be you
> can give me some advice about that warnings:

<snip
It looks like something is fundamentally wrong with your installation, if
you get so many DLL errors. Personally, I would completely remove all MSDE
instances on the server and reinstall it, instead of trying to identify each
error:

http://support.microsoft.com/defaul...3&Product=sql2k

By the way, the error 17055 suggests that you are truncating the log even if
the database is in full recovery mode. This isn't a good idea, because then
you can't recover your previous backups if you need to:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||"Igor Solodovnikov" <igor@.helpco.kiev> wrote in message news:<opr5ullxv2fk7eds@.124-225.dialup.alfacom.net>...
> Hi.
> I am trying to automatically backup transaction log when error 9002
> happened. So i have created appropriate job and alert to catch this error.
> I have two instances of sql server under Windows 2000. One of them is full
> SQL Server, another is msde.
> When transaction log is full in full SQL Server error 9002 severity 17
> state 2 is logged in sql server log and in Windows Application log. My
> alert firing my job. All is fine.
> But when transaction log is full in MSDE error 9002 severity 17 state 6 is
> logged only in sql server log. It is not logged into Windows Application
> log so my alert does not work.
> So here is my questions:
> 1. Why MSDE does not log error into Windows Application log?
> 2. Why error 9002 has severity 19 in sysmessages table but is generated
> with severity 17?
> 3. Why state of error 9002 differs under sql server and msde?
> Thank you.

I do this by defining a Database Maintenance plan, but leaving the
jobs this generates disabled. I then use msdb.dbo.sp_start_job to call
the job when wanted. Not only does this method log to the msdb history
tables, but old backups, execution logs, etc are cleaned up per the
retention settings.

You may already be "losing data" when you get the 9002 error. Take a
look at performance alerts and set a threshold (say 70%) so the system
may be able to backup the log and recover space before transactions
fail because the log is full.

HTH
JIV

John W. Pollins IV
Readi Operations DBA
Equitable Resources Inc