September 17, 2007 at 2:06 am
Hi,
I have SQL 2005 database in suspect state. By my opinion it is damaged because of hard disk error. I’m trying to set the database in such kind of state so, that I can read undamaged data from it.
First I was tried with EXEC sp_resetstatus 'PIS';
and I was got the following message:
The suspect flag on the database "PIS" is already reset.
Then I was tried to use DBCC checkdb('PIS')
but I was got the message:
Msg 926, Level 14, State 1, Line 1
Database 'PIS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Then I was tried to set database in emergency mode with Alter Database PIS Set Emergency
but I was got the following error again :
Msg 823, Level 24, State 2, Line 1
The operating system returned error 38(Dosežen je bil konec datoteke (EOF).) to SQL Server during a read at offset 0x0000000c0b8000 in file 'c:\DATA\DATA\MITIC1.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.
The database is now not suspected but in the Management Studio I see just database without any database objects.
Likewise I was tried to move database to another SQL server so, that I was restore database from older backup on the another SQL server. Then I was stopped this server and original server then I was copied original files (I have 3 files and 1 log file) over new restored files and then I was started new SQL server. The database becomes suspected with all above problems.
Does anybody has ideas or tricks how to correct this database so, that I can read good data from it?
Regards
September 18, 2007 at 12:26 am
Hi
Not sure I understand your last paragraph:
"I was tried to move database to another SQL server so, that I was restore database from older backup on the another SQL server"
I read this to mean that you have attempted to copy your database to a new server as a preliminary to restoring an old backup.
You can restore backups to a new SQL server without moving the (probably) corrupt database to that server. Copy the backup file to the new server then restore. Or restore the backup to a new database name on your current server.
Apologies if I mis-understood...
September 18, 2007 at 12:54 am
Yes. I wasn’t enough clear in the last paragraph. The corrupted database was on the laptop. Just with such kind of trick, I can moved it to my stationary PC. Now I have suspected database in the PC and I want to get good data from it, if at all is possible.
September 18, 2007 at 11:20 am
Well, you have at least one corrupt page in the database that's being hit during recovery. This means you can't access the database unless you put it in emergency mode (using ALTER DATABASE PIS SET EMERGENCY). You can then poke about an extract data BUT it will be transactionally inconsistent as recovery has not been run. If you don't have any valid backups, or they're too old, the only way to fixup the database is to use emergency mode repair. See my old blog post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx - I'm going to update it with more instructions on my new blog.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 19, 2007 at 1:31 am
Thank you for suggestion. But I have no luck. When I try to put database in emergency mode, I get the error as I was wrote in the first message. If then I try to run
dbcc checkdb ('pis', REPAIR_ALLOW_DATA_LOSS)
I get the error:
Msg 922, Level 14, State 1, Line 1
Database 'PIS' is being recovered. Waiting until recovery is finished.
Regards
September 19, 2007 at 11:49 am
Can you do the following and let me know the output you get?
ALTER DATABASE PIS SET ONLINE -- this should try to run recovery, fail and put the database into SUSPECT or RECOVERY_PENDING
GO
ALTER DATABASE PIS SET EMERGENCY -- this should work after the step above
GO
You'll then need to put the database into SINGLE_USER mode as well before running repair. If this stuff doesn't work, can you post the results from:
SELECT state_desc FROM sys.databases WHERE name='PIS'
Btw - if this is a time-critical problem, I advise you to contact Product Support to help you immediately rather than waiting for forum responses.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 28, 2009 at 4:16 am
Hello Guys,
My SQL 2005 MSDB database is put in SUSPECT mode, that has lot number of jobs and Sprocs. I dont have any backup of MSDB.
I have tried the following Steps/methods to bring it back to Online, but didn't work on this System Database:
1. Stopped SQL service> copied the MSDB data files into a different drive> restarted Service> Tried SP_Attach_Db to attach these files as a new database. But it failed saying MSDB has been put under Suspect.
2. Tried Sp_Reset_Status 'MSDB' , though it executed successfully..MSDB didnt turn Online either.(It seems this method is for SQL 2000, Correct me If I am wrong).
3. Altering the Database to Emergency mode so that I can execute DBCC CHECKDB ('MSDB', REPAIR_ALLOW_DATA_LOSS), then error said that MSDB can't be set to Emergency Mode
Can some one please tell me, if I can turn my MSDB Online back. I cannot afford losing data by restoring/ re-creating as a new MSDB. I don't have any backup of this MSDB or the data of MSDB as scripts , so if i recreate a new MSDB... there is no way of creating all those jobs/Sprocs from scratch.
Thanks,
[font="Comic Sans MS"]Vampire[/font]
--In 'thoughts'...
Lonely Rogue
April 28, 2009 at 7:23 pm
Well, first off you probably are going to lose some data from the database being suspect - that's what backups are for. You've probably already realized this, but if you can't afford to lose data from msdb, this should be the wake-up call to start backing up all yoru system databases.
The procedure you want is described in this blog post: TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database
Let us know when you got it working.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 29, 2009 at 3:07 am
Thanks for the reference link. However, will that method work even for System Databases ( in my case its MSDB).
I am asking you this because as said in the reference, the System DB can't be set into Emergency Mode...then how to proceed?
Fixing the Database
If you don't have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I'm going to choose to repair the database using emergency-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort for a detailed description of this tool.
ALTER DATABASE DemoSuspect SET EMERGENCY;
GO
ALTER DATABASE DemoSuspect SET SINGLE_USER;
GO
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Thanks,
[font="Comic Sans MS"]Vampire[/font]
--In 'thoughts'...
Lonely Rogue
April 29, 2009 at 8:34 am
Hmm - that's right - msdb can't be put into emergency mode using the ALTER DATABASE syntax. To get it to work you'll need to update the system tables manually - no easy task, involving booting the server in single-user mode. I haven't tried this on 2005 so I can't say if it'll work or not - maybe I'll play around with it today - but that's the only way you'll be able to get into that msdb database.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 1, 2009 at 6:12 am
Hey Paul,
I was able to restore my MSDB jobs 😀 without creating a new MSDB.
Here is what I have done, probably help them lying in the dark struggling with this kind of issue:
Problem :
My SQL 2005 MSDB database was put into SUSPECT, bcoz of abrupt server boot, that has many Sprocs and Jobs. I have no backup of MSDB, hence there is no way of restoring the backup to re-create MSDB.
Solution:
1. Stop the SQL service > Copy the corrupt MSDB MDF data file (only MDF ) from its location onto a different drive/ location > Start the SQL service > Execute the below SQL query to create a User DB with this available MDF file
CREATE DATABASE User_MSDB ON
(NAME=DFT_MSDB , FILENAME='D:\NonSQLDefaultLocation\msdbdata.mdf') FOR ATTACH_REBUILD_LOG
This will create a User database with all the database files @ 'D:\NonSQLDefaultLocation\'
2. Now, take a backup of this User_MSDB database
BACKUP DATABASE User_MSDB TO DISK='D:\NonSQLDefaultLocation\User_MSDB.Bak'
3. Restore the MSDB database with the backup of User_MSDB , the one we have taken in the 2nd step.
RESTORE DATABASE MSDB
FROM DISK='D:\NonSQLDefaultLocation\DFT_MSDB.BAK'
WITH MOVE'MSDBDATA' TO 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSDBDATA.MDF',
MOVE'MSDBLOG' TO 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSDBLOG.LDF',REPLACE,RECOVERY
Whoa...you are back with your MSDB data.
--In 'thoughts'...
Lonely Rogue
May 1, 2009 at 6:29 am
Hello "INFO01", I was following this post and was curious how things r going with ur corrupted Database. Thanks
May 1, 2009 at 7:15 am
Neat - but ATTACH_REBUILD_LOG only works if the databases wasn't cleanly shutdown - so your msdb must have been. I wonder why it thought it was suspect then - suspect means that recovery started but couldn't finish? Hmmm. Thanks for following up.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 4, 2009 at 10:47 pm
But, it seems theres been some post-restoration issues of this System Database. After I restored MSDB...one of my job is failing with the below error:
Message
[LOG] Step 1 of job 'DataImport' (0x01B59EE6D7841D459A251D07291F784A) cannot be run because the ActiveScripting subsystem failed to load. The job has been suspended
However, I have found a post corresponding to this issue @ http://blogs.infosupport.com/blogs/bertrand/archive/2007/11/14/Restoring-MSDB-across-instances_2C00_-a-bad-idea.aspx
Please post if anyone has found the above described issue or something different after restoring MSDB.
--In 'thoughts'...
Lonely Rogue
May 19, 2009 at 11:42 am
Hi,
I have the similar problem. One of my live databases were suspected and we are using SQL 2008. I have tried to recover the database but when I use the statement "EXEC sp_resetstatus " I got an Warning "You must recover this database prior to access"
Then when I use "ALTER DATABASE SET EMERGENCY" got error "ALTER DATABASE statement failed"
Can anyone please help me to recover the suspected database in SQL 2008?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply