June 9, 2011 at 11:00 am
We have an application that tracks support tickets. About 1x per month I get reports of the application hanging warrenting a restart of the server. Once the server comes up I have to manually restart the mssqlservice even though it's set to automatic. That usually fixes the issue. Now, I wanted to dive in deeper here and do some root cause...
In the SQL Server error log I noticed that the SQL server is going down and restarting all databases more frequently that I initally thought. 6/1 @ 8pm, 6/3 @ 8pm, 6/6 @ 8pm, 6/8 @ 8pm, 6/9 @ 8pm. I immediatley started looking at jobs that were running at 8pm and only found a history cleanup task which I just disabled. In the error log I also noticed (on the same dates and minutes before a SQL error) 17054 serverity 16 level 1 on spids 12s and 16s. Not sure what spids mean. Anyway, I then jumped over to the server event log and nothing really pop'd out at me except a failed 'getsql2005DBSpace.js' script.
So, then I turned to the default trace in profiler and I am noticing is..
"Missing Column Statistics" for each domain user with the application name "ticket tracking"
There is also...
"Missing Join Predicate" for user sa
June 9, 2011 at 11:07 am
Wow this is wrong on so many levels.
Do you have auto_close or auto_shrink on?
Please stop using sa for the connection if possible.
Make sure you have auto_create / upate stats to on.
June 9, 2011 at 12:11 pm
Ninja's_RGR'us (6/9/2011)
Wow this is wrong on so many levels.Do you have auto_close or auto_shrink on?
Please stop using sa for the connection if possible.
Make sure you have auto_create / upate stats to on.
1.Both are OFF
2.I didn't set it up but will check into changing it.
3.Yes both are on.
Know anything about the errors?
June 9, 2011 at 1:01 pm
What is worrying me is "7054 severity 16 level 1 on spids 12s and 16s". SPID 12 and 16 are for SQL System processes.. What is the error description?
What is the patch level? Please post output of
Select @@version
xp_msver
June 9, 2011 at 2:19 pm
I've never had that in the error log (in traces yes, but not the log).
If I had only 1 guess I'd bet that you have a once a month process or report that consumes a crapload of ressources.
I never "had" to restart a server... there's always a reason and you'd better find it.
The missing stats might also "prove" this theory. A query that runs only once in a while might not have everything up to date which can lead to a very bad plan which then leads to contention.
I'd start by checking those articles out and blast an e-mail to everything to see what process or reports are being run on the days that you have to reboot the server.
June 10, 2011 at 3:29 am
Look at your SQL Agent jobs. Make sure SA doesn't own any of them. If it does, that might be why things are running under the SA context.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply