sql database crashes every morning smae time - was stable
-
Hi, I'm new to the forum.
I have a dedicated server running asp against a shared sql server with an isp I have been with for several years.
Every morning for the last 4 days at precisely 5:40 AM, all of my database queries cease to run because of some bottleneck on the SQL server.
I have been with this host for several years. They have a profiler which shows how long the worst queries take to run, and how much cpu they use. They measure these in % of total server, and they send emails to me if the cpu usage is over 10%. I occassionally have over 10% cpu, usually when the bots are coming throught. I do have a lot of red showing I use up to 20% of the 'time' -- they are measuring in ms -- this %% can not be real, because after these moring crashes, my time %% go up into the %%thousands%% -- sometimes evan as much as 12,000.92% !!
I have an example query that normally runs 2543 ms -- but after the morning hang it takes 900510 ms -- that is it no one waits around for it to complete.
The only fix for this is for the isp to reset the database connections from their end. Then everything runs fine until the next mornign at 5:40 AM. The only thing I see consistant for this time frame in the morning are system indexing queries.
The host tech support is telling me it's my fault - I need to optimize my code. The explanation that it's my code, after being stable for so long, and breaking at the same exact minute every day just isn't making sense to me.
Does anyone have any ideas what could be causing this problem besides my code?
-
"host tech support is telling me it's my fault - I need to optimize my code"
Geez I just love constructive replies like this. How about you ask this lovely support person to explain what's wrong with the code. I've found out a few people like this, they just throw it back to you because they don't really know what's going on.
--------------------
Colt 45 - the original point and click interface
-
well, actually, they told me that sql trouble shooting cost $125 hour, and I authorized one hour. he examined my code, and gave me some very detailed things I can do to improve overall performance.
Meanwhile, I don't think any of that will fix my immediate problem, and hope that someone here can give me some constructive advice.
thx
pj
-
Ok, so make the suggested changes. If it fixes the problem, its $125 well spent, if not get a refund.
Without knowing any details about the database and server setup, suggested changes, etc... it's pretty hard to give anything that's more than a wild guess.
--------------------
Colt 45 - the original point and click interface -
If the databasse always hang in the same time everyday, there may be some SQL jobs or windows tasks running around that time and caused the issue.
-
p johnson, you are saying "the one fix - to reset db connectionfrom their end".... can it be something is going on on their side? and connection is breaking...sounds like your DB and SQL should be fine, something is going on with connection I think...check around 5:40 - what is going on on both sides.(I think). Good luck! One more - what about Profiler? set the Profiler run and check what is going on there.It could help...
-
Does anyone know what SQLDMO_1 sql user login id does? This morning, my site predictably went down at 5:40 AM. I caught it around 8, and saw this user - Its CPU usage was 401387. There were also numerous IIS connections, through my web sites, zero or nominal cpu.
thanks for all the responses. I agree $125 is well worth the advice I received about optimizing my database, but I continue to doubt that is the problem since I have been stable for so long, have not added a lot of new data, and have not a significant surge in traffic.
-
SQL DMO (Distributed Management Objects) is a set of Objects which allow you to programatically manage SQL Server. For example, I could write an equivalent Enterprise Manager using SQLDMO.
I'm not sure about SQLDMO_1 as a login, but I remember something I read in the past that if an application connects to SQL Server and uses DMO, if not application name is provided, then SQLDMO_X (x being the connection number - not spid) will be used as the application....
Aside from this, the tables it is referencing, have you run them through an execution plan to make sure it is using the correct indexes and also use DBCC SHOWCONTIG to ensure the index has not degraded. Also, is auto update statistics switched on?
-
You may well find a script is running somewhere doing a complex reindex using DMO on your database - this reindex would hold locks on your tables and effectively your connections would block until the locks were lifted.
Alternatively (and I've seen this happen), you may have a connection to the server from an app or system process that issues a begin transaction, does some simple updates, and then doesn't call commit or rollback! Effectively it leaves the transaction in place and leaves locks behind. Again, it would seem that a system process is starting around the 5:40am mark. Try turning off all jobs that relate to your database and start a SQL profiler at 5:20am, asking it to track locks, lock escalations, deadlocks, timeouts, etc.
-
Also, log on to the server at 5:40 and run sp_lock, sp_who and sp_who2 and post the results here...
-
When's the last time you ran sp_updatestats?
A.J.
DBA with an attitude -
Ian's dfirst paragraph about the DMO reindex is exactly what was taking place!! Turns out the host has a new db admin -- when someone finally told me that and I got in touch with her, she looked into it and that is very close to what she described the problem as. Luckily for me she works long hours and was able to correct it last night before it happened.
Thanks so much for all of your responses. This is a fantastic forum!
-
Glad to help - look forward to having u as a continuing member
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply