FIX: Msdb..Syshistory Rows May Be Incorrectly Deleted

• Microsoft SQL Server version 6.5

SYMPTOMS

The task history is maintained for all tasks in the MSDB database. When the maximum number of rows of any given task is reached, the historical rows of that task should be deleted in sequence, starting with the oldest row. However, the INSERT trigger on the syshistory table may remove historical information in an incorrect order.

CAUSE

The problem is in the INSERT trigger for the syshistory table that removes the older rows when the maximum number of records is exceeded. The DELETE statement used to remove the records can delete the wrong record when a non- clustered index (instead of the clustered index) is used by SQL Server to complete the removal.

SET ROWCOUNT is used with the DELETE statement to remove a specified number of sequential records. Depending on index sort order, different rows can be removed.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: sqlexecutive

Keywords          : kbusage SSrvAdmin kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 21, 1999