ConfigMgr 2007: Status Message 2407 in SMS_SQL_MONITOR after upgrade from SMS 2003
Today's tip comes from a Ryan Anderson, a Senior Support Escalation Engineer in our Texas office. If you're getting 2407's after an upgrade to System Center Configuration Manager 2007 then Ryan may have your solution:
========
Issue: After upgrading from SMS 2003 SP2 or SP3 to the Select version of Configuration Manager 2007 SP1, you may see the following status message:
SMS_SQL_MONITOR_SiteServer - 2407 SMS SQL Monitor failed to install trigger "SQLTaskStatus_upd" in the SMS site database.
Possible cause: Either table "SQLTaskStatus" or column "TaskName,TaskType" of table "SQLTaskStatus" does not exist in the SMS site database.
Solution: If you modified table "SQLTaskStatus", restore it to its original, unmodified state.
Possible cause: SQL Server problem.
Solution:
1. Review the immediately preceding status messages from this component about SQL Server errors.
2. Verify that this computer can reach the SQL Server computer.
3. Verify that SQL Server services are running.
4. Verify that SMS can access the SMS site database.
5. Verify that the SMS site database, transaction log, and tempdb are not full.
6. Verify that there are at least 50 SQL Server user connections, plus 5 for each SMS Administrator console.
If this error occurs repeatedly, refer to your SMS documentation or the Microsoft Knowledge Base for further troubleshooting information.
Cause: This occurs when the ConfigMgr 2007 upgrade doesn't remove the SMS_SQL MONITOR server per the following:
When upgrading from SMS 2003 SP2 or SP3 using the Select version of Configuration Manager 2007 SP1, the SMS SQL Monitor service (SMS_SQL_MONITOR) is not completely removed. This service is not required for Configuration Manager 2007 sites, and because the service is not successfully removed during the upgrade process, the service will attempt to start and generate site status warning messages when the site server computer starts up.
Resolution: The following procedure can be used to remove the SMS_SQL_Monitor service:
1. In a new command prompt instance, enter the following command to delete the SMS_SQL_Monitor service:
sc delete SMS_SQL_MONITOR
Then press Enter.
After the SMS_SQL_Monitor service is successfully deleted, enter the following commands to delete service-related registry keys:
REG DELETE HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\COMPONENTS\SMS_SITE_COMPONENT_MANAGER\Component Servers\<site server computer name> \Components\SMS_SQL_MONITOR
REG DELETE HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Tracing\SMS_SQL_MONITOR
Close the command prompt instance, and restart the site server computer.
========
Thanks Ryan!
J.C. Hornbeck | Manageability Knowledge Engineer