primary and secondary filegroup

Last Post 02 Feb 2011 05:26 AM by rm. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ctseah
New Member
New Member

--
25 Jan 2011 12:59 AM
Hi,

I have a database with a primary mdf as big as 190GB.
There is no secondary filegroup defined yet.

I thought of creating a secondary filegroup to balance up the 190GB into 2 filegroups.

Any suggestion how to go about it ?

Thanks in advance

regards
rm
New Member
New Member

--
25 Jan 2011 05:13 AM
You like to have 2 file groups or just 2 files in primary file group? How many disks does the server have for db data files? What kind of storage you use on the server? Number of processores on the server? After adding file in primary group, you can balance file size by rebuild all indices but sql2k5 doesn't handle blob properly. After adding another file group, you need move table to new file group. May need multiple files in a group to reduce file size if there's big table in the db.
ctseah
New Member
New Member

--
25 Jan 2011 04:47 PM
Hi,

The disks are on the SAN box.
There are 3 to 4 CPUs

What is the best practise ? The idea is that in case of backup/recovery, it may be faster to have multiple files instead of 1 big mdf file. And perhaps having multiple files the performance may be better. This mdf may be growing non-stop, currently is 190GB, but may reach 500GB within 2 years.

regards
ctseah
New Member
New Member

--
25 Jan 2011 11:44 PM
Hi rm,

I will like to consider having multiple files in primary file group. Currently I only have 1 mdf file in the primary file group. This mdf file is about 190GB.
Are there any advantages to have multiple files in the primary file group in terms of backup/recovery or performance, etc ?

thanks &
regards
rm
New Member
New Member

--
26 Jan 2011 05:44 AM
Will not get much performance gain if files are on same disk, may have other benefits with multiple file groups like more recovery options. To speed up backup/restore, you can backup db to multiple backup files.
ctseah
New Member
New Member

--
01 Feb 2011 06:59 PM
Hi,

"To speed up backup/restore, you can backup up to multiple backup files." Would like to check with you how to perform such tasks ?
Do you mean if I have a zyx.mdf file which is 190GB, I can back it up to multiple backup files using sql native backup ?

regards
rm
New Member
New Member

--
02 Feb 2011 05:26 AM
Yes:

backup database db_name to disk = '\\path\file1.bak', disk = '\\path\file2.bak', disk = ... with ...
You are not authorized to post a reply.

Acceptable Use Policy