SQL Server - Borrar file y file group tabla particionada - Transact-SQL

Buenos días,

Espero que me podáis ayudar, desde ya, siento el rollazo que os voy a soltar.

Parto de una tabla a la que le he creado 36 particiones, cada partición contiene un mes de datos, por lo que las "particiones" están creadas por un rango de fecha, este mismo campo está en la tabla y es el que uso para particionar la misma en la function.

Para ello, mensualmente he creado un file group y un file, llamado file_yyyymmdd y fg_yyyymmdd, en la function he incluido el último día de cada mes a particionar, tal que así:
CREATE PARTITION FUNCTION [pf_function] (int) AS RANGE LEFT FOR VALUES
(20120928, ....)

Seguidamente en el scheme he incluido todos los file groups. Adicionalmente a esto, comentar que he tenido que crear 37 filegroups y 37 files e incluirlos todos en el scheme y en la function únicamente las fechas de los datos a insertar.

Creando un índice cluster, todo se aloja en sus particiones correspondientes, el problema que tengo es el siguiente:

Mensualmente, crearé una nueva partición, tal que así:

/*1.- Creamos el filegroup con el último día hábil del mes de la nueva partición*/

ALTER DATABASE pruebas ADD FILEGROUP fg_prueba_particion_20150930

/*2.- creamos file con el último día hábil del mes de la nueva partición*/

Alter Database pruebas_particion
Add File (Name = 'file_prueba_particion_20150930',
Filename = 'C:\file\file_prueba_particion_20150930.ndf')
To Filegroup fg_prueba_particion_20150930

/*3.- Incorporamos la nueva fecha del último día hábil del mes a particionar en el scheme*/

ALTER PARTITION SCHEME [ps_prueba_particion]
NEXT USED [fg_prueba_particion_20150930]

/*6.- Incluimos nueva fecha en la función, será el último día hábil del mes de la nueva partición*/

ALTER PARTITION FUNCTION [pf_GSI_prueba_particion] () SPLIT RANGE (20150930);

Elimino el índice, vuelco los nuevos datos a la tabla y reconstruyo el índice.

Seguidamente debo eliminar la partición más antigua con datos para dejar una ventana de 36 meses, para ello, me creo una tabla de balanceo con la misma estructura que la original y el mismo índice y vuelco los datos de la tabla original a la de balanceo con la siguiente instrucción:

ALTER TABLE prueba_particion
SWITCH PARTITION 1 TO [prueba_particion_borrado]

Previamente, comentar que la tabla de balanceo "usada para el borrado" la creo sobre el file group que tengo que eliminar y el índice cluster de esta misma tabla lo tengo que crear sobre el primer filegroup sin datos que he comentado al inicio, ya que sino no tengo posibilidad de volcar los datos.

Una vez están los datos volcados a la tabla de eliminado y no existen en la original particionada la dropeo (la tabla usada para la eliminación), así no dejaré registros en el filegroup ni en los file

Quito la fecha de la partición que hemos eliminado de la function:

ALTER PARTITION FUNCTION pf_prueba_particion() MERGE RANGE(20120928) .


Hasta aqui todo perfecto, el problema lo tengo a la hora de eliminar el file y el filegroup ya que no tengo posibilidad, me dice que tiene datos y no es posible, lo cual no es cierto, lo he intentado con las siguientes opciones:

DBCC SHRINKFILE (file_prueba_particion_20150928, EMPTYFILE)

DBCC UPDATEUSAGE (0)
GO

Alter Database pruebas_particion remove file file_prueba_particion_20150928

Alter Database pruebas_particion remove filegroup fg_prueba_particion_20120928

Msg 5042, Level 16, State 12, Line 1
The filegroup 'fg_prueba_particion_20120928' cannot be removed because it is not empty.

¿Sabéis porque me ocurre esto? ¿No puedo ni tan siguiente hacer el shrinkfile?

Gracias y espero vuestros comentarios.

La instrucción correcta es:

DROP PARTITION SCHEME myRangePS1;

Muchas gracias por tu respuesta!

Si borro el SHEME,¿ no se me quedará la tabla mal?

No quiero quitar las particiones, solo mensualmente quitar la más antigua y crear una nueva con más datos, y no puedo eliminar el file y el file group del mes más antiguo aunque esté vacío, ni tan siquiera puedo hacer el shink ya que me da fallo, he probado lo siguiente:

-->amplio espacio al file:

GO
ALTER DATABASE pruebas_particion
MODIFY FILE
(NAME = file_prueba_particion_20120928,
SIZE = 50000MB);
GO

--> Intento vacíarlo y me devuelve error:

DBCC SHRINKFILE (file_prueba_particion_20120928, EMPTYFILE)


SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files where name ='file_prueba_particion_20120928';

file_prueba_particion_20120928 499.875000

DBCC SQLPERF(LOGSPACE);
GO

DBCC SHRINKFILE (file_prueba_particion_20120928, EMPTYFILE)
--DEVUELVE ERROR

--revisamos los datos que hay en el filegroup

SELECT OBJECT_NAME(object_id), *
FROM sys.data_spaces ds INNER JOIN sys.allocation_units au
ON ds.data_space_id = au.data_space_id
INNER JOIN sys.partitions p
ON au.container_id = CASE WHEN au.type = 2 THEN p.partition_id ELSE p.hobt_id END
WHERE ds.name = 'fg_prueba_particion_20120928'

--Existe el filegroup prueba_particion fg_prueba_particion_20120928

GO
ALTER DATABASE pruebas_particion SET SINGLE_USER WITH NO_WAIT
GO
DBCC CHECKDB (pruebas_particion,REPAIR_REBUILD)
GO
ALTER DATABASE pruebas_particion SET MULTI_USER WITH NO_WAIT

DBCC SHRINKFILE ('file_prueba_particion_20120928', EMPTYFILE)
--DEVUELVE ERROR


Alter Database pruebas_particion remove file file_prueba_particion_20120928

Alter Database pruebas_particion remove filegroup fg_prueba_particion_20120928

Msg 5042, Level 16, State 1, Line 1
The file 'file_prueba_particion_20120928' cannot be removed because it is not empty.

¿Alguen sabe porqué no puedo eliminarlo?

Gracias de antemano

Revisa esta ligas

http://iunite.com.au/wp/blog/index.php/2012/04/the-filegroup-cannot-be-removed-because-it-is-not-empty/


http://iunite.com.au/wp/blog/index.php/2012/04/how-to-remove-a-filegroup-that-was-part-of-a-table-partitioning-function/

sp_helpfilegroup

go
sp_helpfilegroup @filegroupname='PRIMARY'

sp_helpfilegroup @filegroupname='Data_..'


--eliminar file y group file
/*
ALTER DATABASE [..] REMOVE FILE [File_...]
GO
ALTER DATABASE [.. REMOVE FILEGROUP [Data_...]