August 24, 2021 at 7:44 am
Hi Experts,
yesterday their was a problem at my server , the transaction was paused for around 1:15:00 minutes can i know why the server is paused for the particular time or any log information for a particular period.
Thanks
Patel Mohamad
August 24, 2021 at 9:26 am
I take it you have no monitoring software already deployed as that would be the first thing to go check.
If you don't I would suggest getting something in rather than writing your own as you keep the development time to other parties especially with how fast the SQL product grows. Keeping up to speed with all the changes, and changing home grown code is a full time job in it's own right.
Secondly its the usual places to look, look at your event logs, error logs and the system health extended event files for the times in question and see what was going on during that period, to see why things paused / hung.
August 24, 2021 at 4:24 pm
I just wanted to second Ant-Green's statement about buying a tool. SQL Monitoring tools are a must for any SQL instance you care about.
And his statement about checking the logs is what I was going to suggest. If you have no automated monitoring tool, the logs are your next best bet. If this is a recurring problem, you may be able to log on when it is paused and have a look. Might not hurt to check what jobs were running at that time too. The "paused" query MAY have been blocked.
It may also be useful to reach out to the end user to determine what "paused" means. Were they trying to load an SSRS report (for example) and it took forever to load? If so, how much data were they pulling? If they were pulling several TB worth of data and are working on a slow network connection (ie not LAN, but work from home internet speeds), it may be that it was working and they just needed to be patient.
But, as Ant-Green said, I'd recommend checking the SQL logs, the windows logs (both on the server and the client), the application logs (whatever tool was reported as being "paused"), and any other logs you can find that seem relevant.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply