January 7, 2009 at 3:15 am
Hi all,
I restore database two days before... Now i not able to open my database when viewing it says Cannot open the Database in the Sql Server Management Studio the database was highlighted as "Suspect".
After that i have copied that MDF and LDF Files to another machine and tried to Attach the Files...I got some error
anyone can help me to solve this problem....
January 7, 2009 at 3:22 am
There should be an entry in the SQL error log saying why the database has been marked suspect. Can you look through the error log and see if there's anything that mentions that database? Post the messages here if you're not sure what they mean.
Got a recent backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 4:20 am
UnFortunately i have that Deleted that Database..but right now i am having that .mdf and .ldf files. I tried to attached that .mdf files but i got some error says not able to attach that files
January 7, 2009 at 4:52 am
Post the exact error message, and read this blog post. http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx
For what it's worth, you should never detach a suspect or corrupt database, because it will not reattach.
Do you have a backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 5:52 am
This is the Error which i got during attach the .mdf File
Location: IndexDataSet.cpp:12109
Expression: retCode == INSERT_SUCCESSFUL
SPID: 56
Process ID: 4024
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test'. CREATE DATABASE is aborted.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
January 7, 2009 at 5:56 am
Thank you for your reply...
Can i know the Exact cause of this Error. so, i may aware of this and not repeat the same error in future
January 7, 2009 at 6:14 am
Thanks For your Reply,
I Finally got the cause of this problem with the help of your Guidance.
January 7, 2009 at 6:20 am
venkatg86 (1/7/2009)
Can i know the Exact cause of this Error. so, i may aware of this and not repeat the same error in future
No idea. The message you posted doesn't have enough info. It's just a generic 'Something went wrong' error.
Can you find the error log on the server that this DB was originally on (where it went suspect) and post any errors in that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 3:05 am
If you have just one .mdf file you can use sp_attach_single_file and just use the one .mdf fille which will built your .ldf file. If you have multiple .mdf files and the attach does not work the only way is to restore it from your backup. You can set your database in ALTER DATABASE [dbname] set emergency mode and then attach using sp_attach_single_file in one go.
February 4, 2009 at 3:11 am
varun_3g (2/4/2009)
If you have just one .mdf file you can use sp_attach_single_file and just use the one .mdf fille which will built your .ldf file. If you have multiple .mdf files and the attach does not work the only way is to restore it from your backup. You can set your database in ALTER DATABASE [dbname] set emergency mode and then attach using sp_attach_single_file in one go.
No. If the database was not cleanly shut down then sp_attach_single_file_db or CREATE DATABASE ... FOR ATTACH will *NOT* create a new log file for you.
You cannot set a database into EMERGENCY mode unless it is already attached - so your last sentence is also incorrect.
Please do not post advice unless you are sure that it works for the problem posted in the thread (and is correct).
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
February 5, 2009 at 8:57 pm
Which is exactly the advice given in a post further up... although its great that you're posting advice, it would be more useful to post on threads that haven't *already* been answered, rather than posting the same advice again.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply