Crescimento exagerado do TEMPDB.

[Marina Marques]

Boa dia.

Então, tenho um servidor SQL Server 2008R2, com espaço em disco dedicado para o tempdb de 250GB.

Em dois meses esse tempdb cresceu, ocupando hoje 200gb. O que não ocorria anteriormente.

Tenho mais de 120 bases neste servidor, que está em Cluster.

Como boa prática do consultor que nos ajudou na implantação desse cluster, foi criado 1 NDF do tempdb para cada núcleo de processador que tenho nesse servidor. Explicação que seria uma melhor distribuição das demandas.

Como o numero de bases é alto. Como poderia mapear as demandas para apontar o responsável pelo crescimento? Com Profile?Audit?

--
----------------------------------------------
Att.
Marina Marques Malvino

[Raiane Lins]

Bom dia,

Atualmente ele está usando de fato esses 200GB?

Primeiramente poderia executar a seguinte query e postar aqui o resultado?

USE TEMPDB

EXEC sp_spaceused

--

---
You received this message because you are subscribed to the Google Groups "SQLServerDF" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlserverdf...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marina Marques]

Raiane, 

Segue o retorno do resultado.

[Leonardo Pedroso Costa]

Marina,

monitore o momento em que os growths desse banco ocorrem (talvez isso seja gravado no default trace, não tenho certeza), pode ser que uma reindexação online de alguma big table usou o tempdb pra ordenar o índice e o fez crescer dessa maneira. Porém como a Raiane suspeitou, esse espaço pode estar livre ao término da transação e o tempdb só crescerá se precisar usar mais espaço do que tem alocado.

Em 23 de abril de 2014 11:16, Raiane Lins <nan...@gmail.com> escreveu:

--

Leonardo Pedroso Costa

DBA SQL Server

Belo Horizonte - MG

[Gustavo Maia]

Marina, tem um post no simple-talk (https://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/) muito bom sobre essa sua busca pelo responsável pelo crescimento do tempdb.

Também acho bacana dar uma olhada nessas dicas do Aaron Bertrand no stackexchange (http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log) para minimizar a utilização do tempdb...

Por último eu verificaria se cada arquivo do tempdb está sendo utilizado igualmente, se estão do mesmo tamanho, se está com a trace flag 1118 ativa, etc.

Gustavo Moura Fé Maia

MCTS | MCP | MTA

[Marina Marques]

Mas o LOG do TEMPDB está crescendo, venho monitorando, verificando a porcentagem de espaço livre desse disco, e percebo que alem dos NDFs, o LOG também vem crescendo.

Ontem as 18:00 estava com 19%, hoje pela manhã estava com 14%. Dei um shirinkfile no LOG do tempdb, e ele reduziu dos 31GB apenas 1 GB.

Uns 40% dos NDFs estão com o mesmo tamanho e data de modificação iguais, os restantes variam.

[Jefferson Oliveira]

Qual resultado desses comandos?

use tempdb

go

select * from tempdb.sys.sysfiles  

dbcc loginfo()

-------------------------------------------------
att,

Jefferson Santos

[Rodrigo Ribeiro Gomes]

Bom dia Marina,

Você já verificou se as sessões ficam abertas por muito tempo? Essas sessões que não estão fechando, podem ter terminado de utilizar o tempdb, porém não libaram o espaço utilizado.

O que sugiro é realizar um SHRINK dos arquivos de dados e quando houver esse crescimento novamente executar as seguintes querys para análise:

--Verificar sessões abertas que estão abertas há mais de duas horas sem executar nada.

SELECT  s.session_id,

@@ServerName,

DB_NAME (p.dbid),

p.open_tran,

s.host_name,

s.login_name,

s.status,

est.text

FROM 

sys.dm_exec_sessions s

INNER JOIN sys.sysprocesses p on s.session_id = p.spid

OUTER APPLY sys.dm_exec_sql_texT(p.sql_handle) as est

WHERE s.session_id > 50 

AND s.is_user_process = 1

AND s.last_request_end_time < DATEADD (HH,-2, GETDATE()) 

--Verificar as sessões que estão consumindo mais recursos do TEMPDB

SELECT

*

FROM

(

SELECT

SU.session_id

,((SU.internal_objects_alloc_page_count+SU.user_objects_alloc_page_count)

-

(SU.internal_objects_dealloc_page_count+SU.user_objects_dealloc_page_count)

)/128.00 as Alocado_Data

,L.Qtdlog/1024.00/1024.00 as Alocado_Log

,st.text

FROM

sys.dm_db_session_space_usage SU

LEFT JOIN

sys.dm_exec_connections C

ON C.session_id = SU.session_id

OUTER APPLY

sys.dm_exec_sql_text(C.most_recent_sql_handle) ST

LEFT JOIN

(

SELECT

ST.session_id

,DT.database_transaction_log_bytes_reserved as QtdLog

FROM

sys.dm_tran_database_transactions DT

INNER JOIN

sys.dm_tran_session_transactions ST

ON DT.transaction_id = ST.transaction_id

WHERE

DT.database_id = 2

) L

ON L.session_id = SU.session_id

) T

WHERE

T.Alocado_Data > 0

OR

T.Alocado_Log  > 0

[]'s Rodrigo Ribeiro Gomes

MCITP: Database Administrator 2008

MCITP: Database Developer 2008

061 8106-2710

http://rodrigorrg.wordpress.com/

[Marina Marques]

Rodrigo, obrigada pelas consultas e a todos os outros que me ajudaram.

Encontrei um usuario, com mais de 70 conexões, que há mais de 8 horas não executam nada. Esse usuário, é de uma aplicação nova que foi instalada recentemente. Não posso afirmar nada, mas já encaminhei o questionamento do por quê de tantas sessões abertas.

Sobre fazer Shrink, tenho minhas preocupações em fazer "a quente", o do LOG já fiz, porém apenas 1GB foi liberando, restando 30 GB.

Consegui uma janela, e vou reiniciar o serviço na hora do almoço. Assim o Tempdb será zerado, certo? E ai, começarei a mapear o uso dele.

Att.

Marina

[Leonardo Pedroso Costa]

Marina, sim, o tempdb será zerado assim que reiniciar.

Você conseguiu ver se são essas 70 conexões abertas que está consumindo esses 30GB? Pode ser que não sejam elas também. O Jeferson te mandou o comando dbcc loginfo()  que mostra os VLF's do LOG, pode ser que algum vlf ainda esteja ativo e sendo utilizado (status = 2), e isso é um dos motivos que a gente não consegue fazer o shrink de todo o espaço disponível. Se não me engano a opção TRUNCATE ONLY "arreda" esse vlf pro final do log e permite essa redução, sinceramente não sei se o truncate only no tempdb traria algum efeito indesejável, visto que é um banco temporário e não tem rotina de backup/restore para ele.

[Marina Marques]

Leonardo,

Tenho dois VLF com status=2.

[Leonardo Pedroso Costa]

Então Marina,

até onde entendo, esse VLF deveria ser "limpo" quando a transação concluir e acontecer o checkpoint. Tem um script em anexo e você conseguirá reproduzir fielmente esse processo. Faça isso num banco separado e execute as etapas.

Para encontrar transações abertas eu uso esses comandos:

DBCC OPENTRAN

select * from sysprocesses where open_tran <> 0 and dbid = 2 --Busca inclusive transações aninhadas

select * from sys.dm_exec_sessions where open_transaction_count > 0 and database_id = 2

No fringir dos ovos, seria: procurar pelas transações em aberto no banco, verificar se a conexão não foi fechada ou se realmente está em execução. O checkpoint já é automático então de acordo com métricas internas roda de tempo em tempo.

[Jefferson Oliveira]

Leonardo,

O script você mandou serve de aprendizado pra quem tem esse problema. É praticamente isso que eu faço também rsrs.

Mas em casos de banco em produção com rotinas de backup, não executar o truncateonly (é o 5º passo do script), troque a operação por um backup de Log e depois um shrink sem a opção do truncateonly.

[Leonardo Pedroso Costa]

Isso mesmo Jefferson,

o truncate é pro caso do tempdb mesmo. Uma dúvida que tive foi a seguinte, e no caso de bancos com recovery model simple? Não tem como fazer backup de log, creio que a solução seja identificar a transação e rodar o checkpoint mesmo.

[Jefferson Oliveira]

Leonardo, 

Tanto no modo FULL ou SIMPLE (recovery mode) o SQL Server vai ocupar os VLFs. 

A diferença está na hora de marcar os VLFs como inativo (Status = 0), pois no modo FULL é necessário fazer o backup de LOG e no modo SIMPLE basta fazer um checkpoint (em ambos os casos a transação não pode estar ativa).

Nos dois modos de recuperação, para limpar os VLFs, ou seja, diminuir o tamanho do LOG, deve fazer o shrink database.

Marina,

Vi o resultado do seu comando DBCC LOGINFO(), e achei estranho, você tirou print de todos registros? Porque o campo FileSize está em bytes, se somarmos os VLFs e dividir eles por 1024 + 8 k, vai dar o tamanho do arquivo que é idêntico a coluna size do comando sp_helpdb. Entaun com base no print, seu  arquivo de log está com 1280 Kilobytes e não 30 GB.

Segue calculo: (((253952 + (262144 * 4)) / 1024) + 8 K) = 1280 KB

Você tirou o print depois de reiniciar o serviço, certo?

[Marina Marques]

Leonardo, tirei o print minutos antes de reiniciar o servidor.

O LOG do meu Tempdb estava com 30GB.

Bom, vou fazer uns testes com esses scripts no ambiente de desenvolvimento. Hoje o LOG do TEMPDB desse ambiente está com 18GB.

[Leonardo]

E aí Marindows,

deu tudo com o log do seu ambiente? Conseguiu fazer o shrink sem problemas ou continuou do jeito que estava?