There was an issue in starting the User Profile Synchronization service from the central administration. Below is the warning and the content of the warning that were shown in the event viewer.
ILM SQL Configuration file could not be read: Error Parcing the SQL File: D:\Microsoft Office Servers\14.0\Sql\SchemaCollections.sql Exception: Specified collection 'StringSchemaCollection' cannot be dropped because it is used by object '
Observation and Steps to resolve the issue -
It was observed that the SYNC Database DBO owner was not the farm account but sa account. That was really very odd as I had used powershell to create the service application. For the issue to be fixed, the dbo on the SYNC database needs to be associated to The farm account. Below are the steps that were taken to fix the issue.
1> Ran the below PowerShell script
$syncdb = Get-SPDatabase -Id 97c3f4a3-2df2-47ef-9d36-52438563b0e8 # we need to get the ID from the respective SP farm
$upa = Get-SPServiceApplication -Id cc4ea1bb-a1ee-40eb-8af7-e2bac2359431 # we need to get the ID from the respective SP farm
2> Once the above script is run successfully, execute the below SQL script on the SYNC database
EXEC sp_changedbowner ‘domain\Farmaccount’
3> Once that is run, restart the timer service across all the SharePoint servers in the farm. This should fix the issue.
Hope this helps someone....