November 20, 2009 at 6:43 am
Hi,
I have a client who is running a production DB ABC around 250 GB and I found that
the DB has 3 datafiles. This is a database on SQL SERVER 2005 running a Microsoft ERP.
Scenario
The files which I mentioned above is as follows:
1) ABC.mdf size 500 MB
2) ABC_1.ndf size 120 GB
3) ABC_2.ndf size 80 GB
4 ABC_3.ndf size 50 GB
I found that all these files belong to the same filegroup.
Now they have bought a storage having 24 HDD of 146 GB each.
Now the problem or rather a question.
From above I could analyze that someone has made these extra datafile without any knowledge as to why they should be doing it. What I would like to do is merge all these datafiles into one big file (ABC_1.ndf file because the ABC.mdf file cannot be tampered with ) and then create different filegroups on seperate allocated RAID assigned disks from the storage (for e.g 4 x 146 GB HDD configured as RAID 1+0) and move the heavily accessed transaction tables into it. And then move master tables into different RAID partition....and so on.
What steps I need to take to accomplish the same ?
What precautionary measures do I need to take before doing the same ?
Approximately how much time it would take me to accomplish the exercise?
(Sometime back I had emptied one datafile around 50 GB to a 100 GB datafile and it took almost 2 days to finish ...Can there be a simpler way to do this because I cannot afford more than 3-4 hours downtime)
Can someone enlighten me as to how do I go about it.
The server config is HP DL580 G5 with 4 Quad core procs and with 32 GB RAM with 6 internal 146 GB HDD.
Your comments and valuable inputs will be appreciated.
Regards,
Prashant
November 20, 2009 at 7:04 am
Edit: Sorry, wrong thread....
November 20, 2009 at 7:42 am
pdevadiga (11/20/2009)
Hi,I have a client who is running a production DB ABC around 250 GB and I found that
the DB has 3 datafiles. This is a database on SQL SERVER 2005 running a Microsoft ERP.
Scenario
The files which I mentioned above is as follows:
1) ABC.mdf size 500 MB
2) ABC_1.ndf size 120 GB
3) ABC_2.ndf size 80 GB
4 ABC_3.ndf size 50 GB
I found that all these files belong to the same filegroup.
Now they have bought a storage having 24 HDD of 146 GB each.
This is understood...
Now the problem or rather a question.
From above I could analyze that someone has made these extra datafile without any knowledge as to why they should be doing it.
Why do you you think that someone added additional datafiles without knowledge? It may have been deliberately created, for administering purpose or for performance, so that each datafile can be placed in a separate drive increasing Disk I/O access. (Don't know if your guy had actually placed them on different drives or not)
What I would like to do is merge all these datafiles into one big file (ABC_1.ndf file because the ABC.mdf file cannot be tampered with ) and then create different filegroups on seperate allocated RAID assigned disks from the storage (for e.g 4 x 146 GB HDD configured as RAID 1+0) and move the heavily accessed transaction tables into it. And then move master tables into different RAID partition....and so on.
Why would you like to merge those files into one single file? As mentioned already, it would be better if you have more datafiles and Filegroups (more better on different Drives) rather than everything in one single file and drive.
Then needs a bit more clarification why you want to merge that I can understand what's your goal.
What steps I need to take to accomplish the same ?
What precautionary measures do I need to take before doing the same ?
Approximately how much time it would take me to accomplish the exercise?
(Sometime back I had emptied one datafile around 50 GB to a 100 GB datafile and it took almost 2 days to finish ...Can there be a simpler way to do this because I cannot afford more than 3-4 hours downtime)
Can someone enlighten me as to how do I go about it.
The server config is HP DL580 G5 with 4 Quad core procs and with 32 GB RAM with 6 internal 146 GB HDD.
Your comments and valuable inputs will be appreciated.
Regards,
Prashant
My game plan would be
Step 1
Create a new database on this server and plan all the tables to spread across your new datafiles and filegroups etc.. in this process I would explore the option of a table partitioning if needed. I cannot suggest here in your example, I am not sure of the tables and the # of records in them.
Step 2
Then use SSIS Packages to move data from the production Database to the newly created database. If planned properly, you can test it and see how quick the data in the entire database can be copied over.
Step 3
Plan for a scheduled downtime and then copy the data (either complete data or move the most of the data the previous night and then the delta in this downtime).
Detach the existing (old) database and rename the (new) database to the old one and test the application.
Of course doing is far more difficult than just suggesting, but much planning needs to done to enable smooth transition.
Hope to see a different approaches from some other peers here in this site.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 1:39 am
Thanx for the response Bru, the reason for which I needed to merge these files into one big file was that I wanted to place the tables across filegroups which have their own disk space rather than spanning over mulitple filegroups. For e.g. I would place the master tables in one filegroup, transaction tables on another, ledger tables on another and so on. Your idea of partitioning the data is perfectly right but alas I cannot do it with this database as I do not know how the application in my case Navision would respond to it. Any other application I would have definitely done the same.
Looks like this is going to be a nightmare:w00t:...so what would I do is simply place these different sized ndf files on seperate set of disks...at least I can expereince some performance boos:cool:t.
Tell me something if I have 3 such ndf files (100 GB, 80 GB, 50 GB ) and I have placed them on different drives. How would SQL use these files in rotation? :w00t:Would these 3 files ever catch up in terms of space occupied. I mean will I get to see all 3 files of the same size going ahead in due course of time.
Any inputs would be really appreciated.
Regards,
Prashant
December 2, 2009 at 8:16 am
pdevadiga (12/2/2009)
Looks like this is going to be a nightmare:w00t:...so what would I do is simply place these different sized ndf files on seperate set of disks...at least I can expereince some performance boos:cool:t.
Yes you will have a performance boost due to different disks being read., instead of one drive.
Tell me something if I have 3 such ndf files (100 GB, 80 GB, 50 GB ) and I have placed them on different drives. How would SQL use these files in rotation? :w00t:Would these 3 files ever catch up in terms of space occupied. I mean will I get to see all 3 files of the same size going ahead in due course of time.
You will not see all of them at the same size in future unless you change their autogrowth settings.
For Example. If all the 3 files have Autogrowth of 2 GB. SQL Server uses proportional fill algorithm, and fills the file1 to 102, then moves to file2 is grown till 82 and the 3rd to 52 and next again back to file1 to 104 and so on...
However, if you wanted to make them of the same size, you have to do some workout.
Disable the Autogrowth of file1 set the max size of file2 as 100 GB and wait till file3 to grows to 100 GB at that point, enable file the autogrowth on file1 and remove max size limit to file2.
All 3 of them will be 102, 102 and 102, then 104, 104, 104....
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 11:57 pm
Thanx Bru...
Regards,
Prashant
December 3, 2009 at 5:21 am
Welcome ! 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply