March 29, 2010 at 10:37 am
Hi,
We have MOSS 2007 SP1 databases on sql server 2005 EE x64 with SP3.
We have Separate drive for Data files (mdf) and log files (ldf) and separate drive for TempDB & both mdf & ldf (1 mdf file & 1 ldf file) of tempDB are on same drive
It is in production since 6 months and now the content database sixe is reached to 60 GB and now we want to create secondary files (ndf) for content database.
I went through the link http://technet.microsoft.com/en-us/library/cc298801.aspx and have couple of questions
1. We have allocated a separate drive to keep secondary files
2. What are the exact steps I need to follow to create secondary data file?
3. Do I need to move any tables from Primary data file to secondary?
4. What size of Secondary file I need to create?
5. Will the 60 GB of Primary data file will be sahred with Secondary file automatically?
Thansk for your help
March 29, 2010 at 10:44 am
Not sure what MOSS is, but here goes.
gmamata7 (3/29/2010)
2. What are the exact steps I need to follow to create secondary data file?
Open up Books Online and lookup ALTER DATABASE for the T-SQL version. Alternatively, right click the Database in SSMS, go to Properties, and then the Files and FileGroups pages should help you. Given your later questions, you'll probably want to keep just one FileGroup (the Primary) and only add additional files to that FileGroup.
gmamata7 (3/29/2010)
3. Do I need to move any tables from Primary data file to secondary?
Need to? No. You may want to, though. For tables with Clustered Indexes, you should be able to do it through an Index rebuild (but check that in Books Online).
gmamata7 (3/29/2010)
4. What size of Secondary file I need to create?
That depends entirely on you, your database, and your expected file size increases. At the very least, if you're planning to move existing tables, I'd add up the size of those tables and create the file with a 10% above capacity.
gmamata7 (3/29/2010)
5. Will the 60 GB of Primary data file will be sahred with Secondary file automatically?
Nope. Old existing data will NOT be shared. Only new data will go to the new file, and then only if you put that file on the PRIMARY filegroup. If you create the new file on a new filegroup but leave the new filegroup set as SECONDARY, then no data will ever go to it.
Look up Files and FileGroups in Books Online. Much more detail there.
March 29, 2010 at 11:12 am
Do we need to create multiple data files for the COntent database which is more than 100 GB only? or
It required to create multiple data files for the COntent database which has less than 100 GB too?
thanks
March 29, 2010 at 11:33 am
Do not mistake "want" for "need". There is no need. Technically, you could run a 1 TB size database on one file and one filegroup.
The idea of splitting your database up into multiple files exists for two major reasons: 1) Performance management, 2) Backup & Restore Plans.
If you put all your read only tables into one filegroup, you can optimize your backup plan by using Partial backups.
By splitting highly read & written data into multiple files and putting those files on different *physical* drives (not logical), you can enhance SQL Server I/O.
There are lots of other tricks you can do with multiple files or filegroups, but you can read up about them in Books Online. These two, however, are the main ones that help most people. Again, though. It's not a matter of "need". It's a matter of what you think is best for your database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply