Symptoms

The smart object service tester not responding if I restart the SQL Service.
But if I tried to add new database service, there's no issue.
What should I check?
 

Diagnoses

Started a SQL Server Profile trace, and try to add the SQL Server Service Instance again. Discovered that the process got stuck in one particular stored procedure, thus stopping the SQL Server Service Instance registration from proceeding.
 

Resolution

Removing the stored procedure from the database and manages to successfully add the SQL Server Service Instance with no issue.