USE master GO IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test')) DROP DATABASE [Test] --1.创建数据库 CREATE DATABASE [Test] GO USE [Test] GO --2.创建文件组 ALTER DATABASE [Test] ADD FILEGROUP [FG_Test_Id_01] ALTER DATABASE [Test] ADD FILEGROUP [FG_Test_Id_02] --3.创建文件 ALTER DATABASE [Test] ADD FILE (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) TO FILEGROUP [FG_Test_Id_01]; ALTER DATABASE [Test] ADD FILE (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) TO FILEGROUP [FG_Test_Id_02]; --4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] GO --5.插入数据 INSERT INTO [dbo].[aa] SELECT 1,REPLICATE('s',3000) GO 500 --6.查询数据 SELECT * FROM [dbo].[aa] --7.创建聚集索引在[FG_Test_Id_02]文件组上 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02] GO --8.我们查看一下文件组的逻辑文件名 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname --9.移除FG_Test_Id_01文件组 ALTER DATABASE TEST REMOVE FILE FG_TestUnique_Id_01_data当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了使用下面的脚本查 看
--数据库文件、大小和已经使用空间 USE [Test] --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置 GO set nocount on create table #Data( FileID int NOT NULL, [FileGroupId] int NOT NULL, TotalExtents int NOT NULL, UsedExtents int NOT NULL, [FileName] sysname NOT NULL, [FilePath] nvarchar(MAX) NOT NULL, [FileGroup] varchar(MAX) NULL) create table #Results( db sysname NULL , FileType varchar(4) NOT NULL, [FileGroup] sysname not null, [FileName] sysname NOT NULL, TotalMB numeric(18,2) NOT NULL, UsedMB numeric(18,2) NOT NULL, PctUsed numeric(18,2) NULL, FilePath nvarchar(MAX) NULL, FileID int null) create table #Log( db sysname NOT NULL, LogSize numeric(18,5) NOT NULL, LogUsed numeric(18,5) NOT NULL, Status int NOT NULL, [FilePath] nvarchar(MAX) NULL) INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath]) EXEC ('DBCC showfilestats WITH NO_INFOMSGS') update #Data set #Data.FileGroup = sysfilegroups.groupname from #Data, sysfilegroups where #Data.FileGroupId = sysfilegroups.groupid INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID) SELECT DB_NAME() db, [FileGroup], 'Data' FileType, [FileName], TotalExtents * 64./1024. TotalMB, UsedExtents *64./1024 UsedMB, UsedExtents*100. /TotalExtents UsedPct, [FilePath], FileID FROM #Data order BY --1,2 DB_NAME(), [FileGroup] insert #Log (db,LogSize,LogUsed,Status) exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ') insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID) select DB_NAME() db, 'Log' [FileGroup], 'Log' FileType, s.[name] [FileName], s.Size/128. as LogSize , FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace, ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct, s.FileName FilePath, s.FileID FileID from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s where f.dbid = DB_ID() and (s.status & 0x40) <> 0 and s.FileID = f.FileID and l.db = DB_NAME() SELECT r.db AS "Database", r.FileType AS "File type", CASE WHEN r.FileGroup = 'Log' Then 'N/A' ELSE r.FileGroup END "File group", r.FileName AS "Logical file name", r.TotalMB AS "Total size (MB)", r.UsedMB AS "Used (MB)", r.PctUsed AS "Used (%)", r.FilePath AS "File name", r.FileID AS "File ID", CASE WHEN s.maxsize = -1 THEN null ELSE CONVERT(decimal(18,2), s.maxsize /128.) END "Max. size (MB)", CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"FROM #Results r INNER JOIN dbo.sysfiles s ON r.FileID = s.FileID ORDER BY 1,2,3,4,5 DROP TABLE #Data DROP TABLE #Results DROP TABLE #LogUSE master GO IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test')) DROP DATABASE [Test] --1.创建数据库 CREATE DATABASE [Test] GO USE [Test] GO --2.创建文件组 ALTER DATABASE [Test] ADD FILEGROUP [FG_Test_Id_01] ALTER DATABASE [Test] ADD FILEGROUP [FG_Test_Id_02] --3.创建文件 ALTER DATABASE [Test] ADD FILE (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) TO FILEGROUP [FG_Test_Id_01]; ALTER DATABASE [Test] ADD FILE (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) TO FILEGROUP [FG_Test_Id_02]; --4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] GO --5.插入数据 INSERT INTO [dbo].[aa] SELECT 1,REPLICATE('s',3000) GO 500 --6.查询数据 SELECT * FROM [dbo].[aa] --7.创建聚集索引在[FG_Test_Id_02]文件组上 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02] GO --8.我们查看一下文件组的逻辑文件名 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname --9.移除FG_Test_Id_01文件组 ALTER DATABASE TEST REMOVE FILE FG_TestUnique_Id_01_data当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了使用下面的脚本查 看
--数据库文件、大小和已经使用空间 USE [Test] --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置 GO set nocount on create table #Data( FileID int NOT NULL, [FileGroupId] int NOT NULL, TotalExtents int NOT NULL, UsedExtents int NOT NULL, [FileName] sysname NOT NULL, [FilePath] nvarchar(MAX) NOT NULL, [FileGroup] varchar(MAX) NULL) create table #Results( db sysname NULL , FileType varchar(4) NOT NULL, [FileGroup] sysname not null, [FileName] sysname NOT NULL, TotalMB numeric(18,2) NOT NULL, UsedMB numeric(18,2) NOT NULL, PctUsed numeric(18,2) NULL, FilePath nvarchar(MAX) NULL, FileID int null) create table #Log( db sysname NOT NULL, LogSize numeric(18,5) NOT NULL, LogUsed numeric(18,5) NOT NULL, Status int NOT NULL, [FilePath] nvarchar(MAX) NULL) INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath]) EXEC ('DBCC showfilestats WITH NO_INFOMSGS') update #Data set #Data.FileGroup = sysfilegroups.groupname from #Data, sysfilegroups where #Data.FileGroupId = sysfilegroups.groupid INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID) SELECT DB_NAME() db, [FileGroup], 'Data' FileType, [FileName], TotalExtents * 64./1024. TotalMB, UsedExtents *64./1024 UsedMB, UsedExtents*100. /TotalExtents UsedPct, [FilePath], FileID FROM #Data order BY --1,2 DB_NAME(), [FileGroup] insert #Log (db,LogSize,LogUsed,Status) exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ') insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID) select DB_NAME() db, 'Log' [FileGroup], 'Log' FileType, s.[name] [FileName], s.Size/128. as LogSize , FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace, ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct, s.FileName FilePath, s.FileID FileID from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s where f.dbid = DB_ID() and (s.status & 0x40) <> 0 and s.FileID = f.FileID and l.db = DB_NAME() SELECT r.db AS "Database", r.FileType AS "File type", CASE WHEN r.FileGroup = 'Log' Then 'N/A' ELSE r.FileGroup END "File group", r.FileName AS "Logical file name", r.TotalMB AS "Total size (MB)", r.UsedMB AS "Used (MB)", r.PctUsed AS "Used (%)", r.FilePath AS "File name", r.FileID AS "File ID", CASE WHEN s.maxsize = -1 THEN null ELSE CONVERT(decimal(18,2), s.maxsize /128.) END "Max. size (MB)", CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"FROM #Results r INNER JOIN dbo.sysfiles s ON r.FileID = s.FileID ORDER BY 1,2,3,4,5 DROP TABLE #Data DROP TABLE #Results DROP TABLE #Log
联系客服
