March 5, 2008 at 3:38 am
I am trying to drop a filegroup that is no longer used. I have removed the data file so theoretically it is empty. It was referenced in the creation of a partition scheme. I am guessing this is the problem.
Please run the following script to illustrate the problem.
TIA 8adger.
USE [master]
GO
-- Create the database
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RiskArchiveMini')
BEGIN
ALTER DATABASE [RiskArchiveMini] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [RiskArchiveMini]
END
GO
CREATE DATABASE [RiskArchiveMini] ON PRIMARY
( NAME = N'RiskArchiveMini',
FILENAME = N'C:\RiskArchiveMini.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'RiskArchiveMini_log',
FILENAME = N'C:\RiskArchiveMini_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
USE RiskArchiveMini;
GO
-- Create the FileGroups and Data files this will this will be for monthly for 2007. The first
-- Filegroup will store data for before Jan 2007 and the last will be for Dec07. This means there will be 13.
-- 1
ALTER DATABASE RiskArchiveMini ADD FILEGROUP fgMar07;
GO
ALTER DATABASE RiskArchiveMini ADD FILE
(
NAME = 'dfLimitUtilisationMar07',
FILENAME = 'C:\dfLimitUtilisationMar07.ndf'
)
TO FILEGROUP fgMar07;
GO
ALTER DATABASE RiskArchiveMini ADD FILEGROUP fgMar08;
GO
ALTER DATABASE RiskArchiveMini ADD FILE
(
NAME = 'dfLimitUtilisationMar08',
FILENAME = 'C:\dfLimitUtilisationMar08.ndf'
)
TO FILEGROUP fgMar08;
GO
IF EXISTS(SELECT 1 FROM Sys.Partition_Functions WHERE NAME = 'pfPartitionByMonth')
BEGIN
DROP PARTITION FUNCTION pfPartitionByMonth
END
GO
CREATE PARTITION FUNCTION pfPartitionByMonth(DATETIME)
AS RANGE RIGHT
FOR VALUES ('20070401');
GO
IF EXISTS(SELECT * FROM Sys.Partition_Schemes WHERE NAME = 'psLimitUtilisation')
BEGIN
DROP PARTITION SCHEME psLimitUtilisation
END
GO
CREATE PARTITION SCHEME psLimitUtilisation
AS PARTITION pfPartitionByMonth
TO (fgMar07,fgMar08);
GO
IF EXISTS(SELECT 1 FROM Sys.Objects WHERE Name = 'LimitUtilisation' AND Type = 'U')
BEGIN
DROP TABLE LimitUtilisation
END
GO
CREATE TABLE [dbo].[LimitUtilisation](
[DataDate] [datetime] NOT NULL
) ON psLimitUtilisation(DataDate)
GO
ALTER TABLE LimitUtilisation
ADD CONSTRAINT PK_LimitUtilisation
PRIMARY KEY([DataDate])
ON psLimitUtilisation(DataDate);
use riskarchivemini
-- Lets have a look what we've got
-- Filegroups
select * from sys.data_spaces
-- Partition sceme mappings to FileGroups
select * from sys.destination_data_spaces
-- Check to see if there is any data in the table
SELECT partition_number, rows FROM sys.partitions WHERE object_id = OBJECT_ID('LimitUtilisation')
-- Look at the partiton ranges specified by the function
SELECT * FROM sys.partition_range_values ORDER BY boundary_ID
-- Add a Filegroup for next month
ALTER DATABASE RiskArchiveMini ADD FILEGROUP fgApr08
-- Add dataFile
ALTER DATABASE RiskArchiveMini ADD FILE(NAME = 'dfLimitUtilisationApr08', FILENAME = 'C:\dfLimitUtilisationApr08.ndf') TO FILEGROUP fgApr08
-- Modify the PS so it maps to the next filegroup
ALTER PARTITION SCHEME psLimitUtilisation NEXT USED fgApr08
-- Add new Partition range to the end
ALTER PARTITION FUNCTION pfPartitionByMonth() SPLIT RANGE ('Apr 1 2008 12:00AM')
----------------------------------------------------------------------
-- Now we have the next month set up Remove the oldest months stuff.
----------------------------------------------------------------------
-- Drop the oldest datafile
ALTER DATABASE RiskArchiveMini REMOVE FILE dfLimitUtilisationMar07
UPDATE STATISTICS LimitUtilisation
ALTER PARTITION SCHEME psLimitUtilisation NEXT USED
-- Drop the oldest Filegroup
ALTER DATABASE RiskArchiveMini REMOVE FILEGROUP fgMar07
January 19, 2011 at 8:44 am
before removing the file try shrinking the file first with EMPTYFILE PARAM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply