October 23, 2008 at 8:01 am
Dear All,
One of my database is in Emergency mode. I want to take it to online now.
I fired the command "Alter database DBNAME Aet Online". But this is not working.
Am getting the following error. Kindly guide me what to do..?
Msg 5173, Level 16, State 1, Line 3
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.
If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'DB_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 945, Level 14, State 2, Line 3
Database 'DB' 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 3
ALTER DATABASE statement failed.
Thansk in advance.
Santhu..
October 24, 2008 at 5:54 am
did you touch any of the mdf or log files while db was offline?
October 24, 2008 at 6:05 am
Why was the DB in emergency mode and what exactly did you do to it, from the beginning, step by step.
Can you please also look at the SQL error log and post any and all errors that you find in there that relate to that database.
Do you have a backup of this database?
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
October 27, 2008 at 12:00 am
The problem was that I was not able to take backup properly.
Backup size was shown as 80MB even though the actual size was 2.6GB (It dint give any error while taking or restoring backup).
I tried to detach the database but it was not allowing and was giving error.
Since the language version is GERMAN, I couldn't understand what exactly the error message was.
So I stopped the SQL server and copied MDF file to local server using FTP.
After executing the "sp_attach_single_file_db" procedure, I ran the following commands as I was getting some errors.
EXEC ('ALTER DATABASE [DB] SET EMERGENCY');
EXEC sp_dboption 'DB', 'single user', 'TRUE';
DBCC checkdb ('DB', repair_allow_data_loss);
EXEC sp_dboption 'DB', 'single user', 'FALSE';
This took databse to EMERGENCY. I checked the data and found that everything is uptodate.
Then I tried to take it to ONLINE, but was getting the mentioned error.
Thanks,
Santhu..
October 27, 2008 at 12:41 am
What error did the initial detach give? (error code's fine. I can use that to get the english version)
What error did the attach_single_file give?
Do you still have the mdf and ldf on the server? If so, copy them both to the local server and try to attach the database using both of them.
What's in the SQL error log relating to this DB?
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
October 27, 2008 at 1:03 am
I will get those details at the earliest. Thank you.
(MDF and LDF are still there..but LDF is huge..can't import)
For time being whot I have done is, I EXPORTed all the tables from EMERGENCY databse to another temporary database. Also I copied all the SPs. So now data is available at least.
Thanks,
Santhu.
October 27, 2008 at 1:12 am
nairsanthu1977 (10/27/2008)
For time being whot I have done is, I EXPORTed all the tables from EMERGENCY databse to another temporary database. Also I copied all the SPs. So now data is available at least.
If you have everything working there, then I suggest you forget the errors and carry on using that. From what I can see from the errors, the only way you'd be able to fix things is to use that original log and data file to reattach. If you can't do that, then exporting all the data is about the only way to proceed.
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
October 27, 2008 at 3:16 am
Thank you so much.
I won't leave it like that. Will definetly see wot's wrong with that database. (backup is not properly happening, can't detatch..). I will revert back with all the informations.
Yeah, now data is at our disposal...so things are not stopped....will continue with that for timebeing.
October 27, 2008 at 5:02 am
Backup size was shown as 80MB even though the actual size was 2.6GB (It dint give any error while taking or restoring backup).
A bilt late now, but... although the actual database size was 2.6gb, are you sure the database didn't contain only 80mb of data... you mentioned that the log was huge... how much of the 2.6gb was the mdf file, and how much was the ldf file?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply