從 filegroup 中刪掉 file

–建立測試用資料庫
CREATE DATABASE [a] ON  PRIMARY
( NAME = N’a’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAa.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
( NAME = N’a1′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAa1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N’a_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAa_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
go
use a
go
 
–插入資料
create table t(c1 int,c2 char(100))
set nocount on
while 1=1
 insert t values(100,’a’)
GO
 
–清空 file
DBCC SHRINKFILE(‘a1’, EMPTYFILE)
GO
 
–從 filegroup 中刪掉 file
ALTER DATABASE [a]  REMOVE FILE [a1]
GO

3 Comments

  1. Posted 2010 年 04 月 26 日 at 06:57:42 | Permalink | 回覆

    百敬老师: 如果我的数据库中有两个文件组(一个PRIMARY文件组和另外一个文件组Second),其中Second文件组中有一个文件Files(已经存在数据) ,现在我想删除Second文件组的话,我应该怎么做?

  2. 百敬
    Posted 2010 年 04 月 26 日 at 12:36:55 | Permalink | 回覆

    先建一個 table,把資料搬過去,在刪掉舊 table,rename 新 table,刪檔案與檔案群組CREATE DATABASE [test] ON PRIMARY ( NAME = N’test’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ), FILEGROUP [fg2] ( NAME = N’f2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’test_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)GOUSE TestCREATE TABLE t(c1 int) ON fg2INSERT t values(1)create table t1(c1 int) on [primary]insert t1 select * from tdrop table tEXEC dbo.sp_rename @objname = N'[dbo].[t1]’, @newname = N’t’, @objtype = N’OBJECT’ALTER DATABASE [test] REMOVE FILE [f2]GOALTER DATABASE [test] REMOVE FILEGROUP [fg2]GO

  3. Posted 2010 年 04 月 27 日 at 00:58:50 | Permalink | 回覆

    谢谢 百敬老师的回复

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: