Borrar file y file group tabla particionada - Transact-SQL

[encarna1986]

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.

[Libras]

estas segura que la particion 1 es la que se movio? que es lo que te regresa cuando revisas que las particiones tengan informacion?? porque creaste las particiones con left en lugar de right? Estas segura que la particion que estas moviendo o queriendo mover es la numero 1(esto porque normalmente la particion numero 1 esta reservada por el sistema)??

[encarna1986]

Lo primero muchas gracias por responder y leerte mi post.

Te comento, la primera partición no la elimino ya que supuestamente es la que se reserva el sistema, entonces, los datos que muevo son los de la "segunda" partición por así decir, no obstante cuando hago el split para moverlos, si que indico que es la primera, ya que es la que me aparece con datos...creo que algo no lo estoy haciendo bien y se me escapa

Lo cierto es que una vez que he movido los datos de la partición que tengo que eliminar y están vacíos los file y file group no puedo eliminarlos con lo que se queda el espacio asignado. He probado lo siguiente y tampoco me funciona

-->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.

¿Sabes que le puede suceder?

Mil millones de gracias

[Libras]

Lo que pasa es que cuando hiciste el switch de la particion moviste los datos que estaban en la particion 1, normalmente la particion 1 se reserva para el sistema, y no estas revisando en que particion estan tus datos, prueba con este query para obtener el numero de renglones por particion y revisar cual es el numero de particion que quieres mover:

Código SQL:

SELECT
DB_NAME() AS 'DatabaseName'
,OBJECT_NAME(p.OBJECT_ID) AS 'TableName'
,p.index_id AS 'IndexId'
,CASE
WHEN p.index_id = 0 THEN 'HEAP'
ELSE i.name
END AS 'IndexName'
,p.partition_number AS 'PartitionNumber'
,prv_left.VALUE AS 'LowerBoundary'
,prv_right.VALUE AS 'UpperBoundary'
,CASE
WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END AS 'FileGroupName'
,CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'UsedPages_MB'
,CAST(p.in_row_data_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'DataPages_MB'
,CAST(p.reserved_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'ReservedPages_MB'
,CASE
WHEN p.index_id IN (0,1) THEN p.ROW_COUNT
ELSE 0
END AS 'RowCount'
,CASE
WHEN p.index_id IN (0,1) THEN 'data'
ELSE 'index'
END 'Type'
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id
AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id
AND prv_left.boundary_id = p.partition_number - 1
WHERE
OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
 

ahora mencionas que el dbcc marca errores, que tipo de errores??

[encarna1986]

Buenas,

El movimiento de los datos lo hice con el siguiente alter:

ALTER TABLE GSI_prueba_particion
SWITCH PARTITION 1 TO [GSI_prueba_particion_borrado]

He lanzado la query que me has pasado y parece que las dos primeras particiones, las ha marcado como la partición 1, entonces al mover los datos..puede que la haya liado :(,

pruebas_particion GSI_prueba_particion_aux 0 HEAP 1 NULL NULL PRIMARY 0.02 0.02 0.07 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 1 NULL 20121031 fg_GSI_prueba_particion_20120928 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 2 20121031 20121130 fg_GSI_prueba_particion_20121031 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 3 20121130 20121231 fg_GSI_prueba_particion_20121130 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 4 20121231 20130131 fg_GSI_prueba_particion_20121231 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 5 20130131 20130228 fg_GSI_prueba_particion_20130131 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 6 20130228 20130329 fg_GSI_prueba_particion_20130228 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 7 20130329 20130430 fg_GSI_prueba_particion_20130329 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 8 20130430 20130531 fg_GSI_prueba_particion_20130430 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 9 20130531 20130628 fg_GSI_prueba_particion_20130531 0.03 0.02 0.03 50 data
pruebas_particion GSI_prueba_particion 1 ix_GSI_prueba_particion 10 20130628 20130730 fg_GSI_prueba_particion_20130628 0.03 0.02 0.03 50 data


Se debería eliminar el file group y el file del día 29-09-2012, no obstante.

[Libras]

si te fijas todas las particiones tienen datos, por eso esque no puedes borrarla, antes de hacer el switch te recomiendo que revises que la particion que quieres mover sea la correcta :)

otra cosa, porque usaste el switch para mover la particion? ademas estas moviendo a la misma particion que quieres borrar? aqui lo que debes de hacer es realizar un merge sin hacer el switch..

seria mas o menos como lo que se explica en este link:

http://iunite.com.au/wp/blog/index.p...ning-function/

El switch IN y switch out se usa para mover datos entre particiones no para limpiar la particion antes de eliminarla ;)

[encarna1986]

Mil millones de gracias por la atención

El problema era que estaba usando en la función la opción left y la primera partición siempre se quedaba con datos y no me dejaba eliminar el file. He cambiado todo el planteamiento y al usar la opción rigth si me funciona correctamente y puedo eliminar el archivo file.

Ahora viene lo bueno, no puedo eliminar el filegroup ya que parece ser que si está asignado al sheme aunque no tenga un file no se puede eliminar.

¿Se te ocurre alguna forma de quitar el filegroup del sheme?

El sheme no puedo eliminarlo porque tengo otras particiones y el tema de mover la tabla a otra es imposible ya que la tabla es muuuuuuuy pesada