August 12, 2014 at 11:48 pm
[font="Comic Sans MS"]Hi, I am stuck in a situation where I am getting these errors.
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140726_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA3B\CDW_F_MOBILE_INSTRU_FACT_20140607_4_OLD_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140719_4_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA3B\CDW_F_MOBILE_INSTRU_FACT_20140531_OLD_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140712_4_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140705_4_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140628_4_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140621_4_OLD.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'CDW' 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 1
ALTER DATABASE statement failed.
When I am seeing the state_desc it said it is in "RECOVERY_PENDING", and I've checked the LUN's as well but there isn't any reference of this file anywhere, and I am not sure if someone deleted this or not but I cannot locate this file.
Now, I need to recover this db quickly to make it operational. Could you'll pls. assist me in this with the right solution.
This is what I am getting from the error log:
During upgrade, database raised exception 945, severity 14, state 2, address 0000000000CB03C1. Use the exception number to determine the cause.
Regards,
Faisal
[/font]
August 13, 2014 at 12:19 am
Quick questions
1. How much free space is on the P: drive?
2. Has the content of P:\Mnt\DATA4B\ changed?
3. Do you have a file backup of P:\Mnt\DATA4B\ ?
😎
August 13, 2014 at 12:39 am
You need to either find that file (P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140705_4_OLD.ndf) or restore a backup of the database.
The file, whatever it is, is a critical part of the database. Probably part of the PRIMARY filegroup. The DB won't come online without that file.
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
August 13, 2014 at 1:34 am
[font="Comic Sans MS"]The free space is not pretty much as to accommodate another db as the db is around 5 TB. Secondly, I don't have a file backup available
Is there a way to resolve this issue, as I don't have the physical files anymore it has been completely deleted from the system.
[/font]
August 13, 2014 at 1:36 am
GilaMonster (8/13/2014)
You need to either find that file (P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140705_4_OLD.ndf) or restore a backup of the database.The file, whatever it is, is a critical part of the database. Probably part of the PRIMARY filegroup. The DB won't come online without that file.
[font="Comic Sans MS"]Thanks! Gail. However, can I do a detach, and possibly while attaching it back remove those file entries. At least if you can give me a work around apart from restore.
Regards,
Faisal
[/font]
August 13, 2014 at 1:40 am
faisalfarouqi (8/13/2014)
[font="Comic Sans MS"]The free space is not pretty much as to accommodate another db as the db is around 5 TB. Secondly, I don't have a file backup availableIs there a way to resolve this issue, as I don't have the physical files anymore it has been completely deleted from the system.
[/font]
A low-level file recovery might be an option, depends an the volume's activity after the delete.
😎
August 13, 2014 at 1:45 am
Eirikur Eiriksson (8/13/2014)
faisalfarouqi (8/13/2014)
[font="Comic Sans MS"]The free space is not pretty much as to accommodate another db as the db is around 5 TB. Secondly, I don't have a file backup availableIs there a way to resolve this issue, as I don't have the physical files anymore it has been completely deleted from the system.
[/font]
A low-level file recovery might be an option, depends an the volume's activity after the delete.
😎
[font="Comic Sans MS"]I can't go with that option...could you provide something else as an alternative.[/font]
August 13, 2014 at 2:12 am
faisalfarouqi (8/13/2014)
However, can I do a detach, and possibly while attaching it back remove those file entries.
You can, and the attach will fail.
You need to find that file or restore from backup.
I'm not giving you those options because they're the only ones I can think of. I'm giving you those options because they're the only applicable ones.
At least if you can give me a work around apart from restore.
could you provide something else as an alternative.
Find or recover the file, or restore from your last good 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
August 13, 2014 at 2:24 am
faisalfarouqi (8/13/2014)
Eirikur Eiriksson (8/13/2014)
faisalfarouqi (8/13/2014)
[font="Comic Sans MS"]The free space is not pretty much as to accommodate another db as the db is around 5 TB. Secondly, I don't have a file backup availableIs there a way to resolve this issue, as I don't have the physical files anymore it has been completely deleted from the system.
[/font]
A low-level file recovery might be an option, depends an the volume's activity after the delete.
😎
[font="Comic Sans MS"]I can't go with that option...could you provide something else as an alternative.[/font]
Not certain what to say, you have a database with eight files missing,
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140726_OLD.ndf
P:\Mnt\DATA3B\CDW_F_MOBILE_INSTRU_FACT_20140607_4_OLD_OLD.ndf
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140719_4_OLD.ndf
P:\Mnt\DATA3B\CDW_F_MOBILE_INSTRU_FACT_20140531_OLD_OLD.ndf
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140712_4_OLD.ndf
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140705_4_OLD.ndf
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140628_4_OLD.ndf
P:\Mnt\DATA4B\CDW_F_MOBILE_INSTRU_FACT_20140621_4_OLD.ndf
I would be working on restoration from the last good backup by now.
😎
August 13, 2014 at 2:31 am
GilaMonster (8/13/2014)
faisalfarouqi (8/13/2014)
However, can I do a detach, and possibly while attaching it back remove those file entries.You can, and the attach will fail.
You need to find that file or restore from backup.
I'm not giving you those options because they're the only ones I can think of. I'm giving you those options because they're the only applicable ones.
At least if you can give me a work around apart from restore.
could you provide something else as an alternative.
Find or recover the file, or restore from your last good backup
[font="Comic Sans MS"]What if I delete the file entry from sys.master_files..will that help[/font]
August 13, 2014 at 2:40 am
faisalfarouqi (8/13/2014)
What if I delete the file entry from sys.master_files..will that help
The system tables cannot be edited. Edit: And even if they could, the DB is not online, so you wouldn't be able to get at the system tables which underlie sys.database_files to change them. Not to mention that, since those files have to be part of PRIMARY to be giving this kind of errors, the missing files will include parts of the system tables, so the DB doesn't have a chance in hell of coming online without them.
Get out your latest backup and restore it.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply