Cómo saber el espacio que puedo liberar haciendo un shrink en SQL Server

Cuando damos soporte a servidores SQL uno de los problemas más habituales es es el espacio libre en los discos duros, o mejor dicho, la falta de espacio en estos discos. Esto nos obliga a los DBA a tener que liberar espacio con shrink habitualmente.

Por lo general, las empresas no son muy dadas a tener mucho espacio sin utilizar en los discos ya que esto puede significar un coste monetario elevado por algo que no están usando (o eso creen), por lo que lo normal es encontrar que los discos que están siendo usados por la instancia SQL Server tienen un espacio libre de entre el 10 y el 30%.

Para que tengáis una referencia, en mi actual empresa recibimos alertas «warning» cuando el disco baja del 20% de espacio libre y alertas «criticas» cuando baja del 10%, y aún así algunos clientes prefieren bajarlo al 10% y 5% respectivamente.

Cuando recibimos una de estas alertas, lo primero que hacemos es informar al cliente ya que ellos pueden decidir si quieren ampliar el espacio en el disco (rara vez ocurre si pueden evitarlo) o si prefieren que intentemos hacer algún shrink a los archivos de las bases de datos.

¿Qué es un shrink en SQL Server?

Podría ponerme muy técnico, pero un shrink básicamente consiste en intentar recuperar todo ese espacio que los archivos de la base de datos están consumiendo pero no están usando y se puede aplicar a los archivos de datos, a los logs y a los archivos de datos de la TempDB.

El problema surge cuando el servidor tiene muchas bases de datos y revisar una por una la base de datos para comprobar el espacio libre disponible se vuelve una tarea muy pesada, por lo que aquí os dejo 3 scripts que os harán este trabajo increíblemente más sencillo.

Hacer un shrink a los archivos de datos

El siguiente script nos mostrará el nombre del archivo de datos, a que base de datos pertenece, en que disco está almacenado, el espacio que está ocupando , el espacio que está usando realmente y cuanto espacio podemos ganar si hacemos un shrink sobre él. Además, también nos genera 2 scripts, uno para truncar el archivo y para hacer shrink.

IF OBJECT_ID('tempdb..#DBsizes') IS NOT NULL
    DROP TABLE #DBsizes

CREATE TABLE #DBsizes
(
    ServerName nvarchar(max),
    DBname nvarchar(max),
    Filename nvarchar(max),
    CurrentSize INT,
    SpaceUsed	INT,
    CurrentAvailableSpace	INT,
    Growth INT,
    Physical nvarchar(max),
    status INT,
)

EXEC sp_MSforeachdb'
USE [?]
INSERT INTO #DBsizes
SELECT
        @@SERVERNAME,
        db_name(),
        ms.name, 
        -- Current Size		
        sf.size/128,
        -- Used space
        fileproperty(sf.name,''SpaceUsed'')/128,
        -- Current Available space
        (sf.size-fileproperty(sf.name,''SpaceUsed''))/128,
        CASE
            WHEN sf.growth < 0 THEN -1
            WHEN sf.growth = 0 THEN 0
            ELSE (sf.growth)/128
        END,
        ms.filename,
        sf.status
FROM	master.dbo.sysaltfiles ms, dbo.sysfiles sf
WHERE	dbid = DB_ID()
        AND ms.fileid = sf.FILEID
        AND sf.groupid <> 0
        AND dbid <> 2
';

WITH Cte
AS
(
SELECT
    DBname,
    Filename,
    SUBSTRING(Physical,1,1) as [Drive],
    CurrentSize,
    SpaceUsed,
    Growth,
    CASE
        WHEN Growth <= 0 THEN (SpaceUsed*1)+1
        ELSE ((SpaceUsed/Growth) + 1) * Growth
    END as TargetSize,
    'USE ' + QUOTENAME(DBname) + '; DBCC SHRINKFILE(N'''+Filename+''' , 0, TRUNCATEONLY);' as 'TruncateOnly',
    CASE
        WHEN Growth <= 0 THEN 'USE ' + QUOTENAME(DBname) + '; DBCC SHRINKFILE(N'''+Filename+''' , '+CAST(CAST((SpaceUsed*1) + 1 AS FLOAT) as NVARCHAR(MAX))+' );'
        ELSE 'USE ' + QUOTENAME(DBname) + '; DBCC SHRINKFILE(N'''+Filename+''' , '+ CAST(CAST((((SpaceUsed/Growth) + 1) * Growth) AS FLOAT) as NVARCHAR(MAX)) +');'
    END as 'ShrinkFile'
FROM #DBsizes
)
SELECT 
    DBname,
    Filename,
    Drive,
    CurrentSize,
    SpaceUsed,
    CAST(SpaceUsed as FLOAT)*100.0/(CurrentSize+1) as 'SpaceUsed_Pct',
    TargetSize,
    CurrentSize - TargetSize as [PotentialGain],
    CAST(CurrentSize - TargetSize as FLOAT)*100.0/(CurrentSize+1) as 'PotentialGain_Pct',
    TruncateOnly,
    ShrinkFile
FROM cte
ORDER BY Drive ASC, PotentialGain DESC

IF OBJECT_ID('tempdb..#DBsizes') IS NOT NULL

    DROP TABLE #DBsizes

Esto será el resultado de la ejecución:

 

Hacer un shrink a los logs de las base de datos

Este script hace básicamente lo mismo que el anterior, pero esta vez para los archivos de log de las bases de datos.

IF OBJECT_ID('tempdb..#DBsizes') IS NOT NULL
    DROP TABLE #DBsizes

CREATE TABLE #DBsizes
(
    ServerName nvarchar(max),
    DBname nvarchar(max),
    Filename nvarchar(max),
    CurrentSize INT,
    SpaceUsed	INT,
    CurrentAvailableSpace	INT,
    Growth INT,
    Physical nvarchar(max),
    status INT,
)

EXEC sp_MSforeachdb'
USE [?]
INSERT INTO #DBsizes
SELECT
        @@SERVERNAME,
        db_name(),
        ms.name, 
        -- Current Size		
        sf.size/128,
        -- Used space
        fileproperty(sf.name,''SpaceUsed'')/128,
        -- Current Available space
        (sf.size-fileproperty(sf.name,''SpaceUsed''))/128,
        CASE
            WHEN sf.growth < 0 THEN -1
            WHEN sf.growth = 0 THEN 0
            ELSE (sf.growth)/128
        END,
        ms.filename,
        sf.status
FROM	master.dbo.sysaltfiles ms, dbo.sysfiles sf
WHERE	dbid = DB_ID()
        AND ms.fileid = sf.FILEID
        AND sf.groupid = 0
        AND dbid <> 2
';

WITH Cte
AS
(
SELECT
    DBname,
    Filename,
    SUBSTRING(Physical,1,1) as [Drive],
    CurrentSize,
    SpaceUsed,
    Growth,
    CASE
        WHEN Growth <= 0 THEN (SpaceUsed*1)+1
        ELSE ((SpaceUsed/Growth) + 1) * Growth
    END as TargetSize,
    'USE ' + QUOTENAME(DBname) + '; CHECKPOINT; DBCC SHRINKFILE(N'''+Filename+''' , 0, TRUNCATEONLY);' as 'TruncateOnly',
    CASE
        WHEN Growth <= 0 THEN 'USE ' + QUOTENAME(DBname) + '; CHECKPOINT; DBCC SHRINKFILE(N'''+Filename+''' , '+CAST(CAST((SpaceUsed*1) + 1 AS FLOAT) as NVARCHAR(MAX))+' );'
        ELSE 'USE ' + QUOTENAME(DBname) + '; CHECKPOINT; DBCC SHRINKFILE(N'''+Filename+''' , '+ CAST(CAST((((SpaceUsed/Growth) + 1) * Growth) AS FLOAT) as NVARCHAR(MAX)) +');'
    END as 'ShrinkFile'
FROM #DBsizes
)
SELECT 
    DBname,
    Filename,
    Drive,
    CurrentSize,
    SpaceUsed,
    CAST(SpaceUsed as FLOAT)*100.0/(CurrentSize+1) as 'SpaceUsed_Pct',
    TargetSize,
    CurrentSize - TargetSize as [PotentialGain],
    CAST(CurrentSize - TargetSize as FLOAT)*100.0/(CurrentSize+1) as 'PotentialGain_Pct',
    TruncateOnly,
    ShrinkFile
FROM cte
ORDER BY Drive ASC, PotentialGain DESC

IF OBJECT_ID('tempdb..#DBsizes') IS NOT NULL

    DROP TABLE #DBsizes

Hacer un shrink a los archivos de la tempdb

Este script es un poco diferente y simplemente truncará todos los archivos de datos de la tempdb de manera automática. Es posible que el shrink no funcione en la tempdb aunque haya espacio disponible, si os pasa esto podéis echar un vistazo a este post donde os explico como solucionarlo.

USE [tempdb]
GO
CHECKPOINT; 
GO
DECLARE @sqlCmd VARCHAR(MAX)
SET @sqlcmd=''
SELECT @sqlCmd = @sqlCmd + 'DBCC SHRINKFILE (' + name + ' ,0, TRUNCATEONLY);'
FROM sys.database_files
--PRINT @sqlCmd
EXEC (@sqlCmd)

Espero que esto os haya resultado útil y hayáis podido eliminar esa alerta de espacio que tan molesta resulta.

¿Todavía tienes dudas?

Si este post no ha solucionado tu problema o todavía tienes dudas sobre este o cualquier otro asunto relacionado con SQL Server, puedes enviarme un mensaje o solicitar una consultoría gratuita haciendo clic aquí.