Solucionar problemas de espacio en disco insuficiente en tempdb

Este tema contiene procedimientos y recomendaciones para ayudarle a diagnosticar y solucionar problemas que se producen porque no hay espacio de disco suficiente en la base de datos tempdb. Si se agota el espacio de disco de tempdb, se pueden producir interrupciones importantes en el entorno de producción de SQL Server y es posible que las aplicaciones en ejecución no puedan finalizar sus operaciones.

Requisitos de espacio de tempdb

La base de datos del sistema tempdb es un recurso global a disposición de todos los usuarios conectados a una instancia de SQL Server. La base de datos tempdb se utiliza para almacenar los siguientes objetos: objetos de usuario, objetos internos y almacenes de versiones. En SQL Server 2005, tempdb requiere más espacio en disco que en versiones anteriores de SQL Server. Para obtener más información, vea Planeamiento de capacidad para tempdb.

Puede utilizar la vista de administración dinámica sys.dm_db_file_space_usage para supervisar el espacio en disco que utilizan los objetos de usuario, los objetos internos y los almacenes de versiones en los archivos de tempdb. Asimismo, para supervisar la actividad de asignación o desasignación de páginas en tempdb en el nivel de sesión o tarea, se pueden utilizar las vistas de administración dinámica sys.dm_db_session_space_usage y sys.dm_db_task_space_usage. Estas vistas pueden utilizarse para identificar consultas grandes, tablas temporales o variables de tabla que utilizan mucho espacio de disco de tempdb.

Diagnosticar problemas de espacio de disco de tempdb

En la tabla siguiente se muestran los mensajes de error que indican que no hay espacio de disco suficiente en la base de datos tempdb. Estos errores se pueden encontrar en el registro de errores de SQL Server y también pueden ser devueltos por cualquier aplicación que se esté ejecutando.

Los problemas de espacio de disco de tempdb también se indican cuando se establece la opción de crecimiento automático de la base de datos y su tamaño aumenta rápidamente.

Supervisar el espacio de disco de tempdb

En los ejemplos siguientes se muestra cómo determinar la cantidad de espacio disponible en tempdb y el espacio utilizado por el almacén de versiones y los objetos internos y de usuario.

Determinar la cantidad de espacio libre en tempdb

La consulta siguiente devuelve el número total de páginas libres y el espacio libre total, en megabytes (MB), que están disponibles en todos los archivos de tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Determinar la cantidad de espacio utilizado por el almacén de versiones

La consulta siguiente devuelve el número total de páginas utilizadas por el almacén de versiones y el espacio total, en MB, utilizado por el almacén de versiones en tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Determinar la transacción que más tarda en ejecutarse

Si el almacén de versiones utiliza mucho espacio de tempdb, será necesario determinar cuál es la transacción que más tarda en ejecutarse. Utilice esta consulta para mostrar las transacciones activas ordenadas por transacción de mayor duración.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Una transacción de larga duración que no está relacionada con una operación de índices en línea requiere un almacén de versiones grande. Este almacén de versiones mantiene todas las versiones generadas desde que se inició la transacción. Las transacciones de generación de índices en línea pueden tardar mucho en finalizar, pero se utiliza un almacén de versiones independiente, dedicado a las operaciones de índices en línea. Por tanto, estas operaciones no impiden que se quiten las versiones de otras transacciones. Para obtener más información, vea Uso de recursos del control de versiones de filas.

Determinar la cantidad de espacio utilizado por objetos internos

La consulta siguiente devuelve el número total de páginas utilizadas por los objetos internos y el espacio total, en MB, utilizado por los objetos internos en tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Determinar la cantidad de espacio utilizado por objetos de usuario

La consulta siguiente devuelve el número total de páginas utilizadas por los objetos de usuario y el espacio total utilizado por los objetos de usuario en tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Determinar la cantidad total de espacio (libre y utilizado)

La consulta siguiente devuelve la cantidad total de espacio de disco utilizado por todos los archivos de tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Supervisar el espacio utilizado por las consultas

Uno de los problemas más habituales relacionados con el uso del espacio de tempdb tiene que ver con las consultas grandes, que utilizan una gran cantidad de espacio. Por lo general, este espacio se utiliza para los objetos internos, como las tablas o archivos de trabajo. Aunque la supervisión del espacio utilizado por los objetos internos indica cuánto espacio se utiliza, no identifica directamente la consulta que utiliza ese espacio.

Los métodos siguientes ayudan a identificar las consultas que utilizan la mayor parte del espacio de tempdb. El primer método examina los datos de nivel de lote y hace un uso menos intensivo de los datos que el segundo método. El segundo método se puede utilizar para identificar la consulta, la tabla temporal o la variable de tabla específica que está utilizando el espacio del disco, pero deben recopilarse más datos para obtener la respuesta.

Método 1: información de nivel de lote

Si la solicitud de lote contiene pocas consultas y sólo una de ellas es una consulta compleja, esta información suele ser suficiente para saber qué lote, y no qué consulta específica, está utilizando el espacio.

Para continuar con este método, es necesario configurar un trabajo del Agente SQL Server para el sondeo de las vistas de administración dinámica sys.dm_db_session_space_usage y sys.dm_db_task_space_usage utilizando un intervalo de sondeo de pocos minutos. En el ejemplo siguiente se emplea un intervalo de sondeo de tres minutos. Es necesario sondear ambas vistas porque sys.dm_db_session_space_usage no incluye la actividad de asignación de la tarea activa actual. Al comparar la diferencia entre las páginas asignadas en dos intervalos de tiempo se puede calcular cuántas páginas se asignan entre un intervalo y otro.

En los ejemplos siguientes se proporcionan las consultas necesarias para el trabajo del Agente SQL Server.

A. Obtener el espacio utilizado por los objetos internos en todas las tareas que actualmente se ejecutan en cada sesión.

En el ejemplo siguiente se crea la vista all_task_usage. Cuando se consulta, la vista devuelve el espacio total utilizado por los objetos internos en todas las tareas que actualmente se ejecutan en tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

B. Obtener el espacio utilizado por los objetos internos para las tareas que actualmente se ejecutan y las tareas finalizadas en la sesión actual.

En el ejemplo siguiente se crea la vista all_session_usage. Cuando se consulta, la vista devuelve el espacio utilizado por los objetos internos para las tareas finalizadas y en ejecución en tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Se asume que, cuando se consultan estas vistas en un intervalo de tres minutos, los conjuntos de resultados proporcionan la información siguiente.

• A las 5:00 p.m., la sesión 71 asignó 100 páginas y canceló la asignación de 100 páginas desde el comienzo de la sesión.
• A las 5:03 p.m., la sesión 71 asignó 20100 páginas y canceló la asignación de 100 páginas desde el comienzo de la sesión.

Al analizar esta información, puede afirmar que entre las dos medidas: la sesión ha asignado 20.000 páginas para objetos internos y no ha desasignado ninguna página. Esto es indicativo de un posible problema.

[!NOTA] Como administrador de la base de datos, puede decidir sondear con mayor frecuencia que tres minutos. Sin embargo, si una consulta se ejecuta durante menos de tres minutos, posiblemente no utilizará una cantidad de espacio de tempdb importante.

Para determinar el lote que se está ejecutando en ese momento, utilice el Analizador de SQL Server para capturar las clases de eventos RPC:Completed y SQL:BatchCompleted.

Una alternativa a utilizar el Analizador de SQL Server es ejecutar DBCC INPUTBUFFER una vez cada tres minutos para todas las sesiones, como se muestra en el ejemplo siguiente.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Método 2: información de nivel de consulta

En ocasiones, no basta ver el búfer de entrada o el evento SQL:BatchCompleted del Analizador de SQL Server para saber qué consulta está utilizando la mayor parte del espacio de disco de tempdb. Puede utilizar los métodos siguientes para obtener esta respuesta, pero éstos requieren la recopilación de un número mayor de datos que los procedimientos descritos en el método 1.

Para continuar con este método, configure un trabajo del Agente SQL Server que sondee la vista de administración dinámica sys.dm_db_task_space_usage. El intervalo de sondeo debería ser más corto que el del método 1 (por ejemplo, una vez cada minuto), ya que sys.dm_db_task_space_usage no devuelve datos si la consulta (tarea) no se está ejecutando en ese momento.

En la consulta de sondeo, la vista definida en la vista de administración dinámica sys.dm_db_task_space_usage se combina con sys.dm_exec_requests para devolver las columnas sql_handle, statement_start_offset, statement_end_offset y plan_handle.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

Si el plan de consulta está almacenado en caché, se puede recuperar el texto de Transact-SQL de la consulta y el plan de ejecución de consultas en el formato de los planes de presentación XML cuando se desee. Para obtener el texto de Transact-SQL de la consulta que se ejecuta, utilice el valor sql_handle y la función de administración dinámica sys.dm_exec_sql_text. Para obtener el plan de ejecución de consultas, use el valor plan_handle y la función de administración dinámica sys.dm_exec_query_plan.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

Si el plan de consulta no está almacenado en caché, puede utilizar uno de los métodos siguientes para obtener el texto de Transact-SQL de la consulta y el plan de ejecución de consultas.

A. Utilizar el método de sondeo

Sondee la vista all_query_usage y ejecute la consulta siguiente para obtener el texto de la consulta:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Dado que sql_handle debería ser único para cada lote único, no es necesario guardar entradas duplicadas de sql_handle.

Para guardar el identificador del plan y el plan XML, ejecute la consulta siguiente.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Utilizar los eventos del Analizador de SQL Server

Como alternativa a sondear las funciones sys.dm_exec_sql_text y sys.dm_exec_query_plan, puede utilizar los eventos del Analizador de SQL Server. Algunos eventos del analizador se pueden utilizar para capturar el plan de consulta y el texto de la consulta que se genera. Por ejemplo, el evento 165 devuelve estadísticas de rendimiento de traza, texto SQL, planes de consulta y estadísticas de consulta.

Supervisar el espacio utilizado por tablas temporales y variables de tabla

Para supervisar el espacio utilizado por las tablas temporales y las variables temporales se puede utilizar un método similar al sondeo de consultas. Las aplicaciones que adquieren una gran cantidad de datos de usuario de tablas o variables temporales pueden generar problemas con el uso del espacio de tempdb. Estas tablas o variables pertenecen a los objetos de usuario. Puede utilizar las columnas user_objects_alloc_page_count y user_objects_dealloc_page_count de la vista de administración dinámica sys.dm_db_session_space_usage y realizar los métodos descritos anteriormente.

Supervisar la asignación y desasignación de páginas por sesión

En la tabla siguiente se muestran los resultados devueltos por las vistas de administración dinámica sys.dm_db_file_space_usage, sys.dm_db_session_space_usage y sys.dm_db_task_space_usage para una sesión específica. Cada fila representa una actividad de asignación o desasignación en tempdb para una sesión específica. La actividad se muestra en la columna Evento. Las demás columnas muestran los valores que se devolverían en las columnas de las vistas de administración dinámica.

En este escenario se asume que la base de datos tempdb comienza con 872 páginas de extensiones sin asignar y 100 páginas de extensiones reservadas para objetos de usuario. La sesión asigna 10 páginas para una tabla de usuario y, después, cancela la asignación de todas ellas. Las 8 primeras páginas se encuentran en una extensión mixta. Las 2 páginas restantes se encuentran en una extensión uniforme.

Vea también

Conceptos

Optimizar el rendimiento de tempdb
Base de datos tempdb
Planeamiento de capacidad para tempdb

Otros recursos

Solucionar problemas con el motor de base de datos

Ayuda e información

Obtener ayuda sobre SQL Server 2005