Repairing a db from suspect mode

Hello,

I have a SQL Server 2005 database in Suspect mode , as a try to recover , I 
did the following

EXEC sp_resetstatus 'Bluestar3';
ALTER DATABASE Bluestar3 SET EMERGENCY

Database state is now in Emergency Mode , so this worked, then I did that

ALTER DATABASE Bluestar3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Bluestar3', REPAIR_ALLOW_DATA_LOSS)

I received this error
********************************************
Msg 8921, Level 16, State 1, Line 2
Check terminated. A failure was detected while collecting facts. Possibly 
tempdb out of space or a system table is inconsistent. Check previous 
errors.
Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: torn page 
(expected signature: 0xaaaaaaaa; actual signature: 0x55555aaa). It occurred 
during a read of page (1:16783) in database ID 5 at offset 0x0000000831e000 
in file 'c:\Program Files\Microsoft SQL 
Server\MSSQL.1\MSSQL\DATA\Bluestar3_Data.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.
********************************************

What else I can do ?

Thank you
Bassam 

0
Bassam
8/21/2010 11:11:28 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
2890 Views

Similar Articles

[PageSpeed] 30

Bassam (egbas@yahoo.com) writes:
> I have a SQL Server 2005 database in Suspect mode , as a try to recover
> , I did the following 
> 
> EXEC sp_resetstatus 'Bluestar3';
> ALTER DATABASE Bluestar3 SET EMERGENCY
> 
> Database state is now in Emergency Mode , so this worked, then I did that
> 
> ALTER DATABASE Bluestar3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> DBCC CheckDB ('Bluestar3', REPAIR_ALLOW_DATA_LOSS)
> 
> I received this error
> ********************************************
> Msg 8921, Level 16, State 1, Line 2
> Check terminated. A failure was detected while collecting facts. Possibly 
> tempdb out of space or a system table is inconsistent. Check previous 
> errors.
> Msg 824, Level 24, State 2, Line 2
> SQL Server detected a logical consistency-based I/O error: torn page 
> (expected signature: 0xaaaaaaaa; actual signature: 0x55555aaa). It
> occurred during a read of page (1:16783) in database ID 5 at offset  
> 0x0000000831e000 
> in file 'c:\Program Files\Microsoft SQL 
> Server\MSSQL.1\MSSQL\DATA\Bluestar3_Data.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 .
> ********************************************
> 
> What else I can do ?

If you have a clean backup, restore it.

If not, you can try to build a new database from scripts and bulk out
as much data as you can, but you need to consider that there may
gross inconsistencies in the database. And the errors above may come
and some parts of the data may not be accessible at all, at least through
regular methods.

Do you have more databases on this server? Errors like these are often 
due to hardware issues, so this may not be the only bad database on
the server.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/21/2010 12:20:22 PM
It is a POS program running in a shop and using SQL Server Express, only 2 
user databases reside in the instance , all the tables are accessible 
normally using SELECT except one table which is producing all those errors I 
guess, electricity is not stable in that place as it is a mall still under 
construction so a power off can happen suddenly , guess that was the reason 
of the problem ,  is there a method to tell the server to reconstruct pages 
of that table only and neglect or destroy all corrupted pages it finds ?

Thank you
Bassam


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DDB91DEDDA63Yazorman@127.0.0.1...
> Bassam (egbas@yahoo.com) writes:
>> I have a SQL Server 2005 database in Suspect mode , as a try to recover
>> , I did the following
>>
>> EXEC sp_resetstatus 'Bluestar3';
>> ALTER DATABASE Bluestar3 SET EMERGENCY
>>
>> Database state is now in Emergency Mode , so this worked, then I did that
>>
>> ALTER DATABASE Bluestar3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> DBCC CheckDB ('Bluestar3', REPAIR_ALLOW_DATA_LOSS)
>>
>> I received this error
>> ********************************************
>> Msg 8921, Level 16, State 1, Line 2
>> Check terminated. A failure was detected while collecting facts. Possibly
>> tempdb out of space or a system table is inconsistent. Check previous
>> errors.
>> Msg 824, Level 24, State 2, Line 2
>> SQL Server detected a logical consistency-based I/O error: torn page
>> (expected signature: 0xaaaaaaaa; actual signature: 0x55555aaa). It
>> occurred during a read of page (1:16783) in database ID 5 at offset
>> 0x0000000831e000
>> in file 'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\Bluestar3_Data.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 .
>> ********************************************
>>
>> What else I can do ?
>
> If you have a clean backup, restore it.
>
> If not, you can try to build a new database from scripts and bulk out
> as much data as you can, but you need to consider that there may
> gross inconsistencies in the database. And the errors above may come
> and some parts of the data may not be accessible at all, at least through
> regular methods.
>
> Do you have more databases on this server? Errors like these are often
> due to hardware issues, so this may not be the only bad database on
> the server.
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
0
Bassam
8/21/2010 1:23:01 PM
Bassam (egbas@yahoo.com) writes:
> It is a POS program running in a shop and using SQL Server Express, only
> 2 user databases reside in the instance , all the tables are accessible 
> normally using SELECT except one table which is producing all those
> errors I guess, electricity is not stable in that place as it is a mall
> still under construction so a power off can happen suddenly , guess that
> was the reason of the problem ,  is there a method to tell the server to
> reconstruct pages of that table only and neglect or destroy all
> corrupted pages it finds ? 
 
Certainly not any simple and documented way. If DBCC gives up, I guess the
situation is quite dire. Dropping the table may be easiest way out. Maybe
you can access row by row to save as much you can. Or do it manually with
DBCC PAGE, but that would be labourous.

If you really need the data, you could open a case with Microsoft. Be
prepared to pay through the nose.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/21/2010 3:45:48 PM
Reply:

Similar Artilces:

Is there any way to zero out WSUS db, we are not using it.
SBS 2003 R2 We have not used and we will not use WSUS. Is there any way we can zero out its huge db file to release space? thanks jack Which version are you using R2 WSUS 3.0? in WSUS 3.0 you can use the clean up tool But It's been too long since I've even used R2 (WSUS 2.0) There is the option to uninstall it? However I think WSUS is great, and if you use WSUS 3.0 I think you will like it also Russ -- Russell Grover - SBITS.Biz [SBS-MVP] Microsoft Gold Certified Partner Microsoft Certified Small Business Specialist 24hr SBS Remote Support - http://www.SB...

no sync in cache mode with additional mailbox
Hi all, Using outlook2007 sp2 on xpsp3, with Exchange2007 SP1. We use outlook in cache mode and that works well with a single mailbox in the outlook profile. However, when users also load a second, general, mailbox to their outlook profile the sync with Exchange stops. In OWA the user can see new mail in the inbox on the primary mailbox but they dont show in Outlook. Also sent emails appear to be stuck in the outbox folder in OWA but they do get sent properly. When we turn Cache mode off, online mode, sync starts again. Also when in cache mode, when we remove the se...

Safe Mode Problem
Perhaps my previous message didn't explain clear enough itself. But I need some help and would like to further explain the problem again. My system with Windows XP Pro SP-2 was/is working perfectly except that it won't boot into safe mode. The system is booting up smoothly in regular mode but when I try to boot it into safe mode, then it loads some drivers and started to reboot again and again until I choose to start windows in normal boot. This sounds strange as my understanding is that boot up problem normally due to conflict of drivers, software and/or hardware. So if a mach...

Unknown repair
I have recently moved from Excel 2002 to 2007 and when saving a large file with several macros and reopening it keeps saying that there is unreadable content and that a repair had to be made. I don't know how to get rid of this problem and I can see no external links to '.xml' files. Any help gratefully received. Repaired Records: External formula reference from /xl/externalLinks/externalLink8.xml part (Cached values from external formula reference) Repair log: <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> - ...

different execution between (F5) and (Ctrl +F5) in release mode
hi, I have a multi thread program, I executes correctly in debug mode, In release mode, when I execute it (Ctrl+F5), It is ok. But when I use "Go" (F5), Its executes differently. Any idea about the problem ? thanks, Behzad Hello, During debugging timing (time spent on function calling/execution) is different, this will affect threading execution time too. Normally this should not affect program itself, if synchronization is OK. So I could suggest you have problems with thread/access synchronization. Igor Green, Grig Software. www.grigsoft.com Compare It! + Synchronize It! : fi...

Open One DB From Another?
I'd like to be able to open one database which would run an autoexec macro to open a "utility" database to do a few standard initialization things and have that db open the main database which contains my actual app. The main (second) db is secured. Is this possible? I just reread my first post. Too hastily written, I think. Let me try again. I want to have a utility database which does some standard initialization stuff. I would open this utility database first, it would do some initialization things (probably from within an autoexec macro) then it would open the seco...

Outlook repair
I have Office 2000 on my computer and the Outlook application ahs been damaged when a virus invaded my machine. As a result, I am not able to use the email portion of Office which would be very helpful to me. As an alternate, I have been using Netscape. The Office SW was loaded on my computer by my previous employer. When I left that company, I requested that I be given my computer and the related SW. I have a signed note from the CEO transferring all the hardware and SW to me. However, I do not have the CD to reload the Office SW to repair the damage portions. Is there a way t...

Inbox Repair Tool (Almost) ALWAYS Finds Errors #2
This message was originally posted on 1/26/04, but received no response. I'm running Outlook 2003 (11.5608.5703) under WinXP SP1. My Outlook.pst file is a local "Personal Folders File (97-2002)," 127 MB after a compress. The Office 2003 installation was an upgrade from Office 2000. Shortly after installation, I ran the Inbox Repair Tool, more or less out of curiosity. It found and repaired errors. I ran it again a few days later, and again it found errors. In fact, almost every time I run it, it finds errors. Is this normal behavior? For the record, I do NOT abruptly power...

Best DB access method
Hi, I would like to write an application which allows users : - to select a DB type (MS SQL, Oracle, ...) - to create DB and users accounts however, i would like to know what is the best method to do that. 1. we can ask to user to create an ODBC system DSN and after in application let user to setup the DSN. 2. directly into application to create a particular module which will allows users to insert all data regarding connection string to DB. Maybe it exists some other solutions but i do not know. Please could you let me know how to make it as much as flexible and professional ? keeping i...

Evaluation Mode?
I have my RMS at work, which is running fine...I also installed RMS at home, to fool around with, the time has expried, would I have any issues if I took the dongle from work for a day brought it home, plugged it into the home computer and took it back to work say the next day, a few hours after business opened? -- Thank You Vince :) This is a multi-part message in MIME format. ------=_NextPart_000_049B_01C6A11C.CF095790 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Vince, Yes, that would work, but a better option; Make a backup ...

Start DB when not connected to network
Scenario: Currently calling the function SynchronizeDBs in the On Open Event of the Start-up form. The function is called out as follows: =SynchronizeDBs("C:\Documents and Settings\...\IDB HubR1.mdb","\\ww007\...IDB Hub.mdb",1) The function code is: Function SynchronizeDBs(strDBName As String, strSyncTargetDB As String, _ intSync As Integer) Dim dbs As DAO.Database Set dbs = DBEngine(0).OpenDatabase(strDBName) Select Case intSync Case 1 'Synchronize replicas (bidirectional exchange). dbs.Synchronize strSyncTargetDB, dbRepImpExpChanges Case 2 'Synch...

Need help retrieving data from an Oracle db
I need to create a button in Excel that when clicked will query an Oracle database using a value stored in cell E1 as a restriction in the query. Also I need the results of the query to be listed in a range of cells in the workbook on the same sheet the button exists. example: If cell E1 = 10, then I need a button that when pressed runs a query across an Oracle database; Select name, number from Panel where number = '10' I'm expecting 30 to 200 names to be returned. I would like these names from the oracle db to be populated in cells IT4 - It 300. Thanks, Dan --- ~~ Messag...

compact and repair through a VB code
Hi, I have a probem that my database canot be compacted and repaired while it's being used by users,so i'm trying to write the below code on the on time event to do compact and repair at 2:30 am when all users had already closed the application,but i get a message that says that i cannot compact and repair my database through a VB code or through a macro,instead i should go to toos>dtabase utilities...ecc. do suggest something? If Time() > "2:30:00 AM" And Time() < "2:30:06 AM" Then DoCmd.RunCommand acCmdCompactDatabase End If U�ytkownik "...

Pivot Table Mode Function
Hi I have generated a pivot table using data from an Access Query. I' able to generate an Average column in my pivot table but want to b able to calculate a mode. I haven't found this option within the pivo table and was hoping someone could point me in the right direction o explain how to generate this. Many thanks - Davi -- David Gibson ...

Synchronizing Item DB between POS and backoffice
In my backoffice i use a copy of my POS item database. I mainly use it to print mass labels and to process my online orders. This database is not connected in any way to the POS database. What I would like to do is to synchronize the items table between POS and backoffice. I just would like to get all new or updated items from the POS maybe once a week, or whenever i know i made changes to the POS items table. Is there an easy way to get this done? I don't want to just restore an entire backup as I would loose my online order payment processing. Both systems are on RMS 2.0 latest s...

New Exchange DB
We use Exchange 2003 SP2, we have moved many users to a new Database, but some users can't connect for a long period, when they login it crashes after a minute or less. The effected users all use Outlook 2003 SP2. This is the error: Faulting application outlook.exe, version 11.0.8000.0, stamp 43599e06, faulting module ntdll.dll, version 5.1.2600.2180, stamp 411096b4, debug? 0, fault address 0x0000eddc. If I move the users that have the problem back to the old database they are fine. There are 100's of user that are fine on this new database though. Should be restart the se...

Integration DB Size
Our integration database is growing at a very rapid pace (about 200 MB per day) ... compared to the pace that our GP & CRM databases are growing. Is there some way to "shrink" the Integration DB? -- Brandon ================== Presentations Direct - http://www.presentationsdirect.com > Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp > Laminators http://www.presentationsdirect.com/laminators/gbc-laminators.asp > Paper Shredders http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp...

Repair Install..
When I built my current computer, the one I'm using to post this message, I bought duplicate parts and built a second computer. For ease of discussion, the original computer is #1 and the second computer is #2. I have cloned the two hard drives from #1 and put them into #2. It works but there are some hardware differences between #1 and #2 so #2 wants to be authenticated, etc but I don't want to do that. I want to be able to use both computers. If I buy Microsoft Windows XP Home Edition SP3 OEM can I use that disk to basically install that version onto hard drive #2 w...

using extended desktop mode
How can I move an Excel spreadsheet onto my second monitor. Word and Internet provide no problems, I just click & drag. Same way. You have to have the application window in a non-maximised state though, and you can then drag it from one to the other, or even size it so it spreads across the two screens completely. If I have the window maximised though, I am unable to drag it from screen to screen. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�-------------------------...

Repairing One File That SFC Could Not Repair
Question: How do I get a file not repaired by System File Checker from a Vista DVD and put it back on the Vista tree? Cannot find the file on the DVD. Background: I had rare problem with Vista (clean installed 3 months ago, after 28 months of the original installation working). My hard drive is probably nearing life end, but there are only a few bad sectors (and only because HP Pavilion notebooks are NOTORIOUS for overheating and reducing drive life). System Restore did not work: it stopped for hours at the "initializing" phase at EVERY restore point, and...

repair excel appl. for euro zone?
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C58FB5.D2930470 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I bought an excel appl. which is only useful in UK or USA.=20 It doesn't work properly with an other country setting (decimal = problems). The sellers didn't tell me this in advance. Who can make such an appl. useful for Euro-zone? Dirk ------=_NextPart_000_000A_01C58FB5.D2930470 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYP...

Outlook 2000 and freebusy in workgroup mode?
Reading the following article makes me wonder how to use the freebusy option in corporate/workgroup mode: http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;196484&Product=3D= out2K "OL2000: (IMO) How to Use the Internet Free/Busy Feature ..... NOTE: These procedures only apply if you have installed Outlook with the Internet Mail Only option."=20 How does then Exchange get the freebusy times? Anyone any ideas? Timo I found the solution myself. You have to setup an "internet email" service, otherwise freebusy does not work. Strange behaviour.... Timotheus ...

Execute Procedure on a remote Access Db file
I am trying to open another Access DB from my current Access DB and trying to execute a SUB programatically. But I get a message that Microsoft Office Access can't find macro "ParseMnAFileAGH" message. Any help on this is much appriciated. Here is my code: SUB RunMacro() dim objAccess as Access.Application Set objAccess=CreateObject("Access.Application") objAccess.OpenCurrentDatabase ("C:\MnAAH.mdb") objAccess.docmd.RunMacro "ParseMnAFileAH" objAccess.DoCmd.Minimize Set objAccess = Nothing End sub "Kumar" <Kumar@disc...

open and repair
i have an excel 2000 workbook that when you try to open it it says it i corrupt .. the fix i got was to go to file, open from the drop down menu on th open button ,select open and repair. I can only see, open , open rea only , open as copy . what am i doing wrong .thank -- Message posted from http://www.ExcelForum.com Hi philthedil! Open and Repair option was only introduced from Excel 2002 Here's a resource post on recovery of corrupted files. I've checked out all the links referred to. Additional comments and suggested additions are welcome. Some of the most frantic and de...

How to Repair
How can I "Repair" the install of CRM Outlook? When I go to add/remove programs, there isn't a "repair" option. -- Brandon ================== Presentations Direct - Binding Machines, Laminators and Paper Shredders http://www.presentationsdirect.com ================== I got the "repair" option to come up (group policy issue), but now the repair process is giving me an error saying that the source folder that holds the Microsoft.CRM.MetadataHelper.dll file needs "read access" for the "everyone" group . . . but it does have read a...