Solved

Filegroups?

Posted on 2002-07-16
6
1,146 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

910 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

23 Experts available now in Live!

Get 1:1 Help Now