MSSQL – TEMPDB – O que fazer quando receber um alarme por falta de espaço

   Inicio esse post com uma parte do artigo do MSDN explicando o que é uma TEMPDB.

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

• Os 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 por Mecanismo de Banco de Dados do SQL Server, por exemplo, tabelas de trabalho para armazenar resultados intermediários para spool ou classificação.
• Versões de linha geradas por transações de modificação de dados em um banco de dados que usa a leitura de confirmados usando transações de isolação de controle de versão de linha ou isolação de instantâneo.
• As versões de linhas geradas por meio de transações de modificação de dados para recursos como: operações de índice on-line, vários conjuntos de resultados ativos (MARS) e gatilhos AFTER.

   As operações em tempdb são registradas minimamente. Isso permite que transações sejam revertidas. tempdb é recriado cada vez que SQL Server é iniciado, de modo que o sistema sempre começa com uma cópia limpa do banco de dados. As tabelas temporárias e procedimentos armazenados são descartados automaticamente ou desconectados e nenhuma conexão fica ativa quando o sistema é desligado. Portanto, nunca há nada em tempdb a ser gravado de uma sessão de SQL Server para outra. As operações de backup e restauração não são permitidas em tempdb.

  Muitas vezes em determinados momentos, você DBA pode se deparar com o um alarme que o database TEMPDB, que é um recurso de armazenamento global temporário esta próximo de atingir 100% de ocupação.
Isso é um problema, mas que pode ser resolvido com algumas ações:

Passo 1 – Identificar a query ofensora

     A melhor forma de identificar é executando uma query de monitoramento que retorne as informações que você precisa para a analise. Para isso execute no console:

select top 10
t1.session_id,
t1.request_id,
t3.hostname,
t3.loginame,
t3.program_name,
db_name (t3.dbid) as dbname,
t1.task_alloc  * (8.0/1024.0) as Alocado_MB, --qtd de paginas
t1.task_dealloc  * (8.0/1024.0)as Desalocado_MB, --qtd de paginas

    (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                   ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(t2.sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
       from sys.dm_db_task_space_usage
       group by session_id, request_id) as t1,
       sys.dm_exec_requests as t2,
       sys.sysprocesses as t3
where
t3.loginame <> '' and
t1.session_id = t2.session_id and
(t1.request_id = t2.request_id) and
t1.session_id = t3.spid and
      t1.session_id > 50
order by t1.task_alloc DESC

   O importante nesse caso é analisar o que essa query esta fazendo e o quanto está alocando no momento do pico.
Aguarde alguns segundos e execute novamente, e nesse caso, analise o próximo campo que será o quanto esta liberando de espaço. O que isso quer dizer:
Na verdade se a query esta usando muito espaço, mas ao executar novamente verifica-se que o valor livre esta crescendo, quer dizer que essa query já fez o que precisava e gradativamente esta devolvendo esse espaço a TEMPDB. Nesse caso somente aguarde que a TEMPDB será liberada em breve e o alarme vai parar. O ultimo campo não exibido aqui é o plano de execução.
Caso a query continue crescendo e consumindo um espaço exorbitante da TEMPDB a ação correta a tomar é a seguinte:

Passo 2 – Analizar as configurações da TEMPDB

   A TEMPDB está setada com um valor X pré-supondo que é o necessário para o armazenamento necessário das transações.
Para verificar essas informações clique com o botão direito no database TEMPDB > Properties, como mostra a figura abaixo.

   Clique em files, como mostra a figura abaixo:

   O arquivo lógico que precisamos analisar chama-se TEMPLOG, verifique para qual caminho fisico que ele esta apontando, que nesse caso é o *E:* (Não exibido aqui, mas fica ao lado da imagem abaixo) e clique  no botão ao lado das informações de tamanho como mostra a figura a seguir

Nesse momento será exibidos os parâmetros de crescimento do templog, como mostra a figura aseguir.
Caso haja um crescimento de informações, ela irá crescer gradativamente, desde que o parâmetro AutoGrowth esteja setado.
No parâmetro File Growth está definido como será esse crescimento, nesse caso a cada 1GB.
E  por fim analisar o parâmetro Maximum File Size, que define o valor máximo total que esse database pode crescer fisicamente no disco, nesse caso 20GB.

Antes de alterar qualquer parâmetro é importante saber o quanto espaço temos livre no disco em que o database esta armazenado, como vimos anteriormente.
Caso o disco tenha espaço aumente o valor máximo, gradativamente a cada 10GB, até que seja sanado o problema. Depois, quando tudo terminar volte o valor, pois essa é apenas uma medida paliativa.

Também é possivel fazer esse procedimento via código:

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', MAXSIZE = 30720000KB , FILEGROWTH = 102400KB )
GO

Passo 3 – Estudando a query ofensiva e sugerindo tunning para melhoria.

Aumentar tamanho de disco, na verdade é uma medida de emergência.
O que deve ser feito com calma é um estudo da query ofensiva e resolver definitivamente o problema. Para isso é necessário analizar o plano de execução dessa query para entender onde esta o auto consumo do banco.
Para isso clique no link que o aparece no resultado da execução da query de monitoramento ( query mostrada no inicio desse documento ).
Será exibido uma pagina em xml com o plano de execução, como mostra a figura a seguir:

    Esse é o plano de execução da query ofensiva. O SQL Server automaticamente detecta onde esta o problema e sugere uma ação de “tunning” para melhorar a query. Essa sugestão é mostrada no cabeçalho da página como mostra o topo da imagem.
Ao executar a query de monitoramento sabemos qual host, login e programa esta executando a tal query. Entre em contato com o departamento responsável por essa query notificando o auto consumo.

Passo 4 – Matando o SQL Ofensor !!!

Quando nada resolver … é só matar essa query.
Para isso é necessário primeiro descobri o SPID dessa query com o comando a seguir.

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Agora é só matar o processo com o comando:

kill SPID

Passo 5 – Algumas referência.

Artigo Completo MSDN Sobre TEMPDB

http://www.red-gate.com/specials/Grant.htm

Juarez Silva, DBA Senior  que contribuiu com o desenvolvimento do script de monitoração.

Espero ter ajudado um pouco …

Abraços

Kenia Milene
Tweet to @keniamilene

Como Fazer Reorg do SQL Server 2005

Mudar  a Forma do Recovery

O primeiro passo seria  separar o Log e os Datafiles , mas como o banco será alterado para o modo Recovery Simple essa ação não será necessário.

Recovery Simple

Esse é o método que será adotado para as bases de dados. O simple não vai fazer gerar mais logs de transações, mais o backup full continuará sendo feito normalmente.

Ao fazer uma transação, essa é armazenada no datafile e no log de transação ao mesmo tempo, com o simple essa transação será armazenada somente no datafile.

Alterar o modo do banco para Simple:

ALTER DATABASE BANCO SET RECOVERY SIMPLE

Ou ainda pelo Management Studio

Comando para fazer o backup da base de dados:

BACKUP DATABASE BANCO TO DISK = ‘CAMINHO\BANCO.BAK’

Ou pelo management Studio

Reindex do Banco

Outro ponto importante é fazer o reindex dos índices das tabelas.

Existe uma Stored Procedure chamada sp_MSforeachtable que permite executar de forma recursiva umreindex para todas as tabelas dentro de um banco de dados com a utilização de uma única linha de código.

EXEC sp_MSforeachtable @command1= “DBCC DBREINDEX (‘?’, ”, 80)”

Importante saber que ao fazer o reindex o SQL faz um clone do banco, sendo assim ele cresce 100%, providencie espaço caso não haja.

SHRINK

No Microsoft SQL Server 2005, você pode reduzir um arquivo de log de transação ou os datafiles de um banco de dados para remover páginas não utilizadas. O mecanismo de banco de dados reutiliza espaço com eficiência.

Primeiramente faça o shrink do banco:

DBCC SHRINKDATABASE (N ‘BANCO’ )

Logo em seguida faça o Shirink dos arquivos ( datafiles, log)

DBCC SHRINKFILE (N ‘DATAFILE’ , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N ‘LOG’ , 0, TRUNCATEONLY)

TRUNCATEONLY

Libera o espaço livre no final do arquivo para o sistema operacional, mas não realiza qualquer movimento dentro do arquivo. O datafile é reduzida apenas a ultima extensão alocada.

Ou pelo Manager

Olha …. Consegui números bem significativos …

BANCO 1
Antes:  Database – 23GB   / Log – 180MB
Depois:  Database – 22GB / Log – 20MB
Tempo: Reindex – 31 Min / Shrink – 10Min

BANCO2
Antes: Database – 5GB / Log – 504KB
Depois:  Database – 2,8GB / Log – 504KB
Tempo: Reindex – 30 Min / Shrink – 5Min

BANCO3

Antes: Database – 20GB / Log – 7,5GB
Depois:  Database – 20GB / Log – 20MB
Tempo: Reindex – 45 Min / Shrink – 15Min

E é isso ai minha gente

Beijão

Kenia Milene

Exportar e importar dados do SQL Server com linha de comando (BCP)

 Bom amigos … todos conhecem o SQL Server e todas as suas ferramentas gráficas não é mesmo ????

 Pois existe uma maneira rápida, fácil e pratica de exportar (dump) e importar (restore) de uma base de dados SQL Server sem usar essas pesadas ferramentas.

O BCP (Bulk Copy Program) nos da à possibilidade de exportação e importação de dados através de uma simples linha de comando.

Para facilitar a vida é mais pratico criar arquivos bat que segue abaixo:

 

Exportanto

 

Bcp_exp.bat

 

Set Server=IP

Set DbUser=sa

Set DbPwd=SENHA

 

Bcp BD..TABELA OUT CAMINHO\tabela.bcp –o CAMINHO\tabela.log_exp –S%Server% -U%DbUser% -P%DbPwd% -c

Bcp BD..TABELA2 OUT CAMINHO\tabela2.bcp –o CAMINHO\tabela2.log_exp –S%Server% -U%DbUser% -P%DbPwd% -c

 

pause

 

Muito importante lembrar que existem problemas de versão, ou seja, exportação e importação entre Sql Server 2000 e 2005. Sendo assim para que não existam problemas, usamos o parâmetro –c

 

Importando

 

Bcp_imp.bat

 

Set Server=IP

Set DbUser=sa

Set DbPwd=SENHA

 

Bcp BD..TABELA IN CAMINHO\tabela.bcp –o CAMINHO\tabela.log_imp –S%Server% -U%DbUser% -P%DbPwd% -c

Bcp BD..TABELA2 IN CAMINHO\tabela2.bcp –o CAMINHO\tabela2.log_imp –S%Server% -U%DbUser% -P%DbPwd% -c

 

pause

 

PS: Substitua os valores em azul negrito pelos seus dados

E era isso ….

Boa Sorte !!!

 

Kenia Milene