Solved

several problems happened on SQL server

Posted on 2004-08-12
19
1,678 Views
Last Modified: 2012-06-27
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
Comment
Question by:loveviolet
  • 8
  • 4
  • 2
  • +2
19 Comments
 
LVL 12

Expert Comment

by:kselvia
ID: 11789935
It looks like disk or controller problems to me. I would look in the System Event log for other errors.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11790307
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
 
LVL 34

Expert Comment

by:arbert
ID: 11790309
0
 
LVL 34

Expert Comment

by:arbert
ID: 11790320
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11792618
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
 
LVL 34

Expert Comment

by:arbert
ID: 11793355
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
 
LVL 1

Author Comment

by:loveviolet
ID: 11794275
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
 
LVL 34

Expert Comment

by:arbert
ID: 11794322
Did you verify the NAS hardware is on the complancy list????
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:loveviolet
ID: 11843798
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
 
LVL 34

Expert Comment

by:arbert
ID: 11844382
All kinds of specifics you didn't list in your original question....Are you running anything else on the box besides SQL Server?
0
 
LVL 1

Author Comment

by:loveviolet
ID: 11844420
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
 
LVL 34

Expert Comment

by:arbert
ID: 11844473
You said you upgraded--did you upgrade from regular 2003, or did you upgrade from windows 2000?
0
 
LVL 1

Author Comment

by:loveviolet
ID: 11844495
From regular 2003
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 11894484
Ya, I'm still partially blaming TOO much memory allocation and a possible issue with the NAS drivers/controller card BIOS
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12131828
Agree.  I think arbert should bag these points
0
 

Expert Comment

by:zfanz
ID: 12177151
It must be a physically program of memory-RAM,for it  have the same settings with another server .
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now