Banco de dados tempdb

O banco de dados do sistema tempdb é um recurso global que está disponível para todos os usuários conectados à instância do SQL Server e é usado para conter o seguinte:

Objetos de usuário temporários criados explicitamente, como: tabelas temporárias globais ou locais, procedimentos armazenados temporários, variáveis de tabela ou cursores.
Objetos internos criados pelo Mecanismo de Banco de Dados do SQL Server, por exemplo, tabelas de trabalho para armazenar resultados intermediários para spools ou ordenação.
Versões de linha geradas por transações de modificação de dados em um banco de dados que utiliza isolamento de leitura confirmada com controle de versão de linha ou transações de isolamento por instantâneo.
Versões de linha geradas por transações de modificação de dados para recursos, tais como: operações de índice online, MARS (Conjuntos de Resultados Ativos Múltiplos) e gatilhos AFTER.

As operações no tempdb são minimamente registradas. Isso permite que as transações sejam revertidas. tempdb é recriado sempre que o SQL Server é iniciado para que o sistema sempre comece com uma cópia limpa do banco de dados. Tabelas temporárias e procedimentos armazenados são descartados automaticamente ao desconectar e nenhuma conexão está ativa quando o sistema é desligado. Portanto, nunca há nada no tempdb a ser salvo de uma sessão do SQL Server para outra. As operações de backup e restauração não são permitidas no tempdb.

Propriedades físicas do tempdb

A tabela a seguir lista os valores de configuração iniciais dos arquivos de log e dados tempdb . Os tamanhos desses arquivos podem variar ligeiramente para diferentes edições do SQL Server.

Arquivo	Nome lógico	Nome físico	Crescimento de arquivos
Dados primários	tempdev	tempdb.mdf	Crescimento automático em 10% até que o disco esteja cheio
Registro	templog	templog.ldf	Crescimento automático de 10% para um máximo de 2 terabytes

O tamanho do tempdb pode afetar o desempenho de um sistema. Por exemplo, se o tamanho do tempdb for muito pequeno, o processamento do sistema poderá estar muito ocupado com a criação automática do banco de dados para dar suporte ao requisito de carga de trabalho sempre que você iniciar o SQL Server. Você pode evitar essa sobrecarga aumentando o tamanho do tempdb.

Melhorias de desempenho no tempdb

No SQL Server, o desempenho do tempdb é aprimorado das seguintes maneiras:

Tabelas temporárias e variáveis de tabela podem ser armazenadas em cache. O cache permite que as operações que descartam e criam objetos temporários sejam executadas muito rapidamente e reduz a contenção de alocação de páginas.
O protocolo de bloqueio de página de alocação foi aprimorado. Isso reduz o número de travas UP (atualização) usadas.
A sobrecarga de log para tempdb é reduzida. Isso reduz o consumo de largura de banda de E/S do disco no arquivo de log tempdb .
O algoritmo para alocar páginas mistas no tempdb é aprimorado.

Movendo os arquivos de dados e log do tempdb

Para mover os arquivos de dados e de log do tempdb, consulte Mover Bancos de Dados do Sistema.

Opções de banco de dados

A tabela a seguir lista o valor padrão para cada opção de banco de dados no banco de dados tempdb e se a opção pode ser modificada. Para exibir as configurações atuais dessas opções, use a exibição do catálogo sys.databases .

Opção de banco de dados	Valor padrão	Pode ser modificado
Permitir isolamento de snapshot	DESLIGADO	Sim
ANSI_NULL_DEFAULT	DESLIGADO	Sim
ANSI_NULLS	DESLIGADO	Sim
ANSI_PADDING	DESLIGADO	Sim
ANSI_WARNINGS	DESLIGADO	Sim
ARITHABORT	DESLIGADO	Sim
FECHAMENTO_AUTOMÁTICO	DESLIGADO	Não
CRIAÇÃO_AUTOMÁTICA_DE_ESTATÍSTICAS	LIGADO	Sim
Encolhimento Automático	DESLIGADO	Não
ATUALIZAÇÃO AUTOMÁTICA DE ESTATÍSTICAS	LIGADO	Sim
AUTO_UPDATE_STATISTICS_ASYNC	DESLIGADO	Sim
RASTREAMENTO_DE_ALTERAÇÕES	DESLIGADO	Não
CONCAT_NULL_YIELDS_NULL	DESLIGADO	Sim
CURSOR_CLOSE_ON_COMMIT	DESLIGADO	Sim
CURSOR_DEFAULT	GLOBAL	Sim
Opções de disponibilidade do banco de dados	ONLINE MULTI_USUÁRIO LEITURA/ESCRITA	Não Não Não
DATA_CORRELAÇÃO_OTIMIZAÇÃO	DESLIGADO	Sim
DB_CHAINING	LIGADO	Não
ENCRIPTAÇÃO	DESLIGADO	Não
ARREDONDAMENTO_NUMÉRICO_ABORTAR	DESLIGADO	Sim
VERIFICAÇÃO_DE_PÁGINA	CHECKSUM para novas instalações do SQL Server. Nenhum para melhorias do SQL Server.	Sim
PARAMETRIZAÇÃO	SIMPLES	Sim
IDENTIFICADOR_COM_COTAS	DESLIGADO	Sim
READ_COMMITTED_SNAPSHOT	DESLIGADO	Não
RECUPERAÇÃO	SIMPLES	Não
GATILHOS_RECURSIVOS	DESLIGADO	Sim
Opções do Service Broker	ENABLE_BROKER	Sim
CONFIÁVEL	DESLIGADO	Não

Para obter uma descrição dessas opções de banco de dados, consulte Alter DATABASE SET Options (Transact-SQL).

Restrições

As seguintes operações não podem ser executadas no banco de dados tempdb :

Adicionando grupos de arquivos.
Fazendo backup ou restaurando o banco de dados.
Alterando a ordenação. A ordenação padrão é a ordenação do servidor.
Alterando o proprietário do banco de dados. tempdb pertence a sa.
Criando um instantâneo de banco de dados.
Descartando o banco de dados.
Removendo o usuário convidado do banco de dados.
Habilitando a captura de dados de alteração.
Participando do espelhamento de banco de dados.
Removendo o grupo de arquivos primário, o arquivo de dados primário ou o arquivo de log.
Renomeando o banco de dados ou o grupo de arquivos primário.
Executando DBCC CHECKALLOC.
Executando DBCC CHECKCATALOG.
Definindo o banco de dados como OFFLINE.
Definindo o banco de dados ou o grupo de arquivos primário como READ_ONLY.

Permissões

Qualquer usuário pode criar objetos temporários no tempdb. Os usuários só podem acessar seus próprios objetos, a menos que recebam permissões adicionais. É possível revogar a permissão de conexão para tempdb para impedir que um usuário use tempdb, mas isso não é recomendado, pois algumas operações de rotina exigem o uso do tempdb.

Opção SORT_IN_TEMPDB para índices

Bancos de dados do sistema

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Mover arquivos de banco de dados