March 20, 2002 at 4:17 am
I have been asked to try and recover a sql 7 database from the following state
Primary Filegroup filled up
Database went suspect
Server rebooted
Database Detached
Physical Log file deleted
Database will not reattach (with single file) due to 'Device activation error. The physical file name <path> may be incorrect.
Any ideas anyone (other than to restore from the last backup)
thanks
Steven
March 20, 2002 at 4:56 am
Should work unless there was more than once log file.
Andy
March 20, 2002 at 4:58 am
Can you tell me about the database files. How many files for the DB data and how many was there for the log. Any other things going on with the server in regards to the database?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 5:04 am
Only one file for each the mdf and ldf.
The mdf is only around 200mb and the log file was over 10gb.
They are restoring the last backup for the time being (inorder to get them working).
Shame they deleted the log file otherwise I could have used the sp_add_log_file_recover_suspect_db command
Steven
March 20, 2002 at 5:09 am
Forgot to say. There is a large turnover of data loaded into that database (as you can see from the size of the log file).
Not a lot else on that server. I think IIS may be on it, and two other databases which are not currently used.
Steven
March 20, 2002 at 5:32 am
Just restored the database, and found the mdf has a max file size set (which it was close to)
That explains one thing (I couldn't quite understand why a 10gb log file would fill up a disk with 50gb free space.
I wonder if the database cannot be reattached since the mdf filled up. Records were written to the transaction log, which then the database went suspect. If that was the case it could have been recovered with the sp_add_data_file_recover_suspect_db, but since the log file was deleted it cannot rollback etc. What do you think?
Steven
March 20, 2002 at 7:23 am
Forget my last statement. I think you were alone the right lines about multiple log files.
I have tried to attach the db to a test sql2000 box and noticed the 'Device activation error' appears twice for the log files, where there was only one log file on the database.
Looking at the database sysfiles and sysfiles1 tables (from the last backup) only one log file exists. Are there any other places where the logfile locations are held eg master..sysdatabases holds the location of each databases mdf
Steven
March 20, 2002 at 7:49 am
Not to my knowledge, however are you sure there was not a secound added sometime after the last backup and that it could not have had soemthing happen to it (or you orphaned because the name made no sense). You can open the DB file up in notepad to check for LDF but ou cannot search for it, usually shows up fairly quick but a large file may not open. But when you attached to 2000 and it barked twice it should have told you the names missing anyway. Maybe you missed a log file. But once lost this way, I have not found a way to reattach.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 8:03 am
Its also possible that someone added a log file, when the 'administrator' was trying to find out why the filegroup was full. Since they rebooted the server lots of times to see if they would fix the suspect database (hence cycled all the sql logs in the process) then deleted the ldf prior to trying to attach the mdf I can't say for sure.
I'll take a look at the file in notepad as you suggested. Otherwise if they want there data back I think a call to MS would be in order.
Thanks for all you help
Regards
Steven
Steven
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply