SQL Server Shrinking Tempdb- Script and Troubleshooting

Às vezes, pode ser necessário espaço adicional no banco de dados tempdb para acomodar operações como uma inserção em massa ou manutenção de índices. Outro motivo comum para precisar reduzir o tamanho do tempdb é usar temporariamente o espaço em disco adicional para outra tarefa (como mover ou escrever um arquivo de backup, por exemplo). Como essa não é uma atividade regular, você pode reduzir os arquivos tempdb de volta a um tamanho apropriado após a conclusão do trabalho. Ao contrário dos arquivos de dados do banco de dados do usuário, reduzir os arquivos de dados do tempdb não causará fragmentação ou problemas de desempenho, pois ele armazena apenas objetos temporários e não dados reais.

O código abaixo é um exemplo de redução de um arquivo de dados do tempdb. Você precisará alterar o nome lógico, que pode ser encontrado nas propriedades do banco de dados, e o tamanho para o qual deseja reduzi-lo em megabytes. Tenha cuidado e observe que você deve reduzir cada arquivo de dados do tempdb para o mesmo tamanho para obter o melhor desempenho. Por padrão, há 1 arquivo de dados, mas a prática recomendada sugere que deve haver 1 arquivo de dados por núcleo de CPU, até 8. (Seu Milage Pode Variar)

Exemplo de Script de Redução para Todos os 4 Arquivos de Dados do Meu tempdb:

USE [tempdb]

GO

DBCC SHRINKFILE )(N'tempdev', 6000)

GO

DBCC SHRINKFILE )(N'temp2', 6000)

GO

DBCC SHRINKFILE )(N'temp3', 6000)

GO

DBCC SHRINKFILE )(N'temp4', 6000)

GO

Detect Tempdb Files:

SELECT name, physical_name, ((size*8)/1024) AS [Size in MB] FROM sys.master_files WHERE database_id = 2 ORDER BY 'name';

Tempdb Properties:

Para uma solução mais rápida, abaixo está um script personalizado que detecta todos os arquivos de dados do tempdb e cria as declarações de redução para você executar na coluna 'mensagens'. Você precisará alterar o valor do tamanho para o qual deseja reduzir os arquivos de dados.

Shrink All Tempdb Datafiles Script:

---Change the size in MB to shrink to---

DECLARE @size NVARCHAR(10) = 1024

----------------------------------------

DECLARE @info nvarchar(max)

DECLARE @file nvarchar(max)

DECLARE @q1 nvarchar(max)

DECLARE tempdb_cursor cursor for

SELECT NAME FROM sys.master_files WHERE database_id = 2 AND NAME !='templog';

OPEN tempdb_cursor

FETCH NEXT FROM tempdb_cursor into @info

while @@fetch_status = 0

BEGIN

SET @info = @info

SET @q1 = 'USE [tempdb] DBCC SHRINKFILE (''' + @info + ''' , ' + @size + ')'

--EXEC @Q1

PRINT @q1

FETCH NEXT FROM tempdb_cursor

INTO @info

END

CLOSE tempdb_cursor;

DEALLOCATE tempdb_cursor;

Se você encontrar um erro semelhante ao abaixo, então há um bloqueio em um objeto tempdb que não será liberado e impedirá que você reduza os arquivos ainda mais.

Existem 3 soluções possíveis para isso, mas eu recomendo que você proceda com cautela antes de tentá-las, pois todas podem ter consequências.

Encontre o processo usando tempdb e o encerre. Certifique-se de verificar qual é o processo e seu nível de importância antes de interrompê-lo.

Execute DBCC FREEPROCCACHE. Isso geralmente não é recomendado, pois esvazia o cache de planos que o SQL construiu e fará com que todas as consultas sejam executadas mais lentamente do que o normal até que o cache seja reconstruído. No entanto, também pode liberar o banco de dados tempdb para permitir a redução.

Se as duas últimas opções não funcionarem, reiniciar a instância do SQL deve resolver o problema. Isso, é claro, encerrará todas as conexões e interromperá quaisquer trabalhos em execução, mas garante que o tempdb seja acessível, pois esse banco de dados é recriado a cada inicialização da instância.

Obrigado por ler! Espero que esta postagem ajude você a obter o espaço extra necessário no seu banco de dados tempdb