SQLServerCentral / SQL Server 2005 / Data Corruption

Sharepoint database 'docs' corruption ... what if?

N.D — Wed, 15 May 2013 09:53:56 GMT

Long story short :DWe inherited a sharepoint database on SQL2k with corruption dating back 2 years (so no usable backups). Corruption is affecting only 1 document stored in the docs table (99% sure - from the checkdb results it looks like one of the pages storing this document is damaged, opening the document from Sharepoint fails and gives a a corresponding 823 error in the log)DBCC CheckDB says that repair_allow_data_loss is the minumum repair option However - what would happen if the document was just deleted from Sharepoint? It sounds plausible, but I'm assuming that there is more to it than that :D

Stand by/read only database on SUSPECT

YeeHwua — Mon, 10 Dec 2012 12:03:26 GMT

Hi all, I had the issue twice back to June, and Oct this year.Our environment uses log shipping between two long distance geographical locations. One day on June(another on Oct), the secondary db restore job was failed, the secondary db was on SUSPECT mode.As searched from forum, tried to set the db to emergency mode, but error message indicates the action failed because the db was still in restore process.Since the secondary db is designed as stand by read only mode, am I right that the db couldn't be backup? So that on secondary db location there won't be any full backup set to recovery.What had i done was:1. stop log shipping from primary db;2. started a new full backup on primary db;3. build a maintenance plan for primary db log backup;4. copy the full backup and backup logs from primary db location to secondary db location(where i work);5. restore full backup and the backup logs in stand by mode;6. restart a new log shipping job on primary db.My question: is this would be best way to deal with the issue, is there any other way could be resolving the issue faster, my primary db is 120GB, copy full backup(even zipped) over through network will take 15+ hours, and my secondary db need to be accessible according to our business requirement.I hope someone could answer my question.thanks

Table data Corrupted

DIB IN — Thu, 17 May 2012 06:03:19 GMT

While Fetching record from a table we are getting below error for few records. Error:-Msg 823, Level 24, State 2, Line 1The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x000000344c000 in file 'D:\DB_NAME\DB_NAME.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.After Executing DBCC CHECKTABLE ("TABLE_NAME") We are getting below error:-Msg 8966, Level 16, State 2, Line 1Unable to read and latch page (1:117) with latch type SH. 23(Data error (cyclic redundancy check).) failed. CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.DBCC results for 'TABLE_NAME'.Msg 2533, Level 16, State 1, Line 1Table error: page (1:117) allocated to object ID 1606, index ID 0, partition ID 7205, alloc unit ID 72057 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.There are 932 rows in 927 pages for object "TABLE_NAME".CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'TABLE_NAME' (object ID 1606).repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DB_NAME.dbo.TABLE_NAME).DBCC execution completed. If DBCC printed error messages, contact your system administrator.Please advice how we could rectify this error.Regards

dees is een probeersel

e1125443 — Wed, 10 Apr 2013 08:07:54 GMT

please delete my post

Database gone into Suspect mode after the execution of an SP

SQLServerScrapbook — Wed, 10 Apr 2013 01:54:16 GMT

[b]SQL Server Version: 2005 Standard RTM version 9.00.1406.00 [/b]A Stored Procedure(This SP Runs everyday but has never caused suspect mode) was running which does a large amount of data transfer within a transaction. The transaction might remain active for about 15 to 20 minutes. There might also be other processes accessing the table which is under transaction. So blocking might have been occurring. The Database went into suspect Mode generating huge number of Dump files. The DB was however restored from the Backup files. My concern is that the same issue could rise again as we are not able to identify the problem behind it.[b]Some error data from Dump files:[/b][b]First File Dump File:[/b]* 04/04/13 01:18:16 spid 95 * Location: lckmgr.cpp:9421 * Expression: NULL == m_lockList.Head () [b]Second Dump File:[/b]* 04/04/13 01:18:20 spid 95 * Exception Address = 0000000000000000 Module(sqlservr+FFFFFFFFFF000000) * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION * Access Violation occurred writing address 0000000000000000 [b]Third Dump File:[/b] * 04/04/13 01:18:23 spid 95 * Location: "xact.cpp":2630 * Expression: !m_updNestedXactCnt * SPID: 95 * Process ID: 268 * Description: Trying to use the transaction while there are 1 parallel nested xacts outstanding[b]Fourth Dump File:[/b]* 04/04/13 01:18:43 spid 95 * ex_raise2: Exception raised, major=52, minor=42, state=1, severity=22 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Begin transaction log dump for database 'DB_Name'; for rollback failure - filter XdesId.--Huge Number of Transaction Log Entries Here--[b]Fifth Dump File:[/b] * 04/04/13 01:21:40 spid 18 * Location: page.cpp:4245 * Expression: spaceNeeded <= spaceContig && spaceNeeded <= space_usable [b]Sixth Dump File:[/b]* 04/04/13 01:21:40 spid 18 * HandleAndNoteToErrorlog: Exception raised, major=34, minor=48, severity=21 [b]Next Dump File:[/b]* 04/04/13 01:21:57 spid 21 * Location: page.cpp:4100 * Expression: !pageFull * SPID: 21 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Begin transaction log dump for database 'DB_Name'; for REDO failure - filter on PageId.++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Begin transaction log dump for database 'DB_Name'; for REDO failure - filter XdesId.++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Begin transaction log dump for database 'DB_Name'; for recovery failure - no filter.[b]Seventh Dump File:[/b]* 04/04/13 01:24:39 spid 98 * Exception Address = 000000000288FC14 Module(sqlservr+000000000188FC14) * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION * Access Violation occurred reading address 0000000000000000[b]Eighth Dump File:[/b]* 04/04/13 01:24:41 spid 98 * Location: T:\Yukon\sqltdbms\storeng\dfs\manager\lckmgrp.h:675 * Expression: m_iteratorInSafeState [b]Nineth Dump File:[/b]* 04/04/13 01:24:41 spid 98 * ex_terminator - Last chance exception handling

Database Error - MySQL - 1030

bradyjimbsg — Mon, 08 Apr 2013 23:58:52 GMT

Can somebody tell me what this error code means, 1030? How do you handle/deal with this?

2k5 MDF Emptyfile -> NDF

DuncEduardo — Tue, 02 Apr 2013 05:33:09 GMT

Hi,I wonder if someone can help?I was attempting to move contents of mdf to a secondary data file ndf but this failed about 20% through the process with the error "cannot move all contents of file .... using emptyfile command"Command was: [b]dbcc shrinkfile(1,emptyfile)[/b].There are 3 files in same filegroup MDF, LDF and newly created NDF.I created NDF on separate drive as this had plenty of spaceMDF was around 5GB with almost 5GB free space.When the process failed, around 1GB of data was "emptied" to NDF.My question is this: the original MDF is still showing 5GB. Can I simply remove the NDF and try another method on MDF, i.e. SHRINKFILE (1, 100)Someone suggested installing SP3 on 2k5 but I'm not in a position to do this nor is it advisable due to some applications running off this server.So, I just wish to shrink MDF and avoid the NDF method but I don't want to lose any data with this incomplete process.My original plan was:a)create ndfb)empty mdf using shrinkfile/emptyfilec)shrink mdfd)empty ndf to move data back to mdfe)remove ndfAny suggestions?Thanks,Dunc

Time-out occurred while waiting... Reset to device warning

Andres Zoppelletto — Sat, 23 Mar 2013 23:02:20 GMT

Hi guys and girls, I hope you can help me.I´m running SQL2005 since a couple of years on server, and receantly, with no reason, I´m having some kind of problem with query execution. I´m getting two kind of errors:"Time-out occurred while waiting for buffer latch type 2 for page (3:210666), database ID 9." and"Could not continue scan with NOLOCK due to data movement". That last message is showed when I use with (nolock) option.Generally occurs when I use large size tables as the source.Looking on system event log, everytime I get the SQL errore, I see a warning that says "Reset to device, \Device\RaidPort0, was issued". Could be a IO issue?Any ideas? Thanks!

Corrupt or tampered with?

danaanderson — Thu, 21 Mar 2013 00:21:51 GMT

When I boot up my computer, I get an error dialog for SQL Server. I don't understand what it means, it speaks of a package ID. I thought those were SSIS or some other special thing. I've never done any of that. I have attached the screen capture of the dialog. Does anyone know exactly what this means, and what I should look for? I had SQL Server 2000 developer edition, which came with Office XP Developer's edition. But I've uninstalled that since I now use SQL Server 2005 on my website and use SSMS to connect to it. I also have the 2008 version, which came with Visual Studio 2010. But that error message was coming up before I had that.Help?Dana

Help Please - DBCC Check Failed-The operating system returned error 87

JC-3113 — Mon, 11 Mar 2013 08:54:06 GMT

Hi folkscame in today and found this error on one of my databases.Would appreciate any assistance on this as I have not seen this type of error beforeIf you could lend me your expertise in this i would greatly appreciate it:Event Error:The operating system returned error 87(The parameter is incorrect.) to SQL Server during a write at offset 0x000001cdc58000 in file 'K:\MSSQL\UserDB\Data\SP04_WSS_Content.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.ThanksJimuse [sp04_wss_content];dbcc checkdb;goChanged database context to 'SP04_WSS_Content'.Msg 823, Level 24, State 3, Server NSAB-SS80-SQL-N, Line 4The operating system returned error 87(The parameter is incorrect.) to SQL Server during a write at offset 0x000001cdc58000 in file 'K:\MSSQL\UserDB\Data\SP04_WSS_Content.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information,see SQL Server Books Online.Msg 1823, Level 16, State 2, Server NSAB-SS80-SQL-N, Line 4A database snapshot cannot be created because it failed to start.Msg 7928, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 4The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.Msg 5030, Level 16, State 12, Server NSAB-SS80-SQL-N, Line 4The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 4

Database Corrupt

kristof 75250 — Tue, 29 Jan 2013 13:11:39 GMT

Hi , Last week we have a server crash in the datacenter .one of our customers use a SQL Database.When I would restore / attach the mdf , I get a a lot of errors .When we execute the following query : DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)Error:Msg 7985, Level 16, State 2, Line 1System table pre-checks: Object ID 4. Could not read and latch page (1:148) with latch type SH. Check statement terminated due to unrepairable error.DBCC results for 'Exp_0700SWPDeLoods'.Msg 5233, Level 16, State 98, Line 1Table error: alloc unit ID 262144, page (1:148). The test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. The values are 29362185 and -1.CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.CHECKDB found 0 allocation errors and 1 consistency errors in database 'Exp_0700SWPDeLoods'.We also try to update the status from the systables , also without any result We hope someone could help us out .When you need the mdf file , I could send you

CheckDB fails Sunday, but ok Monday without repair

Del Lee — Mon, 21 Jan 2013 09:40:37 GMT

Hi all:Early Sunday morning, our daily check of database integrity failed on one of our databases. The task is not set to auto repair. Then, early this morning the check passed without error. Even though the issue seems to have resolved itself, I am still concerned, so I wanted to post to get other comments.Other information: According to the error log, just before the integrity check completed there were a couple of entries indicating some I/O requests were taking longer than 15 seconds and another saying "Worker 0x03FA60E8 appears to be non-yeilding on scheduler 1." I've done a google search on this, but I don't see anything obvious so far that relates to my situation. Also, there was a couple of "timeout while waiting for buffer latch - type2" messages.I'll continue to google search some of this, but I'd be interested in any comments you guys may have based on the above.

Found a database error and need advice

Bob Shaw — Tue, 29 Jan 2013 12:37:35 GMT

I was testing some backups on a test server and found that a database generated an error when running CHECKDB BDCC (N'<DB NAME>') WITH NO_INFOMSGS."Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded."Since this is a test server I tried it on the production version using , ALL_ERRORMSGS. So DBCC CHECKDB(N'<DB NAME>') WITH NO_INFOMSGS, ALL_ERRORMSGS is returing this: "Msg 8921, Level 16, State 1, Line 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.Msg 8966, Level 16, State 4, Line 1Unable to read and latch page (1:372048) with latch type SH. UtilDbccVerifyPageId failed.CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.Msg 2579, Level 16, State 1, Line 1Table error: Extent (1:378648) in object ID 5, index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data) is beyond the range of this database.CHECKDB found 1 allocation errors and 0 consistency errors in table 'sys.sysrowsets' (object ID 5).CHECKDB found 1 allocation errors and 1 consistency errors in database '<DB NAME>'."This database just got moved to our server in October and I did a check the first backup we have of that database and its giving the exact same error as the current production. The application is still using this database without reporting any errors so Im not sure if its the user data that is causing the problem or a system object. I checked the tempdb location and there is plenty of drive space.So my questions are:What would be the best way to handle this database now? Why would SQL Server allow a database to get attached/restored if there is a problem with the database?I havent been running a CHECHDB CHECKDB automatically but that will change now.What would be the best schedule to run this? Daily, Weekly, Monthly?

DBCC CHECKDB taking a very long time to run on 500MB database

mjjordan — Mon, 21 Jan 2013 07:38:37 GMT

I want to start by advising that I am new to MS SQL so I ask for your patience. We lost access to our Sharepoint 'companyweb'.I tried deleting the site and recreating it in SharePoint but this did not work as we are still without companyweb access. During the process I received errors stating that ShareWebDb was not accessible.After Googling around I determined that the ShareWebDb database was at the root of my issues.I ran the following on the ShareWebDb-- CHECK THE STATUS OF THE DATABASE SELECT DATABASEPROPERTYEX('ShareWebDb', 'STATUS') as 'DBStatus'This lead to me finding the database was in 'SUSPECT' statusI then ran the following to set the database into 'emergency status'-- IF DATABASE IS 'SUSPECT' set to 'emergency' ALTER DATABASE ShareWebDb SET EMERGENCYI then set the database into Single user mode;-- Set database to single user mode to run checkdb utility ALTER DATABASE ShareWebDb SET SINGLE_USER WITH ROLLBACK IMMEDIATEThen started the DBCC CHECKDB to start he repair process.-- run the checkdb command DBCC CHECKDB (ShareWebDb, REPAIR_ALLOW_DATA_LOSS)The database is only about 500MB and the above has been running for 4 days 17 hours....I am thinking there's an issue with this process but if it takes this long i don't want to lose the 4 days of processing I have already run. I know that DBCC CHECKDB is a single thread process and can therefore result in it taking some time to run but I am thinking that to process 500MB of data shouldn't take this long.I checked the status of the process by runningselect * from sys.dm_exec_requests percent_complete where session_id = 56and it shows the status as 'suspended'. My question is, is it reasonable to think this process is going to take 4+ days to run on a 500MB database or should I kill it and try another approach? How can see/determine how far along the DBCC CHECKDB process is?If another approach is advisable what is your recommended approach to resolving this issue?Thanks for your time and knowledge in advance.

ERROR 823 in SQL 2000 -> Need help

shovankar — Wed, 09 Jan 2013 04:22:54 GMT

Hi All, Good day and Happy New Year !!I need your inputs for the below issue -We have a 1.5 Terabyte SAP database which runs on SQL 2000 SP4 enterprize edition.The SQL instance is on Win2k3. For last couple of months I have observed that we are getting error 823 errors very frequently in the eventviewer - Error: 823, Severity: 24, State: 2I/O error (bad page ID) detected during read at offset 0x000005981be000 in file ...I found out a particular table for which the pages were corrupt and was able to fix the error with CHECKDB. We have a index defragment job set up on the database which runs DBCC INDEXDEFRAG on weekly basis on the database. Its been failing for some time with the error - Object ID 0, index ID 0, page ID (6:2932957). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (6:2932957). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) I/O error (bad page ID) detected during read at offset 0x00000598246000 in file . [SQLSTATE HY000] (Error 823). The step failed.This time I checked the page 6:2932957 and could see that the page is zeroed out - dbcc traceon (3604)dbcc page (IOD,6,2932957,3)Output : PAGE: (0:0)-----------BUFFER:-------BUF @0x04B843C0---------------bpage = 0x457AE000 bhash = 0x00000000 bpageno = (6:2932957)bdbid = 5 breferences = 1 bstat = 0x809bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x457AE000----------------m_pageId = (0:0) m_headerVersion = 0 m_type = 0m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0m_objId = 0 m_indexId = 0 m_prevPage = (0:0)m_nextPage = (0:0) pminlen = 0 m_slotCnt = 0m_freeCnt = 0 m_freeData = 0 m_reservedCnt = 0m_lsn = (0:0:0) m_xactReserved = 0 m_xdesId = (0:0)m_ghostRecCnt = 0 m_tornBits = 0 Allocation Status-----------------GAM (6:2556160) = ALLOCATED SGAM (6:2556161) = NOT ALLOCATED PFS (6:2927856) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULLDIFF (6:2556166) = CHANGED ML (6:2556167) = NOT MIN_LOGGED Further, I can see that the pages in the range of 2932957 to 2932972 are all zeroed out. Below is the GAM Allocation details - PAGE: (6:2556160)-----------------BUFFER:-------BUF @0x01A6AF40---------------bpage = 0x5DD5C000 bhash = 0x00000000 bpageno = (6:2556160)bdbid = 5 breferences = 1 bstat = 0x9bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x5DD5C000----------------m_pageId = (6:2556160) m_headerVersion = 1 m_type = 8m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0m_objId = 99 m_indexId = 0 m_prevPage = (0:0)m_nextPage = (0:0) pminlen = 90 m_slotCnt = 2m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0m_lsn = (13006:314808:58) m_xactReserved = 0 m_xdesId = (0:0)m_ghostRecCnt = 0 m_tornBits = 0 Allocation Status-----------------GAM (6:2556160) = ALLOCATED SGAM (6:2556161) = NOT ALLOCATED PFS (6:2555808) = 0x40 ALLOCATED 0_PCT_FULL DIFF (6:2556166) = CHANGEDML (6:2556167) = NOT MIN_LOGGED GAM: Header @0x5DD5C064 Slot 0, Offset 96-----------------------------------------status = 0x0 GAM: Extent Alloc Status @0x5DD5C0C2------------------------------------(6:2556160) - (6:2566216) = ALLOCATED (6:2566224) - = NOT ALLOCATED (6:2566232) - (6:2641136) = ALLOCATED (6:2641144) - = NOT ALLOCATED (6:2641152) - (6:2758048) = ALLOCATED (6:2758056) - = NOT ALLOCATED (6:2758064) - (6:2845040) = ALLOCATED (6:2845048) - = NOT ALLOCATED (6:2845056) - (6:2870064) = ALLOCATED (6:2870072) - = NOT ALLOCATED (6:2870080) - (6:2922512) = ALLOCATED (6:2922520) - = NOT ALLOCATED (6:2922528) - (6:2928136) = ALLOCATED (6:2928144) - = NOT ALLOCATED (6:2928152) - (6:2990800) = ALLOCATED (6:2990808) - = NOT ALLOCATED (6:2990816) - (6:3005304) = ALLOCATED (6:3005312) - = NOT ALLOCATED (6:3005320) - (6:3066176) = ALLOCATED (6:3066184) - = NOT ALLOCATED (6:3066192) - (6:3067384) = ALLOCATED As the database is huge in size and very frequently used, it is not possible to run a full checkdb on the database at this moment. The SAN guys sent a report that there are no issues at the storage level. Below are the question that I am not able to sort out so far - 1. Is there a way that I can track this page back to a particular object in the database ? 2. Is there a relation between the DBCC INDEXDEFRAG and the pages getting zeroed out ? 3. What will be the best possible way to run a full consistency check on databases of this size ? 4. What is the possible reason 5. Can rebuild index be used instead of using DBCC INDEXDEFRAG in this case ?It would be really great if you could please provide with your valuable feedback on my questions. Regards, Shovan

CHKDSK on a SAN Volume

rothj — Fri, 27 Feb 2009 09:03:34 GMT

I need a little direction.I have a clustered SQL db (Sql 2000 / Win2k).I have errors in the log for corruption on drive S:\.This is my backup volume (data and tx logs are on separate vols).I attempted to run chkdsk but it said it needed to dismount because another process had it in use.Can I dismount this without taking SQL server down or do I need to stop the SQL server? Just trying to determine if I need to sched a downtime. Thank you.I was thinking if I shut down SQL Server Agent...nothing would try to use Drive S:\. There are tx log backups happening every 15 mins. ??Thanks for helping the rookie. :hehe:

DBCC - Could not repair this error.

sqlmate — Fri, 31 Aug 2012 13:40:55 GMT

Hi guys,one of my customer's hard drive failed. They were able to safe most of the data and create bit copy of the failed disk but few corrupted sectors were at MDF file. And guess what ... 'we have no backups'. So I run DBCC checkdb and it reported corruption of clustered index, tried to run dbcc checktable (alldocs,repair_allow_data_loss) and got thisDBCC results for 'AllDocs'.Repair: The Nonclustered index successfully rebuilt for the object "dbo.AllDocs, Docs_IdLevelUnique" in database "WSS_Content_04a315e0-42c4-40d5-a432-65590fe560b0".Msg 8945, Level 16, State 1, Line 1Table error: Object ID 69575286, index ID 1 will be rebuilt. Could not repair this error. Msg 8976, Level 16, State 1, Line 1Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728879) was not seen in the scan although its parent (1:2723806) and previous (1:2728878) refer to it. Check any previous errors. The error has been repaired.Msg 8978, Level 16, State 1, Line 1Table error: Object ID 69575286, index ID 1, partition ID 72057608226996224, alloc unit ID 72057608630173696 (type In-row data). Page (1:2728896) is missing a reference from previous page (1:2728879). Possible chain linkage problem. The error has been repaired.Msg 8945, Level 16, State 1, Line 1Table error: Object ID 69575286, index ID 2 will be rebuilt. The error has been repaired.There are 51682 rows in 3233 pages for object "AllDocs".CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'AllDocs' (object ID 69575286).CHECKTABLE fixed 0 allocation errors and 2 consistency errors in table 'AllDocs' (object ID 69575286).DBCC execution completed. If DBCC printed error messages, contact your system administrator.I think I ran out of options ... any thoughts? :w00t:

checkdb error on index ID 0

David Kang — Mon, 03 Dec 2012 12:10:22 GMT

I need help on fixing checkdb error.Here's the output of checkdb:Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID -1627389056, index ID 0, page ID (1:611861). The PageId in the page header = (14131:894586624).Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID 0, index ID 0, page ID (1:611862). The PageId in the page header = (0:0).Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID 0, index ID 0, page ID (1:611863). The PageId in the page header = (0:0).Server: Msg 8928, Level 16, State 1, Line 1Object ID 226099846, index ID 0: Page (1:611860) could not be processed. See other errors for details.Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 226099846, index ID 0, page (1:611860). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.Server: Msg 8928, Level 16, State 1, Line 1Object ID 226099846, index ID 0: Page (1:611861) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 226099846, index ID 0: Page (1:611862) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 226099846, index ID 0: Page (1:611863) could not be processed. See other errors for details.CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1627389056)' (object ID -1627389056).CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.CHECKDB found 0 allocation errors and 5 consistency errors in table 'REQ_ITEM' (object ID 226099846).CHECKDB found 0 allocation errors and 8 consistency errors in database 'PMM'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PMM ).It's running on SQL 2000 SP3.Can I repair these errors with 'REPAIR_REBUILD'?Thanks in advance,David.

BugCheck Dump: Non-yielding Resource Monitor

SQLdba-473999 — Wed, 05 Nov 2008 13:13:33 GMT

Has anyone ever experienced the following?in sql server log:2008-11-04 22:25:39.06 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x00000000000000002008-11-04 22:25:39.15 Server ***Stack Dump being sent to X:\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0002.txt2008-11-04 22:25:39.15 Server * *******************************************************************************2008-11-04 22:25:39.17 Server *2008-11-04 22:25:39.17 Server * BEGIN STACK DUMP:2008-11-04 22:25:39.17 Server * 11/04/08 22:25:39 spid 02008-11-04 22:25:39.17 Server *2008-11-04 22:25:39.17 Server * Non-yielding Resource Monitor2008-11-04 22:25:39.17 Server *2008-11-04 22:25:39.17 Server * *******************************************************************************2008-11-04 22:25:39.17 Server * -------------------------------------------------------------------------------2008-11-04 22:25:39.17 Server * Short Stack Dump2008-11-04 22:25:40.39 Server Stack Signature for the dump is 0x000000000000024EThe SQLDump0002.txt shows:===================================================================== BugCheck Dump ===================================================================== This file is generated by Microsoft SQL Server version 9.00.3159.00 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request. Computer type is AT/AT COMPATIBLE. Current time is 22:25:39 11/04/08. 8 Unknown CPU 9., 3167 Mhz processor (s). Windows NT 5.2 Build 3790 CSD Service Pack 2. Memory MemoryLoad = 18% Total Physical = 16381 MB Available Physical = 13398 MB Total Page File = 19036 MB Available Page File = 14023 MB Total Virtual = 8388607 MB Available Virtual = 8371762 MB **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000 ***Stack Dump being sent to X:\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0002.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 11/04/08 22:25:39 spid 0 * * Non-yielding Resource Monitor * * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump Please help!

SQL Server 2005 xp_logininfo error message

Scott E-381021 — Wed, 25 Mar 2009 14:05:17 GMT

If anyone has a valid solution, I am all ears.Here is the issue....when you run xp_logininfo, for some AD accounts (or run a SQL job as the owner being an AD account) you get the following error message: Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62Could not obtain information about Windows NT group/user 'mydomain\UserA', error code 0x5.The OS is Server 2003 Enterprise Edition SP1. SQL Server 2005 SP2 Cummulative update package #2UserA is an admin on the server and has SA rights in SQL Server......exact error message.UserB is not an admin on the server and has datareader only permissions in a single db, and xp_logininfo will validate UserB just fine.This is not a case sensitive username issue in this specific isntance, as noted on this website. I have tried every possible combination, and none of them work!I have tried dropping the login and recreating it..exact same error message.

BACKUP DATABASE is terminating abnormally

ravisamigo — Mon, 24 Sep 2012 01:39:24 GMT

Dear All,Could someone please advice on this.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Msg 845, Level 17, State 1, Line 1Time-out occurred while waiting for buffer latch type 3 for page (1:23723), database ID 6.------------------Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 7928, Level 16, State 1, Line 1The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.Msg 845, Level 17, State 1, Line 1Time-out occurred while waiting for buffer latch type 3 for page (1:23723), database ID 6.Thanks and Regards,Ravi.

corrupted mdf file

Ukon — Tue, 15 Mar 2011 15:29:05 GMT

hello: i was given a corrupted mdf file, the server the database (sql 2000) crashed and the only file recovered was this .mdf file. I have tried as many of the options that i can find, and still cannot get this database recovered. downloaded several of the programs that claim to be able to recover mdf files and none work.. This latest gives me the following error:SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xd8b4c0f2). It occurred during a read of page (0:0) in database ID 0 at offset 0000000000000000 in file 'C:\DBIO\testy.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I created a new database (sql 2008 r2) since I do not have sql 2000 anywhere. renamed the mdf file and then renamed the corrupted one to the new name and restarted. This then brings up the db in recovery_pending mode and I cannot get it out of that mode. Tried setting it to emergency mode, no luck. Is there anyway for me to recover this db? No there were no backups of this database at all, and yes, i completely agree that a backup strategy is needed, but i'm just the lucky recipient of the issue :)thank you for any help!

Regarding database backup corrupted

guptaisharinath — Wed, 15 Aug 2012 03:42:57 GMT

Hello Team,I was facing an issue with database backup which I took from sql server 2005.Actually in my office I have my own desktop, in my desktop sql server 2005 was installed, one fine morning I came to office and I started my system after starting my system I came to know that my system got corrupted, I just open sql server Management studio 2005 and took the back up, then after I formatted my system and installed upgraded version i.e, sql server 2008 R2, I opened sql server Management studio and tried to restore the back in 2008 which I took from 2005, while restoring the back up I am getting attached error.Please help me in resolving this issue.Regards,Harinath.

Finding a possible corrupt bit

Dan.Humphries — Wed, 14 Nov 2012 12:09:50 GMT

To All I have a table of the following definition[code="sql"]USE [CISPROD_DEV]GO/****** Object: Table [ADVANCED].[BIF041] Script Date: 11/14/2012 13:00:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [ADVANCED].[BIF041]( [C_CUSTOMER] [char](15) NOT NULL, [C_ACCOUNT] [char](15) NOT NULL, [T_TRANSDT] [datetime] NOT NULL, [C_TRANSCODE] [char](4) NOT NULL, [Y_AMOUNT] [decimal](15, 2) NOT NULL, [I_BILLNUMBER] [decimal](15, 0) NOT NULL, [I_TRANSNUM] [decimal](15, 0) NOT NULL, [C_USERID] [char](15) NOT NULL, [N_SUMFLAG] [decimal](1, 0) NOT NULL, [C_ARCODE] [char](2) NOT NULL, [T_DATETIME] [datetime] NOT NULL, [I_TOTAL] [int] NOT NULL, [C_COMPANY] [char](2) NOT NULL, [C_DIVISION] [char](2) NOT NULL, [I_SONUM] [numeric](15, 0) NOT NULL, [I_BIF041PK] [decimal](15, 0) NOT NULL, [D_AGING] [datetime] NULL, [L_EXCLUDETRANS] [bit] NOT NULL, CONSTRAINT [BIF041_I_BIF041PK] PRIMARY KEY CLUSTERED ( [I_BIF041PK] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CISDATA]) ON [CISDATA]GOSET ANSI_PADDING OFFGOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_CUSTOMER]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_ACCOUNT]GOALTER TABLE [ADVANCED].[BIF041] ADD CONSTRAINT [BIF041_T_TRANSDT_ZDEFA] DEFAULT (getdate()) FOR [T_TRANSDT]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_TRANSCODE]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [Y_AMOUNT]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_BILLNUMBER]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_TRANSNUM]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_USERID]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [N_SUMFLAG]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_ARCODE]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT (getdate()) FOR [T_DATETIME]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_TOTAL]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('01') FOR [C_COMPANY]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('01') FOR [C_DIVISION]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_SONUM]GOALTER TABLE [ADVANCED].[BIF041] ADD CONSTRAINT [BIF041_I_BIF041PK_ZDEFA] DEFAULT ((0)) FOR [I_BIF041PK]GOALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [L_EXCLUDETRANS]GO[/code]When the weekly maintenance ran the Update statistics died on this table saying there was a null value in a non null field. So I ran DBCC Checktable against the table and discovered the same type of error. I am hesitant to loose data and since the minamum repair level recommended by DBCC was to allow data loss I decided I would like to find the offending record. So after some digging I discovered the following script returned some odd results.[code="sql"]select L_EXCLUDETRANS, count(*) from ADVANCED.BIF041 group by L_EXCLUDETRANS [/code]this returnedL_EXCLUDETRANS Count0 107,242,3160 11 747,242So the question became why is it picking up a second 0 as a unique value. My second problem became how to find the record with the bad 0. I have tried selecting by zero selcting by 1 selecting by not in 0 or 1; not equal to 0; not equal to 1 I have tried restoring the DB to a different environment and the problem follows. I have imported the table alone into a new DB and the problem does not follow. I have tried to use Data compare in visual studios but it states all records match between the restored DB and the DB with the copied table. Does anyone have any tricks or tips to find this record?

Execution plans

ravisamigo — Tue, 09 Oct 2012 23:35:48 GMT

Hi All,If query is not using execution plans, how we can find it and what is the action required from DBA side?How we can reduce the query cost for select queries with subqueries[ Ex : select * from table1 where empid in ( select * from table2) ]?Please help me out on this.Thanks and Regards,Ravichandra.

Cannot retrieve row from page (1:2108289) by RID

Nido786 — Mon, 30 Jul 2007 06:57:00 GMT

MigrationData is my production database last few days i got the error message when i execute a procedure on this database, although i have installed new service pack 4 for sql server 2000.also i have recreated a new database By script but issue is same could any one help me in this regards

Error "

Server: Msg 625, Level 20, State 3, Procedure sp_TransformAccountAverageBalances, Line 27

Cannot retrieve row from page (1:2108289) by RID because the slotid (1) is not valid.

Connection Broken "

Regadrs

Syed Naveed

DBCC CHECKDB with Data_Purity consistency errors

D-SQL — Mon, 17 Sep 2012 09:08:41 GMT

DBCC CHECKDB with Data_Purity consistency errorsThis is the first time I ran this command and had errors. I am new to the company and they never had a DBA before. There SQL server has been upgraded all the way back from SQL 7 to 2008 R2. I ran the DBCC CHECKDB with Data_Purity consistency errors and here are a few if someone could point me in the correct direction to try and fix some of these which there are 11000They all are similar to theseMsg 2570 Level 16 State 3 Line 1 Page (1:275392) slot 2 in object ID 536649255 index ID 1 partition ID 72057594088390656 alloc unit ID 72057594089635840 (type "In-row data"). Column "LTV" value is out of range for data type "real". Update column to a legal value.Msg 2570 Level 16 State 3 Line 1 Page (1:275392) slot 8 in object ID 536649255 index ID 1 partition ID 72057594088390656 alloc unit ID 72057594089635840 (type "In-row data"). Column "LTV" value is out of range for data type "real". Update column to a legal value.Msg 2570 Level 16 State 3 Line 1 Page (1:275392) slot 15 in object ID 536649255 index ID 1 partition ID 72057594088390656 alloc unit ID 72057594089635840 (type "In-row data"). Column "LTV" value is out of range for data type "real". Update column to a legal value.Msg 2570 Level 16 State 3 Line 1 Page (1:275393) slot 0 in object ID 536649255 index ID 1 partition ID 72057594088390656 alloc unit ID 72057594089635840 (type "In-row data"). Column "LTV" value is out of range for data type "real". Update column to a legal value.

How can i drop table?

nayminnag — Wed, 12 Sep 2012 03:13:44 GMT

Dear all,I have an error in my database. I write command that is 'Drop table tb_name'.When i run that command, I got the error that is:'Could not find the index entry for RID '1674e85621000300' in index page (1:182), index ID 2, database 'db_name''So, may i know how can i drop this table.Thanks for All,Nay Min

SQL Server detected a logical consistency-based I/O error : SQL 2005

sephrobbertse — Thu, 30 Aug 2012 02:24:45 GMT

Getting this:Msg 926, Level 14, State 1, Line 2 Database 'DM_m000003_m000002_qdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed. Msg 824, Level 24, State 2, Line 2 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x627c02d9; actual: 0xb59aada1). It occurred during a read of page (1:6587397) in database ID 9 at offset 0x00000c9080a000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DM_m000003_m000002_qdb.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. Msg 3313, Level 21, State 2, Line 2 During redoing of a logged operation in database 'DM_m000003_m000002_qdb', an error occurred at log record ID (116769:7548:175). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database. Msg 3414, Level 21, State 1, Line 2 An error occurred during recovery, preventing the database 'DM_m000003_m000002_qdb' (database ID 9) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. Cannot do dbcc, or backup. Any tips?

Can't drop, detach or set SUSPECT database to emergency mode

debbie_jacob — Fri, 11 Dec 2009 07:57:42 GMT

I have a database in a dev enviroment that is in SUSPECT mode. This is 2005. I don't need to recover the database. It is just development and the users can restore and/or upgrade from backup. I just want to remove it. I have browsed the forums but have not found a way to do it. Here is what I tried and the messages I received back:> drop database my_databaseMsg 926, Level 14, State 1, Line 1Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.> EXEC sp_detach_db 'my_database';Msg 3707, Level 16, State 2, Line 1Cannot detach a suspect database. It must be repaired or dropped.> ALTER DATABASE my_database SET EMERGENCY;Msg 926, Level 14, State 1, Line 1Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed.I am not sure what else to try. Is there a way to force a db to be dropped? I can't open/view the errorlog (another issue, perhaps related?). I get an error message saying "Unicode File expected". Our RAID 5 had some issues last week and the systems guy fixed that. There are also 5 dbs whose state is "Recovery_Pending". I mention this in case it is all related and that it might help. I would like to remove these as well but I will tackle one issue at a time. :) Thanks!

Multiple databases constantly going into Recovery Mode

shanewiso — Sat, 11 Aug 2012 00:15:23 GMT

Hi,Several databases are going into Recovery mode, when I view them in SSMS. Only for a few seconds at a time, then the go back to normal...The problems with this are some client app's are trying to connect and cant do so when required.I was running on Express edition, but just recently migrated to SQL Server 2012 standard.The issue is still occuring regardless of this upgrade.What should I be looking at,.. the servers memory and CPU usage are fine when this occurs.Sometimes you expand Tables in SSMS and it takes forever, then it will go into Recovery mode, sometimes on 3 databases at once, somethimes just 1.Where do I start ?

tempdb location change - sql not coming online

Benki Chendu — Wed, 01 Aug 2012 04:02:41 GMT

We have a newly built cluster environment and there is a dedicated disk for the tempdb.Unfortunately, after the setup, I forgot to put tempdb in that drive.so customer himself tried moving it.Without that disk "S" being added as dependency to SQL Server resource in the cluster, customer tried to move tempdb and restarted SQL Server.Now, SQL Server is not coming online as tempdb doesnt really exist anywhere.Can someone help me fix this issue ASAP

Can not modify table properties (Urgent)

manju.ccc — Tue, 24 Jul 2012 20:54:45 GMT

Hi all,i need to update a property of one of my table. Ex. update sys.tables set is_merge_published='0' where object_id='123456' when i run the query it gives an error like "Ad hoc updates to system catalogs are not allowed" then I tried with "sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO" but it doesnt work. im using sql server 2005. thanks.

DBCC Fails When Multiple Timestamp Columns Are In View

Stamey — Tue, 24 Jul 2012 06:02:49 GMT

Background:Developers took note of rule regarding Full Text Indexes, in that in order to have incremental updating of a full text index occur, you must have a TimeStamp column in your table. (http://technet.microsoft.com/en-us/library/ms142575.aspx)What the docs don't address, and caused the developers to make assumptions on, is what if you are creating an FTI on a view, which combines two, or more tables, that have TimeStamp columns in them. My developers thought, "if a TimeStamp column is required to get incremental updates to a FTI, then we need to pull that column into the view so it is eligible for incremental updates, and since we pull data from three tables, and any one of them could change independently of another, we need to include the TimeStamp columns from each table in our view."Right or wrong, I understand where they are coming from, because the documentation does not address this situation, and SQL Server does allow multiple TimeStamp columns in a view.Now, as this is Enterprise version of SQL Server 2005, we also have an index on that view.Now to the problem. When I run integrity checks on the DB, I get an error when DBCC gets to that view, with the error:Msg 2738, Level 16, State 2, Line 1A table can only have one timestamp column. Because table '#priv_iv_temp_table9674_0' already has one, the column 'PharmacyIdInsertUpdateTimestamp' cannot be added.Outcome: SucceededThis is because DBCC looks at a view as a table, and applies the rules for tables to the views.In my testing, I have found that DBCC only has a problem with multiple TimeStamp columns in a view if the index is on the view. If the index is not on the view, DBCC sails right through it, even though it, in this case, has three TimeStamp columns in the view.My goal is to stop the DBCC errors as it fails my data integrity check job. I am open to design changes, provided I don't adversely affect performance.I have posted this question to design forums but have never received a satisfactory response regarding changes to my design. It seems like FTI documentation/knowledge as as elusive as Bigfoot.Thanks,Chris

DBCC Hangs

Stamey — Fri, 20 Jul 2012 09:15:37 GMT

I have a 600GB DB on a SQL 2005 server that causes DBCC to stop responding when running CheckDB. This server has many other DBs on it, and DBCC checkDB is successful with those, until it gets to this one DB. DBCC never returns any kind of status or error, even with it running for 8 hours yesterday. I have databases over 1TB that run CheckDB faster.The database is set to SQL 80 compatibility, for reasons I do not know, and Simple recovery. I inherited this. It has a single data file and a single log file. The DB server is version 9.0.3353. The database is still accessible and I have had no complaints from users about it. I'm just trying to run DBCC as part of maintenance.Anyone know what's up with this?Thanks,Chris

DBCC CHECKDB Error Msg 2570, Level 16, State 3

bsock — Mon, 27 Sep 2010 08:59:32 GMT

[b] Over the weekend, our database received this error when the DBCC CHECKDB job ran: [/b][font="Courier New"]DateTime: 2010-09-26 23:15:08Command: DBCC CHECKDB ([NSpireDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYMsg 2570, Level 16, State 3, Server NSPIRE, Line 1Page (1:219429), slot 7 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "insp_vol" value is out of range for data type "real". Update column to a legal value.Msg 2570, Level 16, State 3, Server NSPIRE, Line 1Page (1:219429), slot 7 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "sys_dead_space" value is out of range for data type "real". Update column to a legal value.Msg 2570, Level 16, State 3, Server NSPIRE, Line 1Page (1:219429), slot 8 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "insp_vol" value is out of range for data type "real". Update column to a legal value.CHECKDB found 0 allocation errors and 3 consistency errors in table 'dl_calc' (object ID 1445580188).CHECKDB found 0 allocation errors and 3 consistency errors in database 'NSpireDB'.Outcome: FailedDuration: 00:03:56DateTime: 2010-09-26 23:19:04[/font][b] I've done some research and ran the following command to narrow down the issue: [/b][code="sql"]DBCC TRACEON (3604)GODBCC PAGE (NSpireDB, 1, 219429, 3)GO [/code][b] Which printed these results about slot 7 and slot 8: [/b][font="Courier New"]Slot 7 Column 32 Offset 0xab Length 4insp_vol = 2.00386e-043 Slot 7 Column 40 Offset 0xcb Length 4sys_dead_space = 2.00386e-043 Slot 8 Column 32 Offset 0xab Length 4insp_vol = 2.00386e-043[/font][b] This data is stored in a table called [i] dl_calc [/i] in a column named [i]insp_vol[/i] that is of type [i]"real" (length 4, precision 24, scale 0)[/i]. Can someone help me with what I need to do next to fix the error message? What does that number equal in the results? Do I need to run an update statement against the rows affected? How do I determine what rows to update? Thanks! [/b]

Page missing in a Clustered Index on a table

WayneS — Mon, 09 Jul 2012 12:52:45 GMT

Running DBCC Checkdb on a database yielded the following:[quote]DBCC CHECKDB(N'MyDB') WITH NO_INFOMSGSTable error: Object ID 2057110419, index ID 1, partition ID 72057597444161536, alloc unit ID 72057597501833216 (type In-row data). Page (1:7823149) is missing a reference from previous page (1:7823150). Possible chain linkage problem.Table error: Object ID 2057110419, index ID 1, partition ID 72057597444161536, alloc unit ID 72057597501833216 (type In-row data). Page (1:7823150) was not seen in the scan although its parent (1:5720869) and previous (1:7823151) refer to it. Check any previous errors.CHECKDB found 0 allocation errors and 2 consistency errors in table 'MyTable' (object ID 2057110419).CHECKDB found 0 allocation errors and 2 consistency errors in database 'MyDB'.repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).[/quote](Names of DB and table changed...)Index 1 is the clustered index on this table (and also it's PK), and there are no other indexes on this table. One of the columns in the PK (the last column) is an identity column. Client says that data appears to be only inserted into the table - and that they don't have a maintenance window. The table has ~ 200 million records.I'm thinking that I should:Restore the database to a parallel database, ensure that it is a clean DB with DBCC CHECKDB, and then check the gaps on the identity column. Run the repair_rebuild on this database, and check for gaps on the identity column.Compare the gaps, and we'll know which ones need replaced.Gail, Paul, and other corruption gurus... do you see anything else that I should look into or have any other suggestions for how to handle this?Thanks in advance for taking time out of your busy day to look / respond to this.

Unlocking resources not owned DBCC CheckDB failure

matt.newman — Mon, 11 Jun 2012 10:19:31 GMT

Problem:I got the error message -[code="plain"]Check Database Integrity Task (MANNASQL2) Check Database integrity on Local server connectionDatabases: |database list here|Include indexesTask start: 2012-06-09T03:28:56.Task end: 2012-06-09T03:55:11.Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "Process ID 55 attempted to unlock a resource it does not own: PAGE: 29:1:25771460. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.During undoing of a logged operation in database 'TheDatabase', an error occurred at log record ID (6729214:1576:425). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.A database snapshot cannot be created because it failed to start.A database snapshot cannot be created because it failed to start.The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.The database could not be exclusively locked to perform the operation.Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/code]I located the MSDN article:http://msdn.microsoft.com/en-us/library/aa337285(v=sql.105).aspxI am running DBCC on the DR machine, a restore from a few days ago on our staging machine, and our Net Admin is copying the backups from last night to the Stage server so I can do a restore and test. I will be doing DBCC later tonight on the production server, off business hours.Background:2005 SP4 Standard.DBCC runs once weekly.No previous error from previous weeks.Windows system and application logs gave no additional information.Anyone seen this error message WITHOUT any notification of a suspect database? I will try to rerun DBCC on the production as stated, off normal hours of operation, but I am curious what I might see when I do as a heads up. Just trying to get a game plan. I have looked over the beautiful interwebs knowledge base but am lacking anything that is spot on and tells of resolution. All other testing has completed without problems and the system has been up for two days without any reported errors seen. All other logs look clean, all backup directories look clean and plenty of space allocations ready to dump files. Database that failed is only about 235GB size, on Raid 10, transferring to Raid 0. Have not seen this in the year of being here. New datamart/warehouse snapshots were recently added, though did not affect the checkdb process last weekend.

Microsoft SQL Server Error 904

vhr2003 — Mon, 02 Aug 2010 05:20:08 GMT

Hi,I am not able to access my any database whenever i try to access i get this message.Error msg:-Database 4 Cannot Be Autostarted During Server Shutdown Or Startup. (Microsoft SQL Server, Error: 904).Please help.Remarks.1.SQLserver.exe is running.2.No trace flag is used.3.Database status is ONline in(sys.databases and databasepropertyex(status)).

Can't delete the Maintenanceplans

ravisamigo — Mon, 02 Jul 2012 00:13:49 GMT

Hi,I'm not able to delete the maintenance plans.When I'm trying to delete the maintenance plans,facing the below error."A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)"When I'm trying to delete the job,facing the below error.The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_schedule_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'schedule_id'.The statement has been terminated.The statement has been terminated. (Microsoft SQL Server, Error: 547)Please advise.Thanks and Regards,Ravi.