SQL Server Performance Forum – Threads Archive

Q834628 doesn’t seem to work

I am running SQL Server 2000 Ent Edition, sp3a, W2K3, 8 CPU, 8GB (max sql memory 6GB), /3GB I applied hotfix Q834628, referenced here http://support.microsoft.com/default.aspx?scid=kb;EN-US;838765</a> It doesn’t seem to help. I am still getting these sorts of errors (listed below, delimited by broken line). Any ideas? ———– 2004-11-09 21:55:00.26 server Error: 17883, Severity: 1, State: 0 2004-11-09 21:55:00.26 server The Scheduler 4 appears to be hung. SPID 210, ECID 0, UMS Context 0x06B73F38. ———– 2004-11-10 21:34:35.16 spid1 Error: 823, Severity: 24, State: 4 2004-11-10 21:34:35.16 spid1 I/O error 1450(Insufficient system resources exist to complete the requested service.) detected during write at offset 0x00000042530000 in file ‘<MDF file>’.. ———– 2004-11-13 17:05:25.17 spid2 LogWriter: Operating system error 1450(Insufficient system resources exist to complete the requested service.) encountered. 2004-11-13 17:05:25.26 spid2 Write error during log flush. Shutting down server 2004-11-13 17:05:25.55 spid167 Error: 9001, Severity: 21, State: 4 2004-11-13 17:05:25.55 spid167 The log for database ‘<database>’ is not available.. 2004-11-13 17:05:25.62 spid167 Error: 9001, Severity: 21, State: 1 2004-11-13 17:05:25.62 spid167 The log for database ‘<database>’ is not available.. 2004-11-13 17:05:25.68 spid167 Error: 9001, Severity: 21, State: 1 2004-11-13 17:05:25.68 spid167 The log for database ‘<database>’ is not available.. 2004-11-13 17:05:25.77 spid167 Error: 3314, Severity: 21, State: 4 2004-11-13 17:05:25.77 spid167 Error while undoing logged operation in database ‘<database>’. Error at log record ID (32283:38320:3).. 2004-11-13 17:05:25.79 spid167 Error: 9001, Severity: 21, State: 1 2004-11-13 17:05:25.79 spid167 The log for database ‘<database>’ is not available.. 2004-11-13 17:05:25.85 spid167 Error: 3314, Severity: 21, State: 5 2004-11-13 17:05:25.85 spid167 Error while undoing logged operation in database ‘<database>’. Error at log record ID (32283:38320:1).. 2004-11-13 17:05:26.63 spid22 Database ‘<database>’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. 2004-11-13 17:05:26.70 spid22 Database ‘<database>’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. 2004-11-13 17:05:27.23 spid22 Starting up database ‘<database>’. 2004-11-13 17:05:34.53 spid22 Analysis of database ‘<database>’ (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> is 100% complete (approximately 0 more seconds) 2004-11-13 17:05:34.54 spid22 Recovery of database ‘<database>’ (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> is 0% complete (approximately 8 more seconds) (Phase 2 of 3). 2004-11-13 17:05:34.81 spid22 Recovery of database ‘<database>’ (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> is 100% complete (approximately 0 more seconds) (Phase 2 of 3). ———– *Dump thread – spid = 106, PSS = 0x57a61220, EC = 0x57a61548 *Stack Dump being sent to <location>SQLDump0001.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 11/13/04 19:24:30 spid 106 * * * * Input Buffer 140 bytes - * dbcc dbreindex(N'[dbo].[<tablename>]’, N”, 90, sorted_data_reorg) * …. 2004-11-13 19:24:32.55 spid106 Stack Signature for the dump is 0x6E979C26 2004-11-13 19:24:32.57 spid106 SQL Server Assertion: File: <buffer.c>, line=3745 Failed Assertion = ‘!(bp->bdbid == dbid && ALL_ON (BUF_HASHED | BUF_CHECKWRITE | BUF_DIRTY, bufstat) && IS_OFF (BUF_IO, bufstat) && bp->bpage->GetXdesId () == xdesId)’. 2004-11-13 19:24:32.64 spid106 Error: 3624, Severity: 20, State: 1.

To me it seems the root error is the 823 at the beginning. The 823 basically says, that this page cannot be read physically from disk. You should the disk and restore from the last good known backup ———————–
–Frank
http://www.insidesql.de
———————–

And also refer to the event viewer logs for any reference on the hardware issues that might be contributing this error too. As suggested you can restore from good old backup or detach the database and reattach with SP_ATTACH_SINGLE_FILE_DB for recreation of new Tlog file. When Windows I/O operations fail due to insufficient system resources or because of insufficient working set quota, SQL Server reports error 823 or error 8966 without attempting a retry. These conditions can be reported by the operating system on a normal system, due to the current workload at that instance. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Looks like you have not granted read/write access to the data/log files to the SQLServer service account…? Cheers
Twan

We are removing the /3GB option. Here are some articles we found. We also found that the available PTEs under normal conditions when /3GB is off was about 180000. When /3GB is on, it dropped to about 10000. Thus, it is pretty clear that under load, the OS kernel was starved for PTE availablility, which eventually led to all the Operating Sytem 1450 errors. http://groups.google.com/groups?q=pae pte sql server problem&hl=en&lr=&sa=N&tab=wg http://www.microsoft.com/whdc/system/platform/server/PAE/pae_os.mspx

Thanx for posting a followup on this issue.

]]>