Comprimir TempDB en SQL Server

Debido a situaciones de origen laboral, en la oficina se ha tenido que contar muchas veces con la opción de comprimir la base de datos temporales de sistema que tiene SQL Server (2005-2008).

Encontre dos maneras (lo que indica que no existan más formas) de realizar esa tarea, los describo de seguido.

Método 01: Comprimir y modificar archivo

Según leí, Microsoft desestima comprimir cualquier TempDB que este siendo usada constantemente. Esto es porque "you may receive multiple consistency errors" que podrían no solamente interrumpir actividades de usuarios cuando se da la operación de compresión. Sin embarga esa actividad normalmente es mínima o nula, en todo caso, aquí están los pasos:

1. Corra el comando DBCC SHRINKFILE en cada archivo que usted quiera reducir el tamaño:
   USE TempDB
   GO
   DBCC SHRINKFILE (N'logical_file_name', 5) -- reducir en 5 MB
2. Luego corra ALTER DATABASE, con el tamaño que usted quiere que sean. Esto causará que el nuevo tamaño se registre en master.sys.master_files, la cual es un catalogo de sistema que SQL Server usa para recrear una nueva TempDB en blanco cada vez que una server/instance es reiniciada.
   USE MASTER
   GO
   ALTER DATABASE TEMPDB MODIFY FILE (NAME=' logical_file_name, SIZE=6MB)

Note que hasta que SQL Server es reiniciado (cuando TempDB es recreada) los cambios no mostraran los nuevos valores en la Database Properties o en la Shrink File GUI. Sin embargo, se pueden verificar los cambios inmediatamente con:

SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILESWHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

Método 02: Modificar archivos en modo de usuario restringido

Este método elimina el riesgo de errores de consistencia. Con la salvedad, que tendrás que desconectar a todos los usuarios antes de hacer algún cambio. Sin embargo, si lo planeas bien, se podrá hacer esos cambios en 10 minutos haciendo estos pasos:

1. Establezca una Coenxion Dedicada de Administrador (DAC) en el Management Studio para conectarse. Tan sencillo como anteponiendo "Admin:" en frente de la instacia de nombre. P.E., ADMIN:Rep-Server\Instancia1.

2. Corra:

• ALTER DATABASE with the REMOVE -- opción para marcar los .ndf como obsoletos.
• USE MASTER GO ALTER DATABASE TEMPDB REMOVE FILE logical_file_name

3. Detenga la instancia desde el command prompt. Para la default instance use:

• C:\>NET STOP MSSQLSERVER
• o
• NET STOP "SQL Server (MSSQLSERVER)"
• o para la named instance:
• C:\> NET STOP "SQL Server ( instancename )"
• o
• NET STOP MSSQL$instancename

4. Inicie la instancia en modo restringido con:

• C:\SQL\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -f

5. Mofique la TempDB con el nuevo tamaño inicial para el .mdf y el .ldf desde la conexión dedicada en SSMS (hasta que reinicie SQL Server en modo normal, será la única conexión disponible).

• USE MASTER
• GO
• ALTER DATABASE TEMPDB MODIFY FILE (NAME='logical_file_name', SIZE=6MB)

6. Regrese al command prompt y teclee Ctrl + C para salir del modo restringido (diga si cuando el prompt pregunte si quiere detener SQL Server). Entonces inicie la instacia en modo normal. Para la default instance:

• C:\NET START MSSQLSERVER
• o
• NET STOP "SQL Server (MSSQLSERVER)"

7. Modifique la TempDB con la opción Add File activada con el nuevo tamaño para los archivos .ndf.

• ALTER DATABASE TEMPDB ADD FILE (NAME=logical_file_name, FILENAME='C:\bla\bla\Data\logical_file_name.ndf', SIZE=6MB)

8. Al final corra esta consulta para asegurarse de que todos los cambios fueron realizados apropiadamente en la base de datos Master:

• SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB FROM MASTER.SYS.MASTER_FILES WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

Podemos observar que el primer método es más simple y rápido, pero pero se corre un riesgo inherente de corromper los datos en la TempDB. Además, la operación de compresión puede no terminar debido al uso concurrente de dichos archivos. Ahora bien, el segundo método es más elaborado pero asegura una operación limpia. Sin embargio requiere detener el sistema.
Espero les sirva.