En algunas ocasiones las base de datos de Microsoft SQL generan archivos de trasacciones de gran tamaño, siendo en muchos casos innecesarios el almacenaje de esta información y en muchas ocasiones la consola de administración no consigue reducir el tamaño de la base de datos.
El siguiente script consigue reducir el fichero de trasaciones del SQL Server al tamaño que se le especifique, solamente hay que modificar el nombre de la base de datos ‘BBDD’.
-- Muestra el nombre y caracteristicas de los ficheros de la BBDD
HP_HELPDB 'BBDD'
-- Muestra la version y el SP de SQL
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
SET NOCOUNT ON
-- Borrado del registro de transacciones
dump transaction BBDD with no_log
go
-- Declaración de variables que se van a usar en la configuración
DECLARE @NombreLogicoArchivo sysname,
@MaxMinutos INT,
@NuevoTamano INT
--Indique aquí su configuración.
--Base de datos con la que se va a interactuar.
USE BBDD
-- Use sp_helpfile para identificar el nombre de archivo lógico que desea reducir.
-- en el ejemplo le hemos denominado BBDD_Log, suele tener este formato.
SELECT @NombreLogicoArchivo = 'BBDD_Log',
@MaxMinutos = 10, --Límite de tiempo permitido para dar la vuelta al registro.
@NuevoTamano = 100 --en MB
-- Configuración e inicio
DECLARE @TamanoOriginal int
SELECT @TamanoOriginal = size --en páginas de 8 KB
FROM sysfiles
WHERE name = @NombreLogicoArchivo
SELECT 'El tamaño original del registro de' + db_name() + ' es ' +
CONVERT(VARCHAR(30),@TamanoOriginal) + ' páginas de 8 KB ó ' +
CONVERT(VARCHAR(30),(@TamanoOriginal *8/1024)) + 'MB'
FROM sysfiles
WHERE name = @NombreLogicoArchivo
CREATE TABLE DummyTrans (DummyColumna char (8000) not null)
-- Dar la vuelta al registro y truncarlo.
DECLARE @Contador INT,
@HoraInicio DATETIME,
@TruncReg VARCHAR(255)
SELECT @HoraInicio = GETDATE(),
@TruncReg = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Intentar una reducción inicial.
DBCC SHRINKFILE (@NombreLogicoArchivo, @NuevoTamano)
EXEC (@TruncReg)
-- Dar la vuelta al registro, si es necesario.
-- no se ha excedido el máximo tiempo establecido
WHILE @MaxMinutos > DATEDIFF (mi, @HoraInicio, GETDATE())
-- no se ha reducido el registro
AND @TamanoOriginal = (SELECT size FROM sysfiles WHERE name = @NombreLogicoArchivo)
-- El valor pasado para el tamaño nuevo es más pequeño que el tamaño actual.
AND (@TamanoOriginal * 8 /1024) > @NuevoTamano
BEGIN --Bucle externo.
SELECT @Contador = 0
WHILE ((@Contador < @TamanoOriginal / 16) AND (@Contador < 50000))
BEGIN -- Actualización
-- Como es un campo de tipo char, inserta 8000 bytes.
INSERT DummyTrans VALUES ('Llenar registro')
DELETE DummyTrans
SELECT @Contador = @Contador + 1
END -- Actualización
-- Probar si un truncamiento reduce de tamaño el registro.
EXEC (@TruncReg)
END -- Bucle externo
SELECT ' El tamaño final del registro de ' + db_name() + ' es de ' +
CONVERT(VARCHAR(30),size) + ' páginas de 8 KB ó ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @NombreLogicoArchivo
DROP TABLE DummyTrans
PRINT '*** RECUERDE: DEBE REALIZAR UNA COPIA DE SEGURIDAD COMPLETA DE LA BASE DE DATOS***'
SET NOCOUNT OFF
nmiralles SQL Server SQL SERVER 2000, SQL SERVER 2005