February 19, 2009 at 11:06 am
This is only currently a hypothetical question, but im curious as to how to do this correctly.
Lets say you have a database with 2 log files and 4 data files. What are the procedures for:
1.deleting one of the log files
2.deleting one of the data files, ensuring the data is placed in a file not marked for deletion.
for the log file, i assume you would take a full system backup, then a log backup then a log backup with log truncate. you would then go into DB properties and remove the entry for the file.
you would then stop the sqlservice and delete the file.
is this correct?
for deleting a data file i imagine i would alter any objects such as tables , ensuring that they are moved to a filegroup on the not containing the extra file. id then do all the relivant backups, remove the file in the db properties , stop the sql service, delete the file and restart the service.
am i incorrect in this assumption?
Its not something that affects me yet, but i feel some of our databases are not optimally configured and may need some tinkering of this sort in the future.
February 19, 2009 at 11:14 am
I think on the log file you could simplify that a lot by just issuing the right Alter Database commands. Of course you do a backup before issuing them, but I think SQL Server will manage the rest of it for you.
On the data file, you could go through and move any indexes (including clustered indexes) out of the file, pretty easily. It's a rebuild command for each one. Then, again, I think it's a matter of issuing the right Alter Database command. Also after a backup, of course.
What you can't do this way is get rid of the primary mdf file. I don't think you can get rid of that and keep the database functional. But the secondary ndf files should be okay.
What I'd do if I had to do something like this would be to create a proof-of-concept database with a number of log and data files, make sure they were in use (could be a very small amount of data and log entries, so long as there's something in every file), and then try removing files and keeping the database online and functional.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 11:31 am
are all the datafiles in separate filegroups?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2009 at 11:36 am
You can't get rid of the primary log file nor the primary data file.
For the log just back it up and issue the appropriate alter database statement
For the data move the data and/or indexes out "empty" the file and issue the appropriate alter database to remove it.
Hope this helps
* Noel
February 20, 2009 at 3:00 am
is there a need to backup the tempdb really?
February 20, 2009 at 7:07 am
winston Smith (2/20/2009)
is there a need to backup the tempdb really?
Nope.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 9:00 am
winston Smith (2/20/2009)
is there a need to backup the tempdb really?
Why would you want to do that ?
* Noel
February 20, 2009 at 6:43 pm
You can't backup the tempdb database.
MJ
February 21, 2009 at 1:54 pm
deleting one of the data files, ensuring the data is placed in a file not marked for deletion.
If you want to remove one or more files with in a file group then you can use
dbcc shrinkfile(datafile,emptyfile)
this will transfer the data from the file "datafile" to another file in the same filegroup....U can now use alter database command to remove the file from the database
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 21, 2009 at 2:55 pm
You may try something like this,
DBCC SHRINKFILE(myLogicalFile, EMPTYFILE)
ALTER DATABASE myDB REMOVE FILE myLogicalFile
April 27, 2009 at 3:59 pm
I have successfully removed 1 data file where there were multiple data files within the same Primary File Group.
I inherited a DB that has 2 mdf's and 2 ldf's on the same Primary File Group, same exact drive and path. Worthless imo as far as performance goes. My guess is that at some point the DB ran out of drive space, they created a 2nd at a diff locale, and eventually placed both back on the same drive at a later time, when space permitted.
At any rate, I did this in our test enviroment. The original mdf was 18 GB and set to NO Growth. As was it's LDF. The 2nd mdf and ldf had 10% autogrowths, and the 2nd mdf size was about 57 GB, both had the default _data/log_1 extensions.
I'm not sure if this had to be done in this manner, but I seemed to be syntactically steered into the direction of emptying the LARGER ldf and mdf into the SMALLER..because the smaller versions were the originals? I don't know and perhaps I didn't give much of a fight for the opposite scenario. If anyone can confirm whether it should have been possible to empty in either direction, that would be most helpful.
Maybe the one mdf had ALL the system tables? If so, perhaps you can't empty that? Just thinking out loud.
I would like to perform the same process in Prod, taking the DB offline of course, however we only have a 4 hour maintenance window, and Test took upwards of 8 hours :-(.
From what I've read, it sounds like I may have certain tables that have data across both mdf's? Just double checking that his is 1) Possible 2) Identifiable? - Is there a dmv or some sp that can tell a db admin that a table in question has some data on the same Primary File Group, but different FileIDs?
That would be very helpful.
Any thoughts, this is a good thread by the way and helped me quite a bit with my original EmptyFile on my Test DB
R
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply