Script para realizar el Shrink DataFile en forma automática

En ciertas ocasiones, tenemos que achicar algun DataFile de nuestra base de datos, que por una mala administracion y no tener controles preventivos, la fragmentacion interna de la misma esta por encima del tamaño real en mas de un 50%.
Para estos casos, la propia herramienta de SQL Server es un poco lenta, porque intenta achicar la misma de una sola vez, con lo que la demora puede ser mas que larga, en algunos casos, superar las 24 hs y no hacer nada inclusive.

En el siguiente script, lo que hacemos es achicar la misma con bloques de 1Gb, de esta forma, sabemos que el motor de la base de datos trabajara en forma mas eficiente y obtendremos el resultado de la desfragmentacion en un tiempo mas reducido y controlado.

 
DECLARE @DB1 NVARCHAR(300)
SELECT TOP 1 @DB1=[name]
FROM master.sys.databases
WHERE NAME LIKE 'BBDD_Analizar'
ORDER BY NAME
 
DECLARE @comando NVARCHAR(4000)
 
 
SELECT @comando='USE '+@db1+char(13)+'
DECLARE @CurrentFileSize INT
DECLARE	@FileId INT
DECLARE @LogicalFile VARCHAR(20)
DECLARE @MinSize INT
DECLARE @cSQl VARCHAR(500)
DECLARE @currentPercent float
DECLARE @minPercent float
SET @minPercent=5


DECLARE cc CURSOR FOR
SELECT	fileid,
		b.name,
		CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),
		CONVERT(decimal(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2))		
FROM	dbo.sysfiles a
INNER JOIN sys.database_files b ON a.fileid=b.file_id
AND CONVERT(decimal(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2)) > 1000
OPEN cc 
FETCH cc INTO @FileId, @LogicalFile, @CurrentFileSize, @MinSize
WHILE (@@FETCH_STATUS = 0)
BEGIN	
	SELECT  @LogicalFile LogicalFile,
			@CurrentFileSize CurrentFileSize, @MinSize MinSize,
			@CurrentFileSize-@MinSize Liberar, 1.00*(@CurrentFileSize-@MinSize)/@CurrentFileSize*100 AS ''% Fragmentation''
	  
	WHILE 1=1
	BEGIN
		SELECT  @LogicalFile = b.name,
				@CurrentFileSize = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),
				@MinSize  = CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2))
		FROM    dbo.sysfiles a
		INNER   JOIN sys.database_files b ON a.fileid=b.file_id
		WHERE   a.FILEID = @FileId
	 
		SET @currentPercent=ROUND(1.00*(@CurrentFileSize-@MinSize)/@CurrentFileSize*100,0)
	 
		IF @currentPercent <= @minPercent BREAK
		SET @CurrentFileSize = @CurrentFileSize - 1000
		EXEC (''DBCC SHRINKFILE '' + ''(N'' + '''''''' + @LogicalFile +'''''''' + '', '' + @CurrentFileSize + '')'')
		PRINT ''Current Size: '' + CONVERT(VARCHAR(15), @CurrentFileSize) + '' FileId: '' + CAST(@FileId AS CHAR(2)) + '' FileName: '' + @LogicalFile
	END
FETCH cc INTO @FileId, @LogicalFile, @CurrentFileSize, @MinSize
END
CLOSE cc
DEALLOCATE cc'
--PRINT @COMANDO 
EXEC(@COMANDO)

Otra forma que tenemos de hacer esto, es siemplemente tomar como metodología, dejar como máximo, el 15% de espacio desfragmentado.
Para esto, simplemente ejecutamos el siguiente script.

 

DECLARE @DB1 NVARCHAR(300)
SELECT TOP 1 @DB1=[name]
FROM master.sys.databases
WHERE NAME LIKE 'BBDD_Analizar'
ORDER BY NAME
 
DECLARE @comando NVARCHAR(4000)
 
 
SELECT @comando='USE '+@db1+char(13)+'
DECLARE @CurrentFileSize INT
DECLARE	@FileId INT
DECLARE @LogicalFile VARCHAR(20)
DECLARE @MinSize INT
DECLARE @cSQl VARCHAR(500)
DECLARE @currentPercent float
DECLARE @minPercent float
SET @minPercent=15


DECLARE cc CURSOR FOR
SELECT	fileid,
		b.name,
		CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),
		CONVERT(decimal(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2))		
FROM	dbo.sysfiles a
INNER JOIN sys.database_files b ON a.fileid=b.file_id
OPEN cc 
FETCH cc INTO @FileId, @LogicalFile, @CurrentFileSize, @MinSize
WHILE (@@FETCH_STATUS = 0)
BEGIN	
	SELECT  @LogicalFile LogicalFile,
			@CurrentFileSize CurrentFileSize, @MinSize MinSize,
			@CurrentFileSize-@MinSize Liberar, 1.00*(@CurrentFileSize-@MinSize)/@CurrentFileSize*100
	  
	WHILE 1=1
	BEGIN
		SELECT  @LogicalFile = b.name,
				@CurrentFileSize = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),
				@MinSize  = CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,''SpaceUsed'')/128.000,2))
		FROM    dbo.sysfiles a
		INNER   JOIN sys.database_files b ON a.fileid=b.file_id
		WHERE   a.FILEID = @FileId
	 
		SET @currentPercent=ROUND(1.00*(@CurrentFileSize-@MinSize)/@CurrentFileSize*100,0)
	 
		IF @currentPercent <= @minPercent BREAK
		SET @CurrentFileSize = @CurrentFileSize - 1000
		EXEC (''DBCC SHRINKFILE '' + ''(N'' + '''''''' + @LogicalFile +'''''''' + '', '' + @CurrentFileSize + '')'')
		PRINT ''Current Size: '' + CONVERT(VARCHAR(15), @CurrentFileSize) + '' FileId: '' + CAST(@FileId AS CHAR(2)) + '' FileName: '' + @LogicalFile
	END
FETCH cc INTO @FileId, @LogicalFile, @CurrentFileSize, @MinSize
END
CLOSE cc
DEALLOCATE cc'
--PRINT @COMANDO 
EXEC(@COMANDO)