SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Database corrupted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pupo
Starting Member

3 Posts

Posted - 12/07/2009 :  14:33:52  Show Profile  Reply with Quote
Hi guys,

I am new to sqldatabase. I am having a problem to open my database. I am not sure what happened to it, but below is error message when I am trying to repair it from this codes.

ALTER DATABASE db1 SET EMERGENCY;
GO
DBCC CHECKDB (db1, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE db1 SET SINGLE_USER;
GO
DBCC CHECKDB (db1, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

Error messages;
Msg 945, Level 14, State 2, Line 2
Database 'db1' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 824, Level 24, State 6, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x58063006; actual: 0x8271ce1b). It occurred during a read of page (1:0) in database ID 5 at offset 0000000000000000 in file 'C:\db1.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 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: invalid encryption key. It occurred during a read of page (2:0) in database ID 5 at offset 0000000000000000 in file 'C:\db1.LDF'. 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.


Can any experts please give me advise? I have been spending so much time fixing it, but i am going no where. I also don't have a backup for this database.

Thank you so much,

Paul

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/08/2009 :  03:33:19  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Why no backup?

First things first. What's in the SQL error log regarding this database?
If you just run ALTER DATABASE db1 SET EMERGENCY; (with nothing else), what does it do?

One step at a time.

Also, does this database have any encryption? (column level or transparent database encryption?)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

pupo
Starting Member

3 Posts

Posted - 12/08/2009 :  10:20:37  Show Profile  Reply with Quote
quote:
Originally posted by GilaMonster

Why no backup?

First things first. What's in the SQL error log regarding this database?
If you just run ALTER DATABASE db1 SET EMERGENCY; (with nothing else), what does it do?

One step at a time.

Also, does this database have any encryption? (column level or transparent database encryption?)

--
Gail Shaw
SQL Server MVP



Hi Gail,


Thank you for your reply. About the backup, I am really new with sql and I didn't know how and why I need to backup until now. I will try to schedule the backup from now on. :)

I went into SQL error log, but I could not find any more error other than it showed on my previous post.

After I set this database into Emergency, the status was staying as Emergency for a while then changed status to Recovery_Pending. Even though the database was in Emergency status, I still cannot manage to retrieve any data from this database by using Sql management studio.

About encryption, I don't think there is any encryption on this database, because I have never set it up.

I am wondering whether it would be easier just to retrieve only all scripts in database. I would like to get only scripts for tables, views, and stored procedures.


Please help.

Thank you.

Edited by - pupo on 12/08/2009 10:38:28
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/08/2009 :  13:10:47  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Post the SQL error log. I want to see exactly what it says. What's the current state of that database? (query sys.databases for the column state_desc)

My gut feeling, from the errors that you've posted, is that this is completely irreparable and there's no way that we'll be able to get anything back at all. It'll be an all or nothing. Structure and (most) data or nothing at all.

quote:
I will try to schedule the backup from now on. :)

I hope you'll do more than try.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 12/08/2009 :  15:05:34  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by GilaMonster


quote:
I will try to schedule the backup from now on. :)

I hope you'll do more than try.

--
Gail Shaw
SQL Server MVP



No kidding. pupo, if you're responsible for the database, then making sure that backups are occurring is your most important task. Everything else is secondary.

I walked into a client shop one time as they were having performance issues that they couldn't resolve. Within 15 minutes of arriving I found out that they also hadn't been able to take a backup for several weeks! I said "Ok, let's talk about performance later and start talking about the backups." That got their attention. Especially after I asked what the impact to the business would be if the SQL Servers went away...
Go to Top of Page

pupo
Starting Member

3 Posts

Posted - 12/09/2009 :  09:47:22  Show Profile  Reply with Quote
Thank you guys for your comment about the backup. I took you guys advise and setup a daily backup for my database yesterday.:)

Hi Gail, Below is the error log on this database. The status of my database is showing "Recovery_Pending"

error log:

12/06/2009 22:50:49,spid54,Unknown,fcb::close-flush: Operating system error (null) encountered.
12/06/2009 22:50:49,spid54,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
12/06/2009 22:50:49,spid54,Unknown,fcb::close-flush: Operating system error (null) encountered.
12/06/2009 22:50:49,spid54,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
12/06/2009 22:50:42,spid52,Unknown,The operating system returned error 1006(The volume for a file has been externally altered so that the opened file is no longer valid.) to SQL Server during a read at offset 0x00000009c32000 in file 'C:\db1.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<c/> see SQL Server Books Online.
12/06/2009 22:50:42,spid52,Unknown,Error: 823<c/> Severity: 24<c/> State: 2.
12/06/2009 22:48:49,spid52,Unknown,The operating system returned error 1006(The volume for a file has been externally altered so that the opened file is no longer valid.) to SQL Server during a read at offset 0x00000000162000 in file 'C:\db1.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<c/> see SQL Server Books Online.
12/06/2009 22:48:49,spid52,Unknown,Error: 823<c/> Severity: 24<c/> State: 2.

Edited by - pupo on 12/09/2009 09:50:15
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/09/2009 :  14:23:39  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Interesting. Is this database on a local drive or a SAN?

Please stop the SQL service and restart it. Once it has restarted, post the error log and check the status of the database again.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

suretalu
Starting Member

2 Posts

Posted - 12/18/2009 :  12:28:54  Show Profile  Reply with Quote
the data can be also restored with the best mssql recovery program, it is here: http://www.recoverytoolbox.com/microsoft_tool_for_ms_sql_recovery_for_sql_server_2000_corrupt_database.html.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/21/2009 :  07:27:03  Show Profile  Reply with Quote
Interesting. With a total history of 2 posts on this website, you manage to "solve" this OP's problem with a redirection to another website....without an explaination of "why" this redirection is of value. Why do I smell some advertising?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/21/2009 :  10:20:33  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
I must admit, I'm getting tired of all this advertising for 'data recovery' tools that appears in every single thread on data corruption.
I'd like to see some proof that they actually work.


--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000