SharePoint 2010 - Unable to provision the User Profile Synchronization Service

Article ID: 2579951 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

Consider the following scenario:

After creating a User Profile Service Application, you attempt to start the User Profile Synchronization Service from the Central Administration site. However, the service remains in a 'Starting' state for a few minutes and then returns to a 'Stopped' state. Additionally, you notice the below event reported in the Application Event log

Log Name : Application
Source : FIMSynchronizationService
Date : dd/mm/yyyy hh:mm:ss
Event ID : 6324
Task Category : Server
Level : Error
Keywords : Classic
User : N/A
Computer : SERVER
Description:
The server encountered an unexpected error and stopped.

"ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 0.

CAUSE

The 'Recursive Triggers Enabled' property for the Model database is set to 'True' in the SQL instance.

RESOLUTION

Change the property value of 'Recursive Triggers Enabled' to 'False'.

From the SQL Server Management Studio, expand 'System Databases' > Right-click 'Model' | Properties | Options | under Miscellaneous section, set 'Recursive Triggers Enabled' property to 'False'.

MORE INFORMATION

The SQL scripts used by SharePoint to provision the Sync database fails to check the value set for the 'Recursive Triggers Enabled' property. This causes the Sync database to be created with only a partial set of tables.


Here's the detailed error message in the application event log:

ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 1.
ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 2.
ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 3.
BAIL: MMS(6016): service.cpp(991): 0x80040e14
Forefront Identity Manager 4.0.2450.11"

Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="FIMSynchronizationService" />
<EventID Qualifiers="49152">6324</EventID>
<Level>2</Level>
<Task>3</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="yyyy-mm-ddThh:mm:ss.000000000Z" />
<EventRecordID>7011</EventRecordID>
<Channel>Application</Channel>
<Computer>SERVER</Computer>
<Security />
</System>
<EventData>
<Data>ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 0.
ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 1.
ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 2.
ERR: MMS(6016): sql.cpp(5580): Query (update [mms_run_history] set [is_run_complete] = 1,[run_result] = N'stopped-server',[end_date] = <Date & Time> where ([is_run_complete] = 0)) performed with error
ERR: MMS(6016): sql.cpp(5633): Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
ERR: MMS(6016): sql.cpp(5641): hrError: 0x80040e14, dwMinor: 217
ERR: MMS(6016): sql.cpp(5796): SQL error: 42000, native: 217
BAIL: MMS(6016): sql.cpp(3608): 0x80040e14
BAIL: MMS(6016): sql.cpp(3505): 0x80040e14
BAIL: MMS(6016): sql.cpp(2029): 0x80040e14
BAIL: MMS(6016): exechist.cpp(1569): 0x80040e14
BAIL: MMS(6016): mastate.cpp(1306): 0x80040e14
BAIL: MMS(6016): mastate.cpp(918): 0x80040e14
BAIL: MMS(6016): server.cpp(427): 0x80040e14
BAIL: MMS(6016): server.cpp(3518): 0x80040e14
BAIL: MMS(6016): service.cpp(1528): 0x80040e14
ERR: MMS(6016): service.cpp(977): Error creating com objects. Error code: -2147217900. This is retry number 3.
BAIL: MMS(6016): service.cpp(991): 0x80040e14
Forefront Identity Manager 4.0.2450.11</Data>
</EventData>
</Event>

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2579951 - Last Review: July 18, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft SharePoint Server 2010
  • Microsoft SharePoint Server 2010 Service Pack 1
  • Microsoft SharePoint 2010 For Internet Sites Enterprise
Keywords: 
KB2579951

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com