Solved

sql server 2008: change default file group

Posted on 2011-03-07
1
699 Views
Last Modified: 2012-05-11
I have 2 files with 2 bd group. I no longer wish to use the primary group file, I use this file PFG01 group. Enter the database properties and the site of filegroup, filegroup PFG01 dial by default, but does not work, that is, when I returned to add more records dbFileGroup increased the file that is in the primary group.
 I make that the new information is recorded on the file that is in the group PFG01

CREATE DATABASE dbFileGroup ON  PRIMARY
( NAME = 'dbPart', FILENAME = N'c:\SQLDATA\dbFileGroup.mdf' , SIZE = 3072KB , FILEGROWTH = 100KB )
 LOG ON
( NAME = 'dbPart_log', FILENAME = N'c:\SQLLOG\dbFileGroup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

go

--
use dbFileGroup
go

CREATE TABLE [dbo].tabla_demo
(  
id int identity primary key,    
c varchar(200) NULL
)

--2
declare @i int
set @i = 1
while @i<=2 -- Luego 1.000.000 filas
begin        
      INSERT INTO dbo.tabla_demo    
      SELECT top (50000)      
               replace(cast(NEWID() as varchar(100)), '-','') + REPLICATE('a', 200-32)    
      FROM AdventureWorks.Sales.SalesOrderHeader  
            CROSS JOIN AdventureWorks.Sales.SalesOrderDetail
      print cast (@i as varchar(100))  
      set @i=@i+1
end

--Creación de los FileGroup
USE master
GO
ALTER DATABASE dbFileGroup ADD FILEGROUP PFG01

--Creación de los DataFile
ALTER DATABASE dbFileGroup ADD FILE
( NAME = N'dbFileGroup1',
  FILENAME = N'C:\SQLDATA\dbFileGroup1.ndf' , SIZE = 3072KB , FILEGROWTH = 512KB )
TO FILEGROUP PFG01

--
use dbFileGroup
go
select MAX(id) from dbo.tabla_demo

declare @i int
set @i = 1
while @i<=1
begin        
      INSERT INTO dbo.tabla_demo    
      SELECT top (50000)      
               replace(cast(NEWID() as varchar(100)), '-','') + REPLICATE('a', 200-32)    
      FROM AdventureWorks.Sales.SalesOrderHeader  
            CROSS JOIN AdventureWorks.Sales.SalesOrderDetail
      print cast (@i as varchar(100))  
      set @i=@i+1
end
0
Comment
Question by:enrique_aeo
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35058438
setting a default filegroup will ONLY choose which filegroup a NEW table will go to.
new records will still go to the same filegroup(s) the table has been created in.

to move a table from 1 filegroup to another, you have to move it's clustered index to the new filegroup (aka create a [new] clustered index on the new filegroup)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

13 Experts available now in Live!

Get 1:1 Help Now