Solved

Filegroups?

Posted on 2002-07-16
6
1,149 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:bhess1
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 100 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2016 SQL Licensing 7 40
create an aggregate function 9 31
Query Help - MSSQL - Averages 5 25
sql server service accounts 4 19
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now