Solved

several problems happened on SQL server

Posted on 2004-08-12
19
1,687 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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