none
Error: 17803, Severity: 20, State: 14, Insufficient memory available.

    Question

  • I have this error in my SQL logs today and I can't find the problem.  Right after this error appeared, my database ended up being corrupted.  I looked online and saw several articles referring to possible disk hardware failure.  Any advice would be greatly appreciated.  We are running SQL 2000.

    Nick

    Thursday, May 14, 2009 6:58 PM

All replies

  • Nick,

    >>database ended up being corrupted

    Did you run the checkdb to find out what errors it threw? If you see corruption from the below command then I will ask this post to be moved to Disaster Recovery where you will get specific responses to deal with this problem. Let us know the errors from corruption.

    DBCC

     

    CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, May 14, 2009 8:48 PM
    Moderator
  • Hi Nick

    please check the bellow article

    http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL+Server&ProdVer=2000.80.760.0&EvtID=17803&EvtSrc=MSSQLServer

    If you run DBCC CHECKDB on that database what did you get?

    Did you find any errors in the Event log related to Hardware issues?

    Provide us more info.
    Karumajji
    Thursday, May 14, 2009 8:50 PM
  • I'd recommend that you read Paul Randal's recent blog post on running CHECKDB and getting all the errors:

    How to get all the corruption messages from CHECKDB

    SSMS will only return the first 1000 problems, and if you have more errors than this, you need to follow Pauls recommendations in the above blog posting.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, May 14, 2009 10:16 PM
    Moderator
  • is your database still accessible? If not, try to set database into emegency mode by using this script

    --to update system table
    use master
    go
    sp_configure 'allow updates', 1
    reconfigure with override
    go

    --Put database into emergency mode
    update sysdatabases set status= 32768 where name = 'your database name'
    --To Repair Database using DBcc with option Repair_Rebuild, this action as a last resort to correct your Database
    use 'your database name'
    go
    sp_dboption 'your database name', 'single_user', 'true'
    go
    dbcc checkdb ('your database name', REPAIR_REBUILD)
    go
    update sysdatabases set status= 0 where name = 'your database name'
    go
    sp_dboption 'your database name', multi_user', 'true'
    go
    --Execute DBcc again to check your database integrity after repair action
    DBCC CHECKDB ('your database name')
    sp_configure 'allow updates', 0
    reconfigure with override
    go

    Thursday, May 14, 2009 11:48 PM
  • Andrian - this is bad advice, for several reasons. Firstly, *never* run repair without figuring out what is wrong first and deciding to use repair instead of backups. Secondly, even though you've given a script for REPAIR_REBUILD, any repair option requires the database to be in single_user mode (i.e. offline). Thirdly, REPAIR_REBUILD can only fix nonclustered indexes, which can be done manually without taking the database essentially offline.

    Don't propose a solution until we know what's actually wrong.

    Nick - running out of memory should never cause corruption. However, I suspect that you may have an issue with the memory on your server. Corruptions caused by memory errors are rare but they definitely do happen. Can you rnu memory diagnostics on the server?

    Please post the output of DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS and I'll help interpret the output for you.

    Thanks


    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 12:26 AM
    Moderator
  • Anyway Thank Paul for your advice, but I only suggest this action as a last resort.

    --Put database into emergency mode
    update sysdatabases set status= 32768 where name = 'your database name'
    --To Repair Database using DBcc with option Repair_Rebuild, this action as a last resort to correct your Database
    use 'your database name'

    Friday, May 15, 2009 1:32 AM
  • Maybe - but you marked it as the Proposed Answer.

    And, in fact, you can't run repair if you *had* to put the database into emergency mode in 2000. If you *had* to use emergency mode, it's because the transaction log was damaged, which means you can't start any transactions and so repair can't run (everything it does is in a transaction). You'd need to rebuild the log first, another no-no unless absolutely necessary, and then run REPAIR_ALLOW_DATA_LOSS, not REPAIR_REBUILD.

    I appreciate that you were trying to help, but the advice was incorrect on several levels. When answering a corruption question, you have to be exactly correct otherwise the question poster could get into even worse trouble.

    Thanks


    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 1:46 AM
    Moderator
  • Thanks again Paul, yeah I'm just trying to help him, because my database ever corrupted and the DBCC CheckDB won't run because the database is inaccessible. but when i switch to emergency mode and run Dbcc CheckDB the command run and that i can repair my database. Thanks Paul for your reply.
    Friday, May 15, 2009 3:48 AM
  • Thanks all for the posts.  I did run CHECKDB and found an error on several of my tables.  Yesterday, I put the database in single user mode and ran the following procedures:
    1. dbcc checkdb ('dasl', repair_fast)
    2. dbcc checkdb ('dasl', repair_rebuild)
    3. dbcc checkdb ('dasl', repair_allow_data_loss)

    After all this I still had an error on two of the tables:

    Error: 605, Severity: 21, State: 1

    Attempt to fetch logical page (1:2881684) in database 'DASL' belongs to object 'tblGPASetStudent', not to object 'tblAbsenceDaily'.


    This fixed all of the tables except two.  I rebuilt those two last night and the tables have no errors in them.  I am now in the process of reviewing the logs to see what happened.  The only error I see in the logs in the database at the time that it went down is the insufficient memory error.  I don't know how that could happen we have 32GB of RAM. 

    One other interesting thing is that when the database went down, the cluster resources split.  One node had the SQL group and one node had the cluster group.  We are running an active passive cluster.

    Nick
    Friday, May 15, 2009 1:51 PM
  • would you give me more information about CPU architecture(32 bit,X64,IA64)? and operating system?
    Friday, May 15, 2009 1:58 PM
  • We are running 32 bit.  We are running Windows Server 2003 R2, 32GB of RAM, with 4 dual core processors.  We are running a Active Passive cluster.
    Friday, May 15, 2009 2:23 PM
  • Can you run a DBCC CHECKTABLE on the two tables mentioned in the 605 error? Were they invovled in the original set of errors? You could have a stale read problem on a RAID controller, or bad memory. Did you run memory diagnostics?
    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 2:42 PM
    Moderator
  • I can't run DBCC checktable because I rebuilt the tables and they are error free.  Yes, they were involved in the original errors.  No, I haven't.  Is memory diagnostics a add on from microsoft?
    Friday, May 15, 2009 2:47 PM
  • I don't understand what you mean by you can't run it - nothing should stop you running DBCC CHECKTABLE again to see whether the tables are *still* error free.

    Memory diagnostics are available in the BIOS of servers - your h/w vendor will have instructions on how to do it for your machines.
    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 3:22 PM
    Moderator
  • In case the BIOS does not have such a thing - the Microsoft Online Crash Analysis Windows Memory Diagnostic Tool is here:

    http://oca.microsoft.com/en/windiag.asp

    Paul
    Friday, May 15, 2009 3:26 PM
  • My fault.  I can run it but there are no errors in the table.

    DBCC results for 'tblAbsenceDaily'.
    There are 3748863 rows in 78144 pages for object 'tblAbsenceDaily'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Friday, May 15, 2009 3:59 PM
  • I wouldn't say 'your fault', Nick.  There was clearly a small difference in the intent and the reading of your prior post.  This sort of thing is inevitable in a forum from time to time (it would not happen so much in person) so no-one need apologise or take blame.  Just my take on it...
    Friday, May 15, 2009 4:04 PM
  • I am not going to be able to run memory diagnostic until later.  It mentions that you have to reboot the server you are analyzing and this issue is on our primary server.

    Friday, May 15, 2009 4:59 PM
  • Fair enough - but bear in mind that the longer you wait to do this, the more likely that corruption will occur again and may not be so easily removed, if at all.

    Btw - when you're running repair, you don't need to run all three levels - only the level that CHECKDB said. REPAIR_ALLOW_DATA_LOSS is a superset of the other two.

    Thanks


    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 5:05 PM
    Moderator
  • Is there anything I can run without rebooting the server?
    Friday, May 15, 2009 5:13 PM
  • I was going through the some error logs and he is the error I was receiving yesterday.  I am not seeing these errors today.

    2009-05-14 08:28:59.01 spid67    Getpage: bstat=0x9, sstat=0x10010000, cache
    2009-05-14 08:28:59.01 spid67      pageno is/should be: objid is/should be:
    2009-05-14 08:28:59.01 spid67      (1:2881684)/(1:2881684)  389628481/293576084
    2009-05-14 08:28:59.01 spid67    ... IAM indicates that page is allocated to this object
    2009-05-14 08:28:59.01 spid67    Error: 605, Severity: 21, State: 1
    2009-05-14 08:28:59.01 spid67    Attempt to fetch logical page (1:2881684) in database 'DASL' belongs to object 'tblGPASetStudent', not to object 'tblAbsenceDaily'..
    Friday, May 15, 2009 5:21 PM
  • Yup - this is genuine corruption. Sometimes you can see 605s if you're running queries with NOLOCK or in the read-uncommitted isolation level, but getting the getpage error means it wasn't that.
    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 5:37 PM
    Moderator
  • Just out of interest, what is the database's setting for PAGE VERIFY?

    Is it NONE, TORN_PAGE_DETECTION, OR CHECKSUM?

    Checksum can help detect some corruptions early, though the setting only applies to pages written since the setting was changed.  So if the database was created under SQL2K, unmodified pages will be still using NONE or TORN_PAGE, depending on what was set in the past.

    Paul
    Friday, May 15, 2009 5:44 PM
  • Paul - they're running on 2000.
    Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
    Friday, May 15, 2009 5:55 PM
    Moderator
  • Oh. Heh.  Excuse me then...I'll just be moving along ;)

    Time to upgrade?

    Paul
    Friday, May 15, 2009 5:58 PM
  • Oh yeah.  We are moving to 2008 in July.  Is there any other checks I can run without taking the server down? 

    Nick

    Friday, May 15, 2009 6:14 PM
  • Here are some other errors I found right before the trouble started.


    2009-05-13 14:14:42.38 spid129   AWE mapping status:
    2009-05-13 14:14:42.38 spid129   There are 173408 pages in the AWE window
    2009-05-13 14:14:42.38 spid129   171290 pages have a bstat of 000000
    2009-05-13 14:14:42.38 spid129   1470 pages have a bstat of 000009
    2009-05-13 14:14:42.38 spid129   510 pages have a bstat of 000020
    2009-05-13 14:14:42.38 spid129   70 pages have a bstat of 001001
    2009-05-13 14:14:42.39 spid129   68 pages have a bstat of 00000b
    2009-05-13 14:14:42.39 spid129   BPool::Map: no remappable address found.
    2009-05-13 14:14:42.42 spid96    BPool::Map: no remappable address found.
    2009-05-13 14:14:42.49 spid104   BPool::Map: no remappable address found.
    2009-05-13 14:14:42.58 spid128   BPool::Map: no remappable address found.
    2009-05-13 14:14:42.58 spid76    BPool::Map: no remappable address found.
    2009-05-13 14:14:42.66 spid109   BPool::Map: no remappable address found.
    2009-05-13 14:14:42.77 spid128   Buffer Distribution:  Stolen=170724 Free=1028 Procedures=674
      Inram=0 Dirty=32118 Kept=0
      I/O=0, Latched=850, Other=3324974
    2009-05-13 14:14:42.77 spid128   Buffer Counts:  Commited=3530368 Target=3530368 Hashed=3357942
      InternalReservation=284 ExternalReservation=472 Min Free=1024 Visible= 173408
    2009-05-13 14:14:42.77 spid128   Procedure Cache:  TotalProcs=69 TotalPages=674 InUsePages=519
    2009-05-13 14:14:42.77 spid128   Dynamic Memory Manager:  Stolen=169521 OS Reserved=1648
      OS Committed=1626
      OS In Use=1608
      Query Plan=163802 Optimizer=1879
      General=4075
      Utilities=278 Connection=835
    Friday, May 15, 2009 7:13 PM
  • have you include /PAE in boot.ini to support the use of  more 4 Gigabyte Physical Memory in 32 bit system?

    Saturday, May 16, 2009 2:13 AM
  • Hi,

    This case is similar with the issue that the following KB article describes.
    http://support.microsoft.com/kb/319477

    Based on the KB article descriptions, you need to install the last SQL Server 2000 Service Pack.
    http://support.microsoft.com/kb/290211/ 


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, May 18, 2009 9:30 AM
    Moderator
  • That KB article looks like exactly what I am seeing in my error logs but when I check the version of DB, I am on SP4.

    8.00.2040  SP4     Enterprise Edition
    Tuesday, May 19, 2009 5:29 PM