regsamp
asked on
Timeout Error Message on Intranet Site
ASKER
The connection timeout is currently 30. The ram is currently 8 GB as it is a new HP DL 360 and the SQL database is 2008 R2. After rebooting the error was cleared. We are a small company and at the most there would be 40 people connected to this site at one time.
Hi,
What size is the database?
Did you check the connections prior to rebooting?
Regards
David
What size is the database?
Did you check the connections prior to rebooting?
Regards
David
ASKER
The database is 50 MB and unfortunately I did not. I had some Executives who insisted right away.
After rebooting the error was clearedNext time this happens don't reboot. Connect to SQL Server and check for locks or any long running query or process. Should be something in the SQL Server side that don't allows the engine to be in a responsive state.
ASKER
Okay, I have Activity Monitor ready this time and just check under Processes and see if it happens again? Where would Queries be checked right away?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, will do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have not done a lot of SQL monitoring so how do I get a solid count of the syslocks? I want to see if I can have some good solid measures that are worth it while trying to hold off the executives.
Hi,
If the count is a smallish number (say a thousand or less), then maybe worth getting the actual results. Which will need to be referenced against actual connections ie sp_who and sp_who2.
As sp_who are sp_who2 are procedures, you can get the text of them and see which tables and DMV's they reference. The number of locks each connection holds may help identify the connection(s) causing your issues.
HTH
David
use tempdb
go
select count( * )
from master.sys.syslockinfo
;
If the count is a smallish number (say a thousand or less), then maybe worth getting the actual results. Which will need to be referenced against actual connections ie sp_who and sp_who2.
As sp_who are sp_who2 are procedures, you can get the text of them and see which tables and DMV's they reference. The number of locks each connection holds may help identify the connection(s) causing your issues.
HTH
David
ASKER
Okay, I will look into that procedure. Thank you
Do not use syslockinfo. It's an old table from SQL Server 2000 and now it's a view that exists only for backward compatibility. The information that returns is not accurate. Even counting locks is not a good solution for monitoring (because there are different kind of locks) if you still want to perform a lock count you can use the following query:
SELECT COUNT(blocking_session_id)
FROM sys.dm_os_waiting_tasks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, let me look more into that query if that will get the results quicker.
Did you find the issue?
ASKER
Not yet as the issue has not reoccured. I have been trying to watch for any issues but nothing yet.
This is the connection timeout.
You could specify a larger value in the website code - if I'm right the default is 30 seconds.
On the other hand, 30 seconds to connect is rather long, and speaks to the load on your server. Look at SQL and see if there are orphaned connections that are taking up CPU and memory. Look at increasing the ram and/or max memory setting on your server.
HTH
David
PS Need to know more about your SQL Server to assist further