Sqlserver y yo: diciembre 2023

En ocasiones las tablas crecen y luego se eliminan los datos lo que genera el archivo de datos quede con el espacio sobre aprovisionado.

Vamos a realizar un ejemplo en el haremos crecer a una tabla y luego eliminaremos los datos.

Usaremos la base de datos de ejemplo TestDB y veremos la asignación de los archivos de datos.

USE [TestDB];
GO
SELECT DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Archivo de datosTestDB con 8Mb asignados y disponible 5.37 Mb.

Creamos una tabla y generamos datos para que se incremente el almacenamiento.

USE [TestDB];
GO
CREATE TABLE [dbo].[TablaTest] (
[Valor] int NULL,
[Cadena_1] nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cadena_2] nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fecha] datetime NULL DEFAULT getdate())
ON [PRIMARY]
GO

insert into [dbo].[TablaTest]([Valor],Cadena_1, Cadena_2)
select c.object_id , c.name+str(s1.n)+str(s2.n)+str(s3.n)+str(s4.n), c.name +str(s2.n)
from [sys].[all_columns] c
cross join
       (select v1.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)) s1
cross join
       (select v1.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)) s2
cross join
       (select v1.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)) s3
cross join
       (select v1.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)) s4
GO

Veamos como crecieron los archivos de datos

SELECT DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Revisemos el reporte de uso de datos por tabla desde el SQL Management

Ahora vamos a truncar la tabla y veamos el tamaño del archivo de datos

USE [TestDB];
GO
GO
truncate table [dbo].[TablaTest]
GO

SELECT DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Como vemos el archivo de datos quedo inflado, por lo que podemos es candidato a reducirlo.

Procederemos a reducir tanto el archivo de datos y el log.
NOTA: Es recomendable previo a realizar esta tarea generar un backup por cualquier eventualidad, fuera de horario operativo mas aun si es un ambiente productivo!!!

USE [TestDB]
GO
DBCC SHRINKFILE (N'TestDB' , 8)
GO
USE [TestDB]
GO
DBCC SHRINKFILE (N'TestDB_log' , 0)
GO

Ahora vamos a ver el tamaño de archivo de datos

USE [TestDB]
GO
SELECT DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Listo! Hemos recuperado el espacio no utilizado

Sqlserver y yo

jueves, 28 de diciembre de 2023

SQLSERVER Como recuperar espacio no utilizado del archivo de datos