May 1, 2012 at 12:13 pm
And it's on a low-performant drive. Is there any salvaging this or do I need to do a restore? I don't know how soon it's going to cause issues but it clearly will, since the data is striped across this new file. I intended to put it on a different filegroup but it ended up on primary.
May 1, 2012 at 12:25 pm
aurato (5/1/2012)
And it's on a low-performant drive. Is there any salvaging this or do I need to do a restore? I don't know how soon it's going to cause issues but it clearly will, since the data is striped across this new file. I intended to put it on a different filegroup but it ended up on primary.
You should be able to render the offended file empty, then remove it by using:
DBCC SHRINKFILE EMPTYFILE
ALTER DATABASE REMOVE FILE
Please, carefully read what these commands do and test on a non-production database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 1, 2012 at 12:56 pm
PaulB-TheOneAndOnly (5/1/2012)
aurato (5/1/2012)
And it's on a low-performant drive. Is there any salvaging this or do I need to do a restore? I don't know how soon it's going to cause issues but it clearly will, since the data is striped across this new file. I intended to put it on a different filegroup but it ended up on primary.You should be able to render the offended file empty, then remove it by using:
DBCC SHRINKFILE EMPTYFILE
ALTER DATABASE REMOVE FILE
Please, carefully read what these commands do and test on a non-production database.
I'll read into them. My intuition is that what your suggestion SHOULD take the data from the one file, move it to another file on the filegroup, and then delete that file.
Is this a correct first intuition? I'll be doing this after production hours with a MS helpdesk person on the phone.
May 1, 2012 at 1:43 pm
aurato (5/1/2012)
PaulB-TheOneAndOnly (5/1/2012)
aurato (5/1/2012)
And it's on a low-performant drive. Is there any salvaging this or do I need to do a restore? I don't know how soon it's going to cause issues but it clearly will, since the data is striped across this new file. I intended to put it on a different filegroup but it ended up on primary.You should be able to render the offended file empty, then remove it by using:
DBCC SHRINKFILE EMPTYFILE
ALTER DATABASE REMOVE FILE
Please, carefully read what these commands do and test on a non-production database.
I'll read into them. My intuition is that what your suggestion SHOULD take the data from the one file, move it to another file on the filegroup, and then delete that file.
Is this a correct first intuition? I'll be doing this after production hours with a MS helpdesk person on the phone.
You are correct. The idea is to move the data away from the offending datafile to other datafile in the same filegroup then remove the un-wanted - now empty - datafile.
I strongly suggest to test it - that will ensure you are familiar with the commands and the returning messages, that you have a sound script and make the production implementation smoother and predictable.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 1, 2012 at 1:52 pm
PaulB-TheOneAndOnly (5/1/2012)
aurato (5/1/2012)
PaulB-TheOneAndOnly (5/1/2012)
aurato (5/1/2012)
And it's on a low-performant drive. Is there any salvaging this or do I need to do a restore? I don't know how soon it's going to cause issues but it clearly will, since the data is striped across this new file. I intended to put it on a different filegroup but it ended up on primary.You should be able to render the offended file empty, then remove it by using:
DBCC SHRINKFILE EMPTYFILE
ALTER DATABASE REMOVE FILE
Please, carefully read what these commands do and test on a non-production database.
I'll read into them. My intuition is that what your suggestion SHOULD take the data from the one file, move it to another file on the filegroup, and then delete that file.
Is this a correct first intuition? I'll be doing this after production hours with a MS helpdesk person on the phone.
You are correct. The idea is to move the data away from the offending datafile to other datafile in the same filegroup then remove the un-wanted - now empty - datafile.
I strongly suggest to test it - that will ensure you are familiar with the commands and the returning messages, that you have a sound script and make the production implementation smoother and predictable.
I'll do this and then post my script here if you wouldn't mind reviewing it? Can't hurt to have more eyes.
May 1, 2012 at 1:55 pm
My method of testing this on a test server will be this:
1) Add file to primary filegroup of test version of the same database.
2) Add a new table with some dummy data to the primary filegroup.
3) Run DBCC command w/ EMPTYFILE on the new file and delete it (assuming this needs to be done all in one command?)
4) Check to see that, with the new file deleted, my dummy data is still alive and well on the other file of the primary filegroup.
May 1, 2012 at 2:07 pm
I ran this:
USE PRICETOOL --Name of database in question
GO
DBCC SHRINKFILE (DummyFile, EMPTYFILE)
GO
ALTER DATABASE PRICETOOL
REMOVE FILE DummyFile
GO
I received this as output:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
74384 384 0 0
The data is still in my dummy table and the command:
select * from sys.database_Files
does not have DummyFile in the result set. It took about a minute. Should I expect it to take longer on a longer file?
May 1, 2012 at 3:04 pm
Perfect!
Only the dbcc command may take longer on production, elapsed time is going to be a function of how much data is already sitting on the offending datafile.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply