La base de datos del sistema tempdb es un recurso global disponible para todos los usuarios conectados a la instancia de SQL Server y se utiliza para incluir lo siguiente:
Objetos de usuario temporales creados explícitamente como: tablas temporales locales o globales, procedimientos almacenados temporales, variables de tabla o cursores.
Objetos internos creados por el Motor de base de datos de SQL Server, por ejemplo, tablas de trabajo para almacenar resultados intermedios para colas u ordenación.
Versiones de fila generadas por las transacciones de modificación de datos en una base de datos que utiliza transacciones read-committed que usan transacciones de isolation or snapshot isolation.
Versiones de fila que se generan mediante transacciones de modificación de datos para características de operaciones (creación y reconstrucion) de índice, Multiple Active Result Sets (MARS) y triggers AFTER.
Los objetos internos se crean y se quitan dentro del ámbito de una instrucción.
Las operaciones realizadas en tempdb se registran con un nivel mínimo. Esto permite que se reviertan las transacciones. La base de datos tempdb se vuelve a crear cada vez que se inicia SQL Server, de forma que el sistema siempre se inicia con una copia limpia de la base de datos. Las tablas y los procedimientos almacenados temporales se quitan automáticamente en la desconexión y ninguna conexión permanece activa cuando se cierra el sistema. Por tanto, en la base de datos tempdb no hay nada que deba guardarse de una a otra sesión de SQL Server. No se permite realizar operaciones de copia de seguridad y restauración en tempdb.
Parámetros de tamaño y de crecimiento de la tempdb
El tamaño y la ubicación física de la base de datos tempdb puede afectar al rendimiento de un sistema. Por ejemplo, si el tamaño definido en tempdb es demasiado pequeño, parte de la carga de procesamiento del sistema puede deberse al crecimiento automático de tempdb hasta el tamaño necesario para admitir la carga de trabajo cada vez que se reinicia la instancia de SQL Server. Para evitar esta sobrecarga, aumente el tamaño de los archivos de registro y de datos de tempdb.
Puede ver los parámetros de tamaño y de crecimiento de archivos de los archivos de datos o de registro de tempdb mediante:
SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
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.
Determinar la cantidad de espacio libre en 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 transacción que más tarda en ejecutarse:
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
Dimensionar de forma apropiada la base de datos TEMPDB
Dimensionar de forma apropiada la base de datos TEMPDB, es decir, cambiar el tamaño por defecto de TEMPDB (tamaño inicial de TEMPDB) de tal modo que durante el funcionamiento de la Instancia de SQL Server, no sea necesario que TEMPDB tenga que crecer y tampoco sea necesario reducir TEMPDB
Si no tenemos dimensionada correctamente TEMPDB, se verá obligada a crecer, según lo vaya necesitando, se producirá fragmentación en el fichero de TEMPDB impactando en el rendimiento general de la instancia. Debido a que cada vez que se inicia la Instancia de SQL Server se elimina y vuelve a crear TEMPDB, existe el riesgo de generar fragmentación en el disco utilizado por TEMPDB.
Aumentar el tamaño inicial de TEMPDB a 2GB:
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'tempdev', SIZE = 2097152KB )
GO
Pero muy importante, si no sabemos cuánto puede crecer, no le pongas restricción de tamaño, y menos tan baja. Ponle que crezca automáticamente, de 100 en 100 Mb, hasta que tengas un tamaño fijado basándote en el comportamiento y las necesidades del servidor, dependerá de cómo evolucione, pueden ser 2Gb, 10 o 40. Si lo limitas a 2Gb, en cuanto se alcance ese tamaño, tu servidor empezará a fallarte. Cuando ya tengas un tamaño más establecido, el que sea, entonces ya sí puedes dejarlo fijo, si así lo estimas, aunque previamente a eso tendrás que establecer un sistema de alertas que te permita detectar con suficiente margen de tiempo que esta base de datos está alcanzando su límite de tamaño.
Además de cambiar el tamaño inicial de TEMPDB es muy recomendable aumentar el número de ficheros de TEMPDB, a poder ser un fichero por cada CPU, con el objetivo de maximizar la afinidad de CPU, esto es facilitar que puedan paralelizarse operaciones de entrada/salida (IOs) y así obtener una mejora de rendimiento. Claro está, que si cada fichero pudiese estar en un disco diferente, y cada uno de estos discos se accediese a través de un camino (path) de fibra distinto (es decir, diferentes puertos de fibra de las HBAs), estaríamos facilitando al máximo la optimización del acceso a disco de TEMPDB. Así, para añadir ficheros a TEMPDB es suficiente con ejecutar un comando ALTER DATABASE tempdb ADD FILE:
USE master
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev02', FILENAME = N'F:\DATA\tempdb02.ndf', SIZE = 2097152KB, FILEGROWTH = 10% )
GO
Si tenemos una máquina con 8 CPUs y necesitamos 8 GB de TEMPDB, la recomendación que deberíamos seguir es configurar TEMPDB con 8 ficheros de datos, cada uno de ellos con un tamaño inicial de 1GB.
Si TEMPDB no está correctamente dimensionado, puede ocurrir que empiece a crecer incluso tomando todo el espacio libre del disco.
Mover TEMPDB de un disco a otro:
Aquí explico como mover la base de datos tempdb de un disco a otro, este ejemplo solo aplica para la base de datos tempdb, si se quieren mover bases de datos de usuario utilice sp_detach_db y sp_attach_db.
Extraiga la ubicación física de sus archivos de la base de datos tempdb:
USE tempdb
GO
EXEC sp_helpfile
GO
El nombre lógico de cada archivo es el contenido en la columna NAME.
Cambie la ubicación de los archivos de datos y del log de trasacciones, usando la sentencia ALTER DATABASE:
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf')
GO
Debe recibir los mensajes siguientes como confirmación del cambio:
Archivo 'tempdev' modificado en sysaltfiles. Elimine el archivo antiguo después de reiniciar SQL Server.
Archivo 'templog' modificado en sysaltfiles. Elimine el archivo antiguo después de reiniciar SQL Server
Detenga e Inicie el servicio de SQLServer.
Reducir TEMPDB:
Si TEMPDB ha aumentado de tamaño desde que se inició la Instancia de SQL Server, la forma más sencilla de recuperar el espacio utilizado por TEMPDB es reiniciar la Instancia de SQL Server. Al detener SQL Server se mantendrán los ficheros de TEMPDB, por lo que todavía no habremos ganado espacio libre en disco. Sin embargo, al iniciar la Instancia de SQL Server, se elimina TEMPDB y se vuelve a crear de nuevo, recuperando, el espacio libre que estaba ocupando de más. El principal inconveniente, es debido a que implica cortar el servicio a los usuarios.
También es posible intentar utilizar los comandos DBCC SHRINKDATABASE y DBCC SHRINKFILE, pero ejecutar estos comandos sobre TEMPDB con actividad en TEMPDB puede implicar que no se pueda reducir TEMPDB o bien pueden producirse errores que tampoco permitan reducir TEMPDB con éxito. Además, mientras se está reduciendo SHRINK TEMPDB es posible que se generen bloqueos sobre otras transacciones que necesiten acceder a TEMPDB.
existe el truco de ejecutar el comando DBCC FREEPROCCACHE, de tal modo que al vaciarse la caché de procedimientos (la zona de memoria en la que se almacenan los Planes de Ejecución para su reutilización) estamos eliminando de forma implícita las Tablas de Trabajo (Worktables) asociadas a dichos Planes de Ejecución. Esto reduciría el tamaño de la TEMPDB, pero solo se ha de hacer en un estado crítico, cuando se ha producido el llenado de disco, y es imposible reiniciar la instancia de SQL por necesidad e una obligada alta disponibilidad de la aplicación, prefiriendo una bajada de rendimiento temporal a tener que reiniciar al instancia de SQL.
Si tempdb está en uso e intenta comprimirlo mediante los comandos DBCC SHRINKDATABASE o DBCC SHRINKFILE, pueden mostrarse múltiples errores de consistencia similares a los siguientes y la operación de compresión no se ejecutará correctamente:
Servidor: Msj 2501, Nivel 16, Estado 1, Línea 1 No se encuentra la tabla '1525580473'. Compruebe sysobjects.
O bien
Servidor: Msj 8909, Nivel 16, Estado 1, Línea 0 Tabla dañada: Id. de objeto 1, Id. de índice 0, Id. de página %S_PGID. Valor de PageId en el encabezado de la página = %S_PGID.
Aunque es posible que el error 2501 no indique daños en tempdb, hace que la operación de compresión no se ejecute correctamente. Por otra parte, el error 8909 podría indicar daños en la base de datos tempdb. Reinicie SQL Server para volver a crear tempdb y limpiar los errores de consistencia. No obstante, tenga en cuenta que los errores de daños físicos en los datos, por ejemplo el error 8909, pueden deberse a otros motivos, entre los que se incluyen problemas del subsistema de entrada y salida.
TEMPDB y la creación de índices:
Cuando crea o vuelve a generar un índice, si establece la opción SORT_IN_TEMPDB en ON, puede indicar al motor de base de datos de SQL Server, que utilice tempdb para almacenar los resultados de ordenación intermedios que se utilizan para generar el índice. Aunque esta opción aumenta la cantidad de espacio en disco temporal utilizado para crear un índice, reduce el tiempo que tarda en crear o volver a generar un índice cuando tempdb está en un conjunto de discos diferente al de la base de datos de usuario.
Si no se necesita una operación de ordenación o si la ordenación se puede realizar en memoria, la opción SORT_IN_TEMPDB se omite.
El espacio disponible del grupo de archivos de la base de datos de usuario, debe ser lo suficientemente extenso como para almacenar la estructura del índice final. La continuidad de las extensiones del índice se puede mejorar si hay suficiente espacio disponible.
Restricciones en la TEMPDB:
En la base de datos tempdb no se pueden realizar las siguientes operaciones:
Agregar grupos de archivos.
Realizar una copia de seguridad o restaurar la base de datos.
Cambiar intercalaciones. La intercalación predeterminada es la intercalación de servidor.
Cambiar el propietario de la base de datos. tempdb pertenece a dbo.
Crear una instantánea de base de datos.
Eliminar la base de datos.
Eliminar el usuario guest de la base de datos.
Habilitar el mecanismo de captura de cambios en los datos.
Participar en el reflejo de la base de datos.
Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro.
Cambiar el nombre de la base de datos o del grupo de archivos principal.
Ejecutar DBCC CHECKALLOC.
Ejecutar DBCC CHECKCATALOG.
Establecer la base de datos en OFFLINE.
Establecer la base de datos o el grupo de archivos principal en READ_ONLY.
CASO Practico, por posibles errores al modificar la ubicación de los ficheros de la temdb, y posteriormente no arranca la instancia de SQL:
1.- Arrancaremos la instancia en modo de recuperacion, con acceso de usuario unico:
(Dejo las 2 opciones, intancia por defecto o con nombre):
NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename /f /T3608
2.- Modificaremos la ubicación, por la correcta:
ALTER DATABASE TEMPDB MODIFY FILE ( NAME = tempdev, FILENAME = 'E:\MSSQL\DataFiles\<filename.mdf>' )
ALTER DATABASE TEMPDB MODIFY FILE ( NAME = templog, FILENAME = 'F:\MSSQL\LogFiles\<Filename.ldf>' )
3.- Paramos servicio, renombra los ficheros antiguos y vuelve a iniciar el servicio normalmente.
Ya que estamos en hilo, y me parece interesante, añadir que se puede almacenan en la tempdb y la pueden hacer crecer: tablas temporales locales o globales, variables de tabla o cursores, (en lo que atañe a Objetos de usuarios). tambien Objetos internos creados por el Motor de base de datos de SQL Server como por ejemplo, tablas de trabajo para almacenar resultados intermedios para obtener GROUP BY, ORDER BY o UNION de una cosnulta... Tambien hacen crecer la temdb, si se tiene activado en alguna base de datos el versionado de filas (row versioning) o el aislamiento de instantáneas. Así como conjuntos de resultados de múltiples MARS y desencadenadores AFTER. Operaciones de combinación hash o de agregado hash. O incluso Resultados de orden intermedio de operaciones como crear o volver a generar índices (si se ha especificado SORT_IN_TEMPDB
Bases:
http://msdn.microsoft.com/en-us/library/ms175527.aspx
http://msdn.microsoft.com/en-us/library/ms190768.aspx
http://support.microsoft.com/kb/307487
y experiencias propias.
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
