Reducción de la base de datos tempdb

  • Artículo

Se aplica a:SQL ServerAzure SQL Managed Instance

En este artículo se describen varios métodos que puede usar para reducir la base de datos tempdb en SQL Server.

Puede usar cualquiera de los métodos siguientes para modificar el tamaño de tempdb. Las tres primeras opciones se describen en este artículo. Si desea usar SQL Server Management Studio, siga las instrucciones de Reducción de una base de datos.

Method ¿Requiere el reinicio? Más información
ALTER DATABASE Sí Proporciona control completo sobre el tamaño de los archivos predeterminados tempdb (tempdev y templog).
DBCC SHRINKDATABASE No Funciona en el nivel de base de datos.
DBCC SHRINKFILE No Permite reducir archivos individuales.
SQL Server Management Studio No Reduzca los archivos de base de datos a través de una interfaz gráfica de usuario.

Comentarios

De forma predeterminada, la base de datos tempdb está configurada para el crecimiento automático según sea necesario. Por lo tanto, esta base de datos puede crecer inesperadamente a un tamaño mayor que el tamaño deseado. Los tamaños de base de datos mayores tempdb no afectarán negativamente al rendimiento de SQL Server.

Cuando se inicia SQL Server, se vuelve a crear tempdb mediante una copia de la base de datos model y se restablece tempdb a su último tamaño configurado. El tamaño configurado es el último tamaño explícito que se estableció mediante una operación de cambio de tamaño de archivo, como ALTER DATABASE que usa la opción MODIFY FILE o las instrucciones DBCC SHRINKFILE o DBCC SHRINKDATABASE. Por lo tanto, a menos que tenga que usar valores diferentes u obtener una resolución inmediata a una base de datos grande tempdb, puede esperar al siguiente reinicio del servicio SQL Server para que el tamaño disminuya.

Puede reducir tempdb mientras la actividad tempdb está en curso. Sin embargo, puede encontrar otros errores, como bloqueos, interbloqueos, etc., que pueden impedir que la reducción se complete. Por lo tanto, para asegurarse de que una reducción de tempdb se realiza correctamente, se recomienda hacerlo mientras el servidor está en modo de usuario único o cuando se ha detenido toda la actividad tempdb.

SQL Server registra solo suficiente información en el registro de transacciones tempdb para revertir una transacción, pero no para rehacer transacciones durante la recuperación de la base de datos. Esta característica aumenta el rendimiento de las instrucciones INSERT en tempdb. Además, no tiene que registrar información para rehacer ninguna transacción porque tempdb se vuelve a crear cada vez que reinicie SQL Server. Por lo tanto, no tiene transacciones para poner al día o revertir.

Para obtener más información sobre cómo administrar y supervisar tempdb, consulte Planeamiento de capacidad y Supervisión del uso de tempdb.

Uso del comando ALTER DATABASE

Nota:

Este comando solo funciona en los archivos lógicos tempdb predeterminados tempdev y templog. Si se agregan más archivos a tempdb, puede reducirlos después de reiniciar SQL Server como servicio. Todos los archivos tempdb se vuelven a crear durante el inicio. Sin embargo, están vacíos y se pueden quitar. Para quitar archivos adicionales en tempdb, use el comando ALTER DATABASE con la opción REMOVE FILE.

Este método requiere reiniciar SQL Server.

  1. Detenga SQL Server.

  2. En un símbolo del sistema, inicie la instancia en modo de configuración mínimo. Para ello, siga estos pasos:

    1. En un símbolo del sistema, cambie a la carpeta donde está instalado SQL Server (reemplace <VersionNumber> y <InstanceName> en el ejemplo siguiente):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn

    2. Si la instancia es una instancia con nombre de SQL Server, ejecute el siguiente comando (reemplace <InstanceName> en el ejemplo siguiente):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD

    3. Si la instancia es la instancia predeterminada de SQL Server, ejecute el siguiente comando:

      sqlservr -c -f -mSQLCMD

      Nota:

      Los parámetros -c y -f hacen que SQL Server se inicie en un modo de configuración mínimo que tenga un tamaño tempdb de 1 MB para el archivo de datos y 0,5 MB para el archivo de registro. El parámetro -mSQLCMD impide que cualquier otra aplicación que no sea sqlcmd tome el control de la conexión de usuario único.

  3. Conéctese a la instancia de SQL Server con sqlcmd y luego, ejecute los siguientes comandos de Transact-SQL. Reemplace <target_size_in_MB> por el tamaño deseado:

    ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = <target_size_in_MB>);

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = <target_size_in_MB>);

  4. Detenga SQL Server. Para ello, presione Ctrl+C en la ventana del símbolo del sistema, reinicie SQL Server como servicio y compruebe el tamaño de los archivos tempdb.mdf y templog.ldf.

Uso del comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE recibe el parámetro target_percent. Este es el porcentaje de espacio disponible que se desea dejar en el archivo de la base de datos después de reducir la base de datos. Si usa DBCC SHRINKDATABASE, es posible que tenga que reiniciar SQL Server.

  1. Determine el espacio que se usa actualmente en tempdb mediante el procedimiento almacenado sp_spaceused. A continuación, calcule el porcentaje de espacio disponible que se deja para su uso como parámetro para DBCC SHRINKDATABASE. Este cálculo se basa en el tamaño de la base de datos deseada.

    Nota:

    En algunos casos, es posible que tenga que ejecutar sp_spaceused @updateusage = true para recalcular el espacio que se usa y para obtener un informe actualizado. Para obtener más información, vea sp_spaceused (Transact-SQL).

    Considere el ejemplo siguiente:

    Supongamos que tempdb tiene dos archivos: el archivo de datos principal (tempdb.mdf) que es de 1024 MB y el archivo de registro (tempdb.ldf) que es de 360 MB. Supongamos que sp_spaceused informa de que el archivo de datos principal contiene 600 MB de datos. Además, supongamos que desea reducir el archivo de datos principal a 800 MB. Calcule el porcentaje deseado de espacio disponbile que queda después de la reducción: 800 MB - 600 MB = 200 MB. Ahora, divida 200 MB en 800 MB = 25 por ciento, y es su target_percent. El archivo de registro de transacciones se reduce en consecuencia, lo que deja un 25 % o 200 MB de espacio libre después de reducir la base de datos.

  2. Conéctese a SQL Server con SQL Server Management Studio, Azure Data Studio o sqlcmd y, a continuación, ejecute el siguiente comando de Transact-SQL. Reemplace <target_percent> por el porcentaje deseado:

    DBCC SHRINKDATABASE (tempdb, '<target_percent>');

Hay limitaciones con el comando DBCC SHRINKDATABASE en tempdb. El tamaño de destino de los archivos de datos y de registro no puede ser menor que el tamaño especificado cuando se creó la base de datos, o menor que el último tamaño que se estableció explícitamente mediante una operación de cambio de tamaño de archivo, como ALTER DATABASE, que usa la opción MODIFY FILE. Otra limitación de DBCC SHRINKDATABASE es el cálculo del parámetro target_percentage y su dependencia del espacio actual que se usa.

Uso del comando DBCC SHRINKFILE

Use el comando DBCC SHRINKFILE para reducir los archivos individuales tempdb. DBCC SHRINKFILE proporciona más flexibilidad que DBCC SHRINKDATABASE porque puede usarla en un archivo de base de datos única sin afectar a otros archivos que pertenecen a la misma base de datos. DBCC SHRINKFILE recibe el parámetro target_size. Este es el tamaño final deseado para el archivo de base de datos.

  1. Determine el tamaño deseado para el archivo de datos principal (tempdb.mdf), el archivo de registro (templog.ldf) y los archivos adicionales que se agregan a tempdb. Asegúrese de que el espacio que se usa en los archivos sea menor o igual que el tamaño de destino deseado.

  2. Conéctese a SQL Server con SQL Server Management Studio, Azure Data Studio o sqlcmd y, a continuación, ejecute el siguiente comando de Transact-SQL para los archivos de base de datos específicos que quiere reducir. Reemplace <target_size_in_MB> por el tamaño deseado:

    USE tempdb;
GO

-- This command shrinks the primary data file
DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
GO

-- This command shrinks the log file, examine the last paragraph.
DBCC SHRINKFILE (templog, '<target_size_in_MB>');
GO

Una ventaja de DBCC SHRINKFILE es que puede reducir el tamaño de un archivo a un tamaño menor que su tamaño original. Puede emitir DBCC SHRINKFILE en cualquiera de los archivos de datos o de registro. No puede hacer que la base de datos sea menor que el tamaño de la base de datos model.

Error 8909 al ejecutar operaciones de reducción

Si tempdb se usa y, si intenta reducirlo mediante los comandos DBCC SHRINKDATABASE o DBCC SHRINKFILE, puede recibir mensajes similares a los siguientes, en función de la versión de SQL Server que use:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Este error no indica ningún daño real en tempdb. Sin embargo, puede haber otras razones para errores de datos físicos dañados, como el error 8909, y que esas razones incluyen problemas del subsistema de E/S. Por lo tanto, si el error se produce fuera de las operaciones de reducción, debe realizar más investigación.

Aunque se devuelve un mensaje 8909 a la aplicación o al usuario que ejecuta la operación de reducción, no se producirá un error en las operaciones de reducción.

Consulte también

Pasos siguientes