The components which were failing while publishing/previewing due to the database error was due to the Razor template which was taking more 90 to 200sec to execute that was causing the issue.
To resolve the above issue:
On the CMS and Publisher server went to the Programs > SDL Web > SDL Web Content Manager configuration in the Microsoft Windows Start menu.
Navigate to the Timeout settings section
In that we increased the timeout value from 50sec to 900sec for the Seconds before a timeout error is generated when executing a query.
After increasing the timeout restarted COM+ and required services on both the server to ensure updated settings are used.
With the above changes publishing was going smooth but it was taking more time at rendering phase and apart from these few more observations are:
- In the CMS DB when we checked the CPU usage was high sometimes it was reaching up to 99%.
- In the Publisher server when checked the Event Viewer in the application and server logs -> navigate to tridion when we are publishing template issues related components getting "n" no of warnings.
After trying with multiple options we came to know that main culprit is "Razor Template".
In the razor template we are calling function called "GetTemplateId("CPTest_CT") which was causing the issue.
To resolve this issue as per time being wherever they are calling GetTemplateId passing the hardcoded id of component template. After doing this changes all the above issues got resolved.
I'm explaining in detail if next time anyone encounter this issue it may help.
