Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filegroups?

Posted on 2002-07-16
6
Medium Priority
?
1,156 Views
Last Modified: 2012-08-14
How do you mage filegroups in SQL Server?  Can you associate tables to filegroups?  I am used to Oracle and wondering if filegroups are szupposed to fill the same roll as tablespaces?
0
Comment
Question by:jmouland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 7157710
Filegroups are *similar* to tablespaces, but not identical.

-- How do you ma[na]ge filegroups in SQL Server?

Filegroups are created with the CREATE DATABASE and ALTER DATABASE commands.  A filegroup in this context is just a name, with one or more associated physical DB files.  These can be on different drives.  An example:

CREATE DATABASE MyDatabase
ON PRIMARY
  ( NAME='MyDatabase_Main',
    FILENAME='c:\mssql7\data\MyDBMain.mdf',
    SIZE=10,
    MAXSIZE=20,
    FILEGROWTH=2),
FILEGROUP My_FileGroup1
  ( NAME = 'My_FileGroup1_Data1',
    FILENAME = 'd:\data\My_FileGroup1_Data1.ndf',
    SIZE = 20MB,
    MAXSIZE=100,
    FILEGROWTH=5),
  ( NAME = 'My_FileGroup1_Data2',
    FILENAME = 'e:\data\My_FileGroup1_Data2.ndf',
    SIZE = 20MB,
    MAXSIZE=100,
    FILEGROWTH=5)

-- Can you associate tables to filegroups?

Yes, individual tables can be associated with filegroups.  In fact, the table and the indexes of the table can each be associated with different filegroups.

0
 

Author Comment

by:jmouland
ID: 7157760
Ok I think i just got spoiled by SQL Server and thought u could use enterprise manager to do everything.  Thanks for the help.
0
 
LVL 9

Expert Comment

by:miron
ID: 7158843
SQL Server 2000 with SP 2 does have an option of creating table on a specific filgroup, if my memroy is correct.

Cheers
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Expert Comment

by:pinkman
ID: 7161110
in the enterprise manager , in the desgin table
look near the "save" sign , you'll see "table and index
properties" sign in which you can change table's file
group and it will move the table to the new file group.
you can add files groups in the database property
window , you'll find it by right clicking on the database
name.
and don't loose your faith in the enterprise manager !
0
 

Author Comment

by:jmouland
ID: 7161792
Can you be a bit more detailed in how exactly I can create and manage filegroups in Eneterprise Manager.  I right clicked on the database and didn't see anywhere to add a filegroup.  In the design are I did see where to change the filegroup for the table filegroup and text filegroup (what are these exactly?)  But I didn't see where to manage the filegroups for any other object like index's and such.
0
 

Accepted Solution

by:
pinkman earned 400 total points
ID: 7162650
o.k.
i tell you what i'm doing . maybe someone in microsoft
designed it to be used differently , but it is working
for me. first lets move table t1 from db db1 to a new
physical location.
1) right click on db1 and then left click "properties"
2)click on the filegroups tab
3)add a new file group just by writing in the second row(or the first empty one) a new name , i use "secondary"
4)press the ok button
3)open the window again (step 1)
4)click on the "data files" tab
5) add a new row , give it your own name(first col),
brose or write the location on the second col ,
choose the secondary file group on the last col( it
opens a combo box with all filegroups names)
for now you can leave all the rest as default (file grow
atc...)
6)click ok
7)open the design table for table t1
8)change its "table filegroup" to "secondary" ,click
close and then click save.( you found the right window
already..)
i don't know what is the text file group option but step
8 will move the table to its new location.
as for indexes : when you create an index or open
its propertis , you'll see a field "index filegroup"
or "file group" where you have a combo box which allow
you to select , in our case between "primary" and
"secondary" and it will move the index to the new location.
and so on create physical locations as much as you want

hope this is ok , one day i will probably
need some answers on Oracle so stay sharp...

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question