Improve company productivity with a Business Account.Sign Up

x
?
Solved

Filegroups?

Posted on 2002-07-16
6
Medium Priority
?
1,164 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
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

601 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