Database File Size & Break-Up

Question

Post reply

Database File Size & Break-Up

Gautam Saha

SSC Journeyman

Points: 86
More actions
January 9, 2009 at 2:43 pm

#198123

I'm trying to manage the database file size(s) for a Vendor app.
Currently the database has 2 files Production.mdf & Production.ndf which are 75GB each, and on separate logical drives (on a SAN)
Can I break up the database so that it consists of 4 mdf/ndf files
(I just read that there is no difference, only a matter of choice (?) between mdf & ndf) of say 37.5GB each ? How would I do that ?
If I add 2 more files say Production2.mdf & Production3.mdf on the PRIMARY filegroup (initialize them to 75GB), my expectation is that data will now be written to the new files also but I do not have available so many large logical drives.
Any help will be appreciated.
Thanks,
Gautam

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Gautam Saha (1/9/2009)
Can I break up the database so that it consists of 4 mdf/ndf files

You can. What are you trying to achieve by doing so?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gautam Saha SSC Journeyman Points: 86 More actions · Answer 2

Space management (several drives are empty whereas 2 are almost full) & Potentially increasing performance. I know the performance plus is highly debated but from what I read it seems having more files should increase concurrent I/O across 4 files and increase performance.

I'm aware of the caveat that logical partitioning in terms of breaking up tables should be done in different files only if we we know which data will be accessed in parallel. This is a vendor app so I don't have much leeway in that aspect.

But initially this same database was one big mdf and creating a secondary ndf on the PRIMARY group on a different drive helped at least with space management, I cannot vouch that it alone improved perfromance but I was very new to the DBA realm at that time and it seemed to have helped.

Thanks for your input.

GilaMonster (1/9/2009)
Gautam Saha (1/9/2009)
Can I break up the database so that it consists of 4 mdf/ndf files
You can. What are you trying to achieve by doing so?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Gautam Saha (1/9/2009)
Space management (several drives are empty whereas 2 are almost full) & Potentially increasing performance. I know the performance plus is highly debated but from what I read it seems having more files should increase concurrent I/O across 4 files and increase performance.

Ok.

For space management, I would suggest you create another filegroup and put one or two files in that. Then you can specifically control what tables are where. If all the files are in Primary, SQL will just allocate in the emptiest file.

As for performance, you will only gain by doing this if you currently are experiencing an IO bottleneck, and the new files that you create are on separate physical devices, and SQL can access the data in parallel.

It's not the normal situation that splitting files massively improves performance

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gautam Saha SSC Journeyman Points: 86 More actions · Answer 4

Thanks.

I'm still not sure how to split up an existing 75GB file into

two 37.5GB files on the same (or different) filegroup.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Look up alter database in Books Online. There's commands to add a file and also to add a filegroup. Or you can do it from the files tab of the database properties in Management studio (Object explorer)

If you add a second filegroup, you can move tables into it by rebuilding the clustered index on the new filegroup.

CREATE INDEX ... WITH DROP_EXISTING ON New_FileGroup_Name

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

Gail has the best suggestion for this. Be aware you may have locks on the tables as you move them, so pick a lot activity time.

Gautam Saha SSC Journeyman Points: 86 More actions · Answer 7

Thanks I'll give that a try. Also found that the DBCC Shrinkfile with the EMPTYFILE option

is supposed to move the contents of a file to all the other available files in a filegroup.

GilaMonster (1/11/2009)
Look up alter database in Books Online. There's commands to add a file and also to add a filegroup. Or you can do it from the files tab of the database properties in Management studio (Object explorer)
If you add a second filegroup, you can move tables into it by rebuilding the clustered index on the new filegroup.
CREATE INDEX ... WITH DROP_EXISTING ON New_FileGroup_Name

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Gautam Saha (1/11/2009)
Thanks I'll give that a try. Also found that the DBCC Shrinkfile with the EMPTYFILE option
is supposed to move the contents of a file to all the other available files in a filegroup.

Yup, the intention with that is that the emptied file will be removed right after that operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 9

Gautam Saha (1/10/2009)
Thanks.
I'm still not sure how to split up an existing 75GB file into
two 37.5GB files on the same (or different) filegroup.

Here's a real life example for a database. I went along the same route Gail suggested and created a secondary file group. I find a lot of times that databases that have really grown haven't had many of their tables archived and they just grow and grow. If you have a table or tables like that, create an archive table on the secondary filegroup/location and migrate the data over in manageable chunks (e.g. by date if there is a entry date column).

Some caveats:

1. In my case, that particular column, did not have an index on it, clustered or otherwise (I didn't design it, so not my fault... 🙂 ) I had to create a non-clustered index on that column. Now if space is tight, you can do what I did, I created the index on the secondary instead of the main data directory.

2. Once I verified that the index was indeed used (you never know, but check on a small sample), I did an insert into the new archive table from the large table. Once you confirm the data is there, then I deleted from the old table using the same criteria I did for the insert.

3. Once you're done, drop the index.

In our case, this is going to be a database that will eventually be retired, so there was no need to keep the index and it won't grow that big again before it is switched off.

Hope this helps.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein