We are seeing that our server will suddenly stop accepting new connection from SQL Server. The server itself is still accessible via RDP, but sharepoint portal/SSMS and any other remote SQL connection will timeout. Configuration manager shows that SQL Services are running, but if you attempt to stop/ restart the services manually, it will hang. Restarting the server will sometimes let the services go back to a working state, but this usually means it will hang within the day if not hours. We have determined that a full SQL Server repair using the installation media will bring it back to a more stable state, but corruption of the SQL Server application will probably happen again. We would like to know how it happened. SQL Errorlogs do not point any specific errors.We've attempted to use Profiler, but haven't run it continuously since it brings additional load onto server. Has anyone ever get this issue? How did you solve it?
I have done some research yesterday so I found following issues:
(1) Database files ldf is increasing more than it's autogrowth limit. The mdf file size is 48 mb and ldf file size is 48gb. So any suggestion to mitigate this issue. As per now, I am just able to think the backup plans.
(2) I am seeing too many fragmented indexes which also another reason of slower performance.
However, I would like to know the MaxDOP. We are using sharepoint database here so any suggestions related to maintain sharepoint farm would be appreciated.
I am also following following thread where they are talking about MAXDOP.
https://community.spiceworks.com/topic/2202073-sql-server-process-high-cpu-usage
