March 26, 2010 at 12:30 pm
Hello friends,
I have a SQL 2005 database which is build with 4 data files and 2 log files.
the data is not distribute same on all data files. One data file size his 45GB while others small than 25GB.
I like to reorganize the data in the data files so all data will have more or less the same size.
Should I use the shrink file to do that? which options should I use with that shrink file option?
Thank,
Miki
March 26, 2010 at 1:30 pm
are all files in the same filegroup?
---------------------------------------------------------------------
March 26, 2010 at 1:42 pm
I really don't see what the point would be. It's not really best practice to split files based ono size of the file, but instead, on the IO requirements.
When you set up SQL for multiple data or log files, you do so based primarily on the IO requirements.
e.g.
You have a 400GB database. You have three tables, two tables are 187.5GB each. The third table is 25GB. In this example, table 3 has 100 transactions/sec. For tables 1 and 2, it's 5.
In this case, you would put table three in a secondary file group on a seperate drive. This despite the fact that Drive #1 contains 375 GB and drive 2 conatins 25 GB.
We try to do our load balancing in SQL on IO (keeping in mind things like disk speed (are they 10 or 15k rpm drives, RAID-5 or RAID-10, etc...), not so much on just trying to keep physical size the same.
Before you try anything, you need to run some traces and determine how to spplit the data. And, it goes without saying, if your drives are logically partitions of the same disk, it serves almost no point
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
March 26, 2010 at 1:51 pm
if the files are spread across separate drives you will get increased throughput. To spread the load evenly between the drives the files would ideally be the same size as SQL spreads the io on a proportional fill basis.
If the drives are raided you can also see an improvement on the same drive.
We need to know what the OP is trying to acheive to see if he really need filegroups with multiple files or secondary filegroups.
and also whether that second logfile is really needed.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply