• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1701
  • Last Modified:

several problems happened on SQL server

This only happens on one machine; another machine which is having exact the same settings do not get these problems.

We are win 2k3, enterprise edition sql 2000, sp3a with additional security patch, NAS device for disk, awe enabled, 8 proc, 8GB (6GB max mem for sql server), also we are using sqllitespeed.

Please see the following 4 segments --- 4 different types of errors and I hope they are related.  Unfortunately I do not have any access right now to change any of the settings to test(probably there will be a chance in the near future), but I definitely need some opinions about what might be the cause.

Thanks in advance!
Jennifer


First Segment:  warnings that happen about 2 times a day
2004-08-11 20:22:24.44 spid112   WARNING:  Failed to reserve contiguous memory of Size= 4128768.
2004-08-11 20:22:24.51 spid112   Buffer Distribution:  Stolen=4294946437 Free=14281 Procedures=164705
               Inram=0 Dirty=2673 Kept=0
               I/O=0, Latched=130, Other=625502
2004-08-11 20:22:24.51 spid112   Buffer Counts:  Commited=786432 Target=786432 Hashed=628305
               InternalReservation=575 ExternalReservation=82 Min Free=952
2004-08-11 20:22:24.51 spid112   Procedure Cache:  TotalProcs=20589 TotalPages=164705 InUsePages=66489
2004-08-11 20:22:24.51 spid112   Dynamic Memory Manager:  Stolen=143846 OS Reserved=32120
               OS Committed=28530
               OS In Use=27726
               Query Plan=164566 Optimizer=0
               General=6331
               Utilities=10 Connection=404
2004-08-11 20:22:24.51 spid112   Global Memory Objects:  Resource=2367 Locks=63
               SQLCache=3693 Replication=2
               LockBytes=2 ServerGlobal=69
               Xact=41
2004-08-11 20:22:24.51 spid112   Query Memory Manager:  Grants=1 Waiting=0 Maximum=215487 Available=215405



Second segment:  Sudden shut down on machine
2004-08-10 18:15:54.50 spid2     LogWriter: Operating system error 1450(Insufficient system resources exist to complete the requested service.) encountered.
2004-08-10 18:15:54.54 spid2     Write error during log flush. Shutting down server
2004-08-10 18:15:54.85 spid19    Error: 9001, Severity: 21, State: 4
2004-08-10 18:15:54.85 spid19    The log for database <databasenameremoved> is not available..


Third segment (stack dump during day):
2004-08-11 20:42:38.83 server    Error: 17883, Severity: 1, State: 0
2004-08-11 20:42:38.83 server    Process 112:0 (12a8) UMS Context 0x1248CE70 appears to be non-yielding on Scheduler 6.
2004-08-11 20:42:40.17 server    Stack Signature for the dump is 0x00000000
2004-08-11 20:56:09.70 spid91    Error: 15457, Severity: 0, State: 1
2004-08-11 20:56:09.70 spid91    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2004-08-11 20:56:09.88 spid91    Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_msver'.


Fourth segment (stack dump during night):
---------------------------------------------------------------------------------------------------
2004-08-08 01:19:56.98 spid99    Stack Signature for the dump is 0xDD4A7E30
2004-08-08 01:19:57.00 spid99    SQL Server Assertion: File: <buffer.c>, line=3723
Failed Assertion = '!(bp->bdbid == dbid && ALL_ON (BUF_HASHED | BUF_CHECKWRITE | BUF_DIRTY, bufstat) && IS_OFF (BUF_IO, bufstat) && bp->bpage->GetXdesId () == xdesId)'.
2004-08-08 01:19:57.00 spid99    Error: 3624, Severity: 20, State: 1.
2004-08-08 01:25:06.13 spid6     Time out occurred while waiting for buffer latch type 4,bp 0x3de1800, page 1:5730344), stat 0x10000b, object ID 9:213575799:2, EC 0x806F03C8 : 0, waittime 300. Not continuing to wait.
2004-08-08 01:25:06.13 spid6     Waiting for type 0x4, current count 0x100022, current owning EC 0x94997550.
2004-08-08 01:25:06.13 spid6     Error: 845, Severity: 17, State: 1
2004-08-08 01:25:06.13 spid6     Time-out occurred while waiting for buffer latch type 4 for page (1:5730344), database ID <number removed>..
2004-08-08 02:05:01.73 spid106   Time out occurred while waiting for buffer latch type 3,bp 0x3de1800, page 1:5730344), stat 0x10000b, object ID 9:213575799:2, EC 0x30C5B550 : 0, waittime 300. Not continuing to wait.
2004-08-08 02:05:01.73 spid106   Waiting for type 0x3, current count 0x10002a, current owning EC 0x94997550.

0
loveviolet
Asked:
loveviolet
  • 8
  • 4
  • 2
  • +2
1 Solution
 
Ken SelviaRetiredCommented:
It looks like disk or controller problems to me. I would look in the System Event log for other errors.
0
 
arbertCommented:
Why did you accept your last question when you said you can't test anything????  I still think your AWE is configure wrong and you're allocating too much memory to SQL Server--this will cause all of the problems you're seeing.  Can you reduce the amount of memory and try it?
0
 
arbertCommented:
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
arbertCommented:
Have you also verified on the Hardware Compatability list that your SQL server is certified to run on the NAS device you have--sql server isn't certified to run on too many of them:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
0
 
ShogunWadeCommented:
i havent used AWE myself but im incluned to go with arbert on this one

2004-08-11 20:22:24.44 spid112   WARNING:  Failed to reserve contiguous memory of Size= 4128768.
2004-08-11 20:22:24.51 spid112   Buffer Distribution:  Stolen=4294946437 Free=14281 Procedures=164705

strongly suggests its a memory config or physically dodgy memory problem
0
 
arbertCommented:
I think that's definately a big part of it (I've been down this road before :( )  I also agree there could be something wrong with the NAS (NAS wasn't mentioned in the first question).  NAS isn't something, personally, that I would try and run a mission critical SQL Server on...
0
 
lovevioletAuthor Commented:
Arbert, I will definitely suggest my IT to reduce the memory to SQL server, and see what the result will be.  Also I am going to convince them to reinstall the sql server service packs.  Do you think this will help?  All the errors (except the memory warning) happened after applying service pack 3a, which was 2 weeks ago.  The memory warning were there 3 month ago, but it is just getting more and more frequent during the past 2 weeks.

Aside from reducing memory and reinstalling service packs, is there any other things I should try?
0
 
arbertCommented:
Did you verify the NAS hardware is on the complancy list????
0
 
lovevioletAuthor Commented:
NAS is not on the complancy list, although Microsoft said they will support it.  We have been using NAS for 3 years now and didn't have as much problems. Maybe it had to do with the Windows 2003 Enterprise Edition we just upgraded?
0
 
arbertCommented:
All kinds of specifics you didn't list in your original question....Are you running anything else on the box besides SQL Server?
0
 
lovevioletAuthor Commented:
No, SQL server is the only thing we have on that box, aside from SQLlitespeed.  I just got the knowledge yesterday that we have Windows 2003 Enterprise Edition instead of normal edition on that box.
0
 
arbertCommented:
You said you upgraded--did you upgrade from regular 2003, or did you upgrade from windows 2000?
0
 
lovevioletAuthor Commented:
From regular 2003
0
 
arbertCommented:
Ya, I'm still partially blaming TOO much memory allocation and a possible issue with the NAS drivers/controller card BIOS
0
 
ShogunWadeCommented:
Agree.  I think arbert should bag these points
0
 
zfanzCommented:
It must be a physically program of memory-RAM,for it  have the same settings with another server .
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now