September 12, 2006 at 7:24 am
I currently have a SQL Server database that is around 60 GB with the data split over 2 data files. The primary data file is 5 GB in size and is set not to grow any more. The rest of the data is located on the second data file and is about 55 GB. I want to split this large file into seperate 5 GB files.
The way I am planning on doing this is creating 12 new 5 GB files and running the command DBCC SHRINKFILE('FILENAME',EMPTYFILE) once this is done I would delete this file and be left with just the individual 5GB files. My questions are, has anyone used this technique before? Is there a better way to accomplish this?
September 12, 2006 at 9:09 am
I normally go for the approach of moving all of my indexes into a seperate filegroup first. (if this is on a different array defrags are faster)
then move text objects to a different file (and move that to a new array)
next step is then to move commonly used tables like lookups into a diff file group.
finally move any remaning large tables into their own filegroup (although this doesn't make much difference to anything)
the only reason i do it this way is you have more control over performance than allowing SQL to use it's proportional fill method.(for example reindexing a large table will hit the index file and large table file, but other tables shouldn't be affected much)
MVDBA
September 13, 2006 at 3:06 am
Mike has good suggestions!
I would come with a follow up question; why would you like to create these files of 'only' 5 GB? If you want to spread fles over RAID I cannot see this 5 GB limit.
And if you just want smaller files on the same drive I do not see any advantage (only disadvantage due to possible extra file fragmentation).
Hanslindgren
September 13, 2006 at 3:53 am
there is a dubious piece of information somewhere on microsoft's site stating that for "optimal" performance a database should have as many files as there are CPU in the machine as a thread can be issued for each file write.
they also say this about tempdb - but i've no idea how much of an improvement this will give (if any)
MVDBA
September 13, 2006 at 4:01 am
That is true, I have read the same somewhere in BOL.
But if I recall correctly, that info concerned earlier versions then SQL Server 2000 and from my testing it would seem that many reader/writer threads on the same 'spindle(s)' cause contention (I.e. the movement of the drive(s) head(s) becomes the limiting factor and not the raw read/write speed) that offsets any multi-thread performance gain...
Hans Lindgren
P.S Anyone! Please divulge any additional (and/or corrective) information you might have!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply