April 7, 2009 at 11:12 am
We have a database server (2005, Workgroup Ed.) that from time to time stops allowing connections. This usually occurs during business hours and the current theory is that there is an issue with one of our applications not closing connections properly. Unfortunately we can't find a "smoking gun" and we have been watching connection counts (on the server) diligently.
Basically for no "apparent" reason sqlservr.exe drops to 0 (in task manager) and no one can connect to sql server although you can log onto the server (windows 2003). The only course of action is to restart the MSSQL service.
Any advice on where to look would be greatly appreciated!!!!
April 7, 2009 at 11:19 am
Have you checked logs (error log, server log) to see if there's something that happens right before that?
Certain error severities can cause SQL Server to "lock up". Take a look at severities 21-25, in Books Online, you'll see the ones I'm talking about. That's definitely a possibility.
It should also be noted that workgroup edition is only intended for about 5-10 people at a time. I don't remember the precise number, but it's pretty small.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:36 am
GSquared,
Unfortunately nothing ever gets logged to the SQL Server log or the event viewer (or anywhere else that we can find!).
I've set up alerts for events 16-25...but none have been triggered. 🙁
It just appears that the database is humming along just fine and then...boom...locks up and no one can get in.
The user limit is something to look into...
Thanks!
April 7, 2009 at 11:51 am
Another possibility that occurs to me, from a bad prior experience, is that the account SQL Server is running under might end up locked out because of failed login attempts. Is that possible in your environment?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 4:01 pm
I am not so sure but my two cents do you experience excessive locking ang blocking on your server?
April 7, 2009 at 4:07 pm
May be statistics are outdated, did you check them?
April 7, 2009 at 4:11 pm
Matt S. (4/7/2009)
We have a database server (2005, Workgroup Ed.) that from time to time stops allowing connections. This usually occurs during business hours and the current theory is that there is an issue with one of our applications not closing connections properly. Unfortunately we can't find a "smoking gun" and we have been watching connection counts (on the server) diligently.Basically for no "apparent" reason sqlservr.exe drops to 0 (in task manager) and no one can connect to sql server although you can log onto the server (windows 2003). The only course of action is to restart the MSSQL service.
Any advice on where to look would be greatly appreciated!!!!
Don't know what you have that is connecting to this but if .NET and using default garbage collection this can be too slow.
Ignore any advice to the contrary and close all connections explicitely if this is the case.
Or if not, good luck with further research!
.
April 7, 2009 at 5:32 pm
Thanks everyone! I appreciate the quick advice!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply