Deleted aged status messages can leave orphaned database entries (306315)


The information in this article applies to:

  • Microsoft Systems Management Server 2.0
This article was previously published under Q306315

SYMPTOMS

When a Systems Management Server (SMS) 2.0 site stores a large number of status messages that meet the "Delete aged status message" criteria, the act of deleting those status messages may leave orphaned status message insertion strings and attributes in the SMS database. Also, these orphaned records may result in an error during a Service Pack upgrade. The following error may be displayed during the upgrade:

Fatal error. Setup cannot upgrade the SQL database.
Contact your SQL Administrator.

The last entries in the C:\SMSSetup.log may show the following:
SqlExecuteAsync < Delete From StatusMessageInsStrs Where RecordID Not in(Select RecordID from StatusMessages)>
Cannot execute sql command Delete From StatusMessageInsStrs Where RecordID Not in(Select RecordID from StatusMessages)

CAUSE

The SMS database monitor (smsdbmon) is the process that is responsible for the deletion of aged status messages from the database. During the deletion process, if more than 1,000 status messages meet the deletion criteria, the first 1,000 status messages are deleted, but the messages may orphan insertion strings and attributes that are related to the status messages. (See the "Additional Information" section for more information about this.) These orphaned records are located in two Microsoft SQL Server database tables that can eventually consume a lot of database space. It may also be necessary to manually remove these orphaned records before a Service Pack can be successfully applied. Contact Product Support for more infomation about manually removing these records.

RESOLUTION

To resolve this problem, obtain the latest service pack for Systems Management Server version 2.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

288239 How to obtain the Latest Systems Management Server 2.0 service pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft Systems Management Server 2.0 Service Pack 5.

MORE INFORMATION

The Delete Aged Status Messages task (available by opening Site Settings, opening Database Maintenance, and then clicking Tasks) is enabled by default to delete aged status messages daily (between 12:00 A.M. and 5:00 A.M.). When it is run by the SMS database monitor, the routine to delete aged status messages deletes up to 1,000 aged status messages if their expiration time is met. If more than 1,000 aged status messages exist, only the first 1,000 are deleted in that batch. This is an effort by the SMS database monitor not to delete all expired status messages in one operation because doing so could result in an attempt to delete tens, if not hundreds of thousands, of status messages at one time in large hierarchies. That would have a negative impact on SQL Server performance and transaction log usage during this time.

The default age for SMS status messages to be stored in the database is determined by Status filter rules. The following two default Status filter rules are applied by default:
  • Write audit messages to the site database and keep them for 180 days
  • Write all other messages to the site database and keep them for 30 days
To determine if an SMS primary site is exhibiting the symptoms that are described in this article, run the following SQL queries against the SMS database by using ISQL/W:
  • If the following query returns a non-zero value, there are orphaned records in the StatusMessageInsStrs table:

    Select Count(*) From StatusMessageInsStrs Where RecordID Not In (Select RecordID From StatusMessages)

  • If the following query returns a non zero-value, there are orphaned records in the StatusMessageAttributes table:

    Select Count(*) From StatusMessageAttributes Where RecordID Not In (Select RecordID from StatusMessages)

Modification Type:MinorLast Reviewed:4/14/2006
Keywords:kbQFE kbHotfixServer kbSMS200preSP5fix kbMiscTools kbbug kbfix kbsms200preSP4fix KB306315
©2004 Microsoft Corporation. All rights reserved.