Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 Database Stuck Expand / Collapse
Author
Message
Posted Wednesday, August 8, 2012 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 9, 2012 12:46 AM
Points: 3, Visits: 21
I am using SQL Server 2008 in on of our project, I am facing an issue, sometime daily or after 1-2days at morning(08:00 to 09:00) and at evening(18:00 to 19:00) database stuck. The query which we runs directly from database it works fine but the same query we execute from ASP.NET application then it stuck, mean when we click on that page it shows in progress. I also tried to debug from live database server but on Database stuck time when we called any stored procedure EXECUTE command then it not responds. I also observe that it stuck for 30-35mints, so after that it works fine automatically.

Current Solution : We are managing by Restarting SQL Server, so as we re-start SQL server it works fine.
Post #1341713
Posted Wednesday, August 8, 2012 3:40 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 27, 2014 7:41 AM
Points: 739, Visits: 518
Using the term "stuck" is is a bit vague.

From what I can gather, running a query from an ASP.NET page causes issues on the SQL Server?

If that is the case, I'd check the Windows Application Logs for the Web Server hosting the application to start with.

Have you set connection and query timeouts in Web.config?

Hope this gives you something to start with.


"Be brave. Take risks. Nothing can substitute experience."
Post #1341756
Posted Wednesday, August 8, 2012 4:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 4,158, Visits: 5,555
Have you checked for locking / blocking / deadlock issues?

Most often, it is a case of an uncommitted transaction blocking other processes that causes such issues.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1341768
Posted Wednesday, August 8, 2012 4:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 4,655, Visits: 11,125
Shot in the dark...
Could be a stale statistics issue. If the query runs fine for a couple of days and then start misbehaving, you statistics might be outdated due to updates to the underlying tables.
I would compare the execution plan of the ad-hoc query in SSMS and the execution plan of the statement executed by the app while it's running. You can capture it from sys.dm_exec_query_plan(plan_handle) passing the plan_handle found in sys.dm_exec_requests.
If the plans are different (they likely are), check if the two statements are executed in the exact same way (literal values/parameters, direct execution/sp_executesql). I would check for outdated statistics and parameter sniffing.

Hope this helps
Gianluca


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1341779
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse