May 20, 2010 at 11:50 am
I have a database with 11 files on 10 different file groups. The vendor "ran a script with the default parameters" during an upgarde and now I have 21 files on 10 different file groups. Basically, the existing names didn't match their script, so it created new files with new names and copied all the data over into them. The problem is the data is still in the original files, so now my db is twice the size. I need to delete the original files, but I can't because there is data in them. I'm trying to figure out how to delete the data from fileid 2-11 so that I can delete them from the database. The only thing I can find is the dbcc shrinkfile with the emptyfile option, but this will migrate the data to the new file, which we don't want because it will duplicate all the data.
I've considered detaching the database, removing the files and reattaching, but I'm worried that may corrupt it. Any ideas are welcome and appreciated!
May 20, 2010 at 3:34 pm
Personally since they broke it, I'd get the vendor in and tell them to fix it.
I'm also trying to work out how they managed to double up your data. Tables are built on File Groups, not on files, and you can't have 2 tables with the same name. So they must have created a new schema or a whole new set of tables. In which case it should be possible to identify and truncate or drop the old/wrong tables. This won't necessarily empty the files, but it will halve your data, then you can run the SHRINKFILE with EMPTYFILE option and the drop the files.
You definitely DON'T want to detach and delete files because you can't actually see which file the data is in, only the FILE GROUP. Also the database will come up SUSPECT if SQL can't find all the files it expects to find.
Leo
Striving to provide a better service
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 21, 2010 at 5:01 am
All I know is that the vendor didn't have rights to see the file groups, so when he got the error in his script saying that it already existed, he ignored it and that is what started the problem. I don't know enough of the schema to figure out exactly what the impact is, except that I now have two files (different names) in each filegroup with the same amount of data in them, and 1% free disk space, which is what tipped me off something was wrong.
After talking to the guy yesterday, and having him talk to his superiors, I convinced them we needed to let me restore and they needed to redo the conversion. This blew away some things, but now at least database is correct. They agreed this was the best way to handle it. I'm still wondering if there was anything I could do though. Everything I found basically said restore and do over.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply