September 8, 2009 at 7:58 am
We created a test file group in our production database along with a test file NDF in that filegroup. This was on a new, unused LUN on our NetApp device. The goal was to test migrating a copy of a table to this new file. The LUN was removed or deactivated during an attempt to make it larger since it was too small initially for the project. Now we have a filegroup and file showing in the database but the actual physical disk space is unavailable. What's the best way to deal with this. Try to reactivate the LUN in the hopes the file is still there so this can be done properly?
September 8, 2009 at 8:04 am
Wow, that's a tough one. I'd try to turn the LUN back on. If that doesn't work, is the database suspect? I might try to drop the file and add it back (in a valid location).
September 8, 2009 at 8:18 am
can't remove the file because the M mapped drive to that space is not there. I don't know yet if the file is still on that LUN and just not reachable by sql. Operating error 21 the device is not ready Database doesn't show as suspect but last night's differential failed so we need to correct it.
September 8, 2009 at 8:25 am
I would try and put the lun back, that would be the 1st thing.
Is the database still online and usable. I would also check the sql log and event logs and see if anything has been logged.
To be honest, would have been better doing this on test environment first, unless this has already been done on test.
if the database is critical, you might think about doing a backup if that is possible. you could always try and move the file back into the primary filegroup.
another thing to think about is, alter database and remove the new filegroup, you will have to try and remove the file first.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 8:42 am
We have backups but definitely would rather not go down that route. I'm waiting to see if our Systems guys can bring the LUn back up. Now if they do and restore the M mapping and the ndf file has been deleted, can I copy any NDF file in there and name it accordingly? Long enough to remove it. No tables or anything else were ever migrated to the file, it was just a new filegroup with one new empty ndf file.
September 8, 2009 at 8:46 am
Indianrock (9/8/2009)
We have backups but definitely would rather not go down that route. I'm waiting to see if our Systems guys can bring the LUn back up. Now if they do and restore the M mapping and the ndf file has been deleted, can I copy any NDF file in there and name it accordingly? Long enough to remove it. No tables or anything else were ever migrated to the file, it was just a new filegroup with one new empty ndf file.
You can try it, you might get away from it. if the m: drive comes back and the .ndf is gone. then you might be able to use alter database to remove the file
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 9:13 am
I don't think you can copy any ndf in there. Was there data on the file?
you could try restoring the backup to a new db, then shutting it down and copying THAT ndf to the LUN. Or even to a new "M:" drive mapped anywhere. Then ALTER database to move it to where you need it.
September 8, 2009 at 10:01 am
no data in the file I'll try restoring the weekend full backup somewhere else in an effort to get a copy of the test ndf file.
September 8, 2009 at 10:45 am
did you try putting any ndf file in there and renaming the file, it might save you a lot of time.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 11:46 am
I'm waiting for our Systems team to reactivate the M drive to this LUN, which they can't do without restarting the cluster service, so tonight. It does seem that the ndf file is still there though.
September 9, 2009 at 6:09 am
Systems reactivated the LUN and the drive letter to it. But since the ndf file was in the root of that instead of in the correct subfolder, the database was basically unavailable.
So I copied the ndf down into it's proper folder and restarted sql server, all is well now. Log that to the "learned something from that" book.
September 9, 2009 at 7:05 am
Indianrock (9/9/2009)
Systems reactivated the LUN and the drive letter to it. But since the ndf file was in the root of that instead of in the correct subfolder, the database was basically unavailable.So I copied the ndf down into it's proper folder and restarted sql server, all is well now. Log that to the "learned something from that" book.
nice to know you got it sorted and yep defintionally one to remember
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 9:56 am
So lessons: 1) remove file and filegroup from database before doing anything that makes the lun/drive unavailable
2) if you fail at #1 the database appears to remain available but complains about device not ready
3) if you then make the drive/lun available again, but don't put the missing data file in EXACTLY the place the DB expects it, the database becomes unusable !!!
Our Systems guy had reactivated the LUN/drive, but placed the missing NDF data file in the root of the mapped drive instead of in the subfolder where it should have gone.
Bottom line is I should have stayed up till midnight last night to work with him since he doesn't know Sql Server.
( or insisted he do his work earlier in the evening )
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply