October 6, 2008 at 8:57 am
Hello, all!
I'm in a corner and do not know to do else, I hope for some help or hints regarding my situation.
We run SQL 2000 (2000.080.2040.00 Hotfix 2140) on Win Server 2003 64 bit.
The problem: SQL server stops accepting any connections every other day. It lasts until I restart service.
The errors I saw are
Error: 17803, Severity: 20, State: 12 Insufficient memory available.
17120 :SQL Server could not spawn process_loginread thread.
Last two times I did not get any errors in System/Application/SQL error logs. I believe it happens because I restart service too quick and server does not have a chance to post anything into them.
Right before "freeze" I see a lot of connects in status - RESOURCE_SEMAPHORE.
The box has 16G of memory, SQL is allowed to use upto 14 gig.
The performance counters are in norm. No disk activities, no paging and so on.... SQL Total Server memory = SQL Target Memory = 1468812.
The most scary part is that it happens almost on schedule : every other day time between 7 and 8AM. I checked 100 times all the jobs and other possible scheduled activities - there is nothing exceptional at that time. The only difference it's an amount of user's connections - it grows very quickly at the time. However the same growth happens every day, but problem occurs only once in two consecutive days.
Running Trace did not reveal any unusual activity either. The only problem I can see in it is "Execute Warning" just about the time of the problem. I think the is not a couse of the issue but rather consequence.
I'd appreciate any thoughts, any ideas any help at this point
Thanks a lot!!!!!
October 6, 2008 at 9:21 am
Do you have /3GB switch in boot.ini?
Did you see this http://support.microsoft.com/kb/316749?
You can use -g startup flag to increase the size of the memory: http://dbakb.com/kb/Default.aspx?Page=memoryleak&AspxAutoDetectCookieSupport=1
October 6, 2008 at 9:37 am
Thanks for quick response, Glen!!!
Glen (10/6/2008)
Do you have /3GB switch in boot.ini?
/3GB is not in boot.ini - MS help desk says this is not required on 64bit platforms.
Glen (10/6/2008)
You can use -g startup flag to increase the size of the memory: http://dbakb.com/kb/Default.aspx?Page=memoryleak&AspxAutoDetectCookieSupport=1
-g512 switch is set up . We do not have any linked servers. Applications use 4096 packet size.
October 6, 2008 at 10:09 am
Do you have AWE enabled?
Can you verify the size and location of a paging file?
What is the version of Windows server? (standard, Ent.)
October 6, 2008 at 11:06 am
Glen (10/6/2008)
Do you have AWE enabled?Can you verify the size and location of a paging file?
What is the version of Windows server? (standard, Ent.)
Yes, AWE is enabled
Paging file is on c: drive - this drive is not used by SQL server for MDF and LDF files.
Size of it is 24567 MB
OS : Windows Server 2003 Ent. x64; SP 2
October 6, 2008 at 12:36 pm
Considering that you are using SQL Server 2000 EE,
I would suggest:
1. Lower max server memory to 12GB.
2. Split page file in two placing the second page file on a separate drive.
3. (only after monitoring for a while), use -g1000 as a startup parameter.
October 6, 2008 at 1:02 pm
Thx for suggestions.
However, I'm a bit concerned about lowering a memory. OS itself is very responsive during a problem. All counters within normal range. Page/Sec is 0.
Why do you think this might help?
October 6, 2008 at 1:10 pm
Do you have any 3rd party backup/audit/etc. tools running on that box (Idera, RedGate, Apex, TSM, etc)?
The Redneck DBA
October 6, 2008 at 1:35 pm
Jason Shadonix (10/6/2008)
Do you have any 3rd party backup/audit/etc. tools running on that box (Idera, RedGate, Apex, TSM, etc)?
The only thing we have running is Symantec Backup Exec. But according to our Network guys it does not run during problem time.
October 6, 2008 at 1:43 pm
You might want to try disabling it for a couple days and just shipping SQL backups to a different server just to rule it out. We've had some funny stuff happen because of it. It may be that it's not exiting cleanly or something and the problem doesn't show up until the server is under a load.
Also make sure there isn't any virus scanning software trying to do an active scan on your disk.
The Redneck DBA
October 6, 2008 at 2:03 pm
I was running in two separate cases.
Both cases were linked to a MemToLeave area.
If I remember correctly under Windows 2003 x64, min memory settings do not work on SQL 2000. As such all memory is allocated at a startup of SQL 2000.
-g512 will not help wince MemToLeave is not reserved. It is actually allocated from the OS at a run time. Take a look in more details here:
http://dubeyms.spaces.live.com/blog/cns!311B93A617AC3154!145.entry?wa=wsignin1.0
In this case lowering a max memory on 16 GB RAM computer to 12 GB setting for SQL server allows to resolved a problem.
In second case scenario problem was linked to a SQLSafe - extended sproc for backup (similar problem exists for Quest product etc.)
In this case, we were using a hotfix 2244 available for SQL server 2000.
October 6, 2008 at 2:25 pm
I had that same problem with SQLSafe a few years back too.
The Redneck DBA
October 6, 2008 at 3:06 pm
Thank you guys for hints - I'll continue digging.
One question:Clients of our sql server perform a lot of very small queries. Every time they connect and disconnect, basically Login/Logout number of events is huge. Just wondering if lowering min memory per query from 3072 to 1024 can help anyhow?
October 6, 2008 at 4:57 pm
Default is 1024. I don't really know why did you change it to begin with...
October 7, 2008 at 7:52 am
Glen (10/6/2008)
Default is 1024. I don't really know why did you change it to begin with...
That is a good question, I do not recall changing it manually and did not pay attention to this parameter until now.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply