PROBLEMA: SQL Server Agent XPs Disabled

Olá pessoal! Tudo certo?

 Hoje vou abordar um outro problema que o DBA pode vir a enfrentar no dia-a-dia. Por acaso vocês já se fizeram login em uma instância SQL e se depararam com o SQL Server Agent com status igual da imagem abaixo?



 O mais estranho, ao entrar nos serviços ou no Configuration Manager do SQL Server você nota que o mesmo está com status “Started”.

Analisando o problema

 O primeiro passo para investigar o problema é olhar o error log. Se a instância do SQL Server estava funcionando normalmente, não teve nenhuma queda inesperada e você se deparou com este problema, você deve ver algo parecido com o seguinte no error log:

2013-06-22 11:09:58.240 spid61       Error: 17053, Severity: 16, State: 1.
2013-06-22 11:09:58.240 spid61       C:\TransactionLog\TESTE_LOG2.LDF: Operating system error 112(There is not enough space on the disk.) encountered.
2013-06-22 11:17:16.020 spid61       Error: 9002, Severity: 17, State: 2.
2013-06-22 11:17:16.020 spid61       The transaction log for database 'TESTE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 Opa! O que temos aqui? Um arquivo de log da base de dados tentou expandir na unidade e não encontrou espaço para tal, continuando a leitura da log:

2013-06-22 12:30:09.100 spid10s      Could not write a checkpoint record in database ID 5 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
2013-06-22 12:30:09.090 spid63       Error: 3314, Severity: 21, State: 4.
2013-06-22 12:30:09.090 spid63       During undoing of a logged operation in database 'TESTE', an error occurred at log record ID (8153835:17896:72). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backu
2013-06-22 12:30:09.110 spid10s      Automatic checkpointing is disabled in database 'TESTE' because the log is out of space. Automatic checkpointing will be enabled when the database owner successfully checkpoints the database. Contact the database owner to either truncate the log file or
2013-06-22 12:30:09.110 Backup       Error: 3041, Severity: 16, State: 1.
2013-06-22 12:30:09.110 Backup       BACKUP failed to complete the command BACKUP LOG TESTE. Check the backup application log for detailed messages.
2013-06-22 12:30:09.120 spid63       Error: 9001, Severity: 21, State: 5.
2013-06-22 12:30:09.120 spid63       The log for database 'TESTE' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

 Tivemos sucessivas falhas, todas relacionadas a falta de espaço na log da base de dados, continuando: 

2013-06-22 12:30:15.750 spid22s      Starting up database 'TESTE'.

 Opa! Este “starting” em meio ao log está nos dando uma dica do que vem a seguir:

2013-06-22 12:32:33.250 spid22s      Analysis of database 'TESTE' (5) is 1% complete (approximately 23 seconds remain). This is an informational message only. No user action is required.
2013-06-22 12:32:35.830 spid22s      Analysis of database 'TESTE' (5) is 20% complete (approximately 11 seconds remain). This is an informational message only. No user action is required.
2013-06-22 12:32:40.280 spid22s      Analysis of database 'TESTE' (5) is 55% complete (approximately 5 seconds remain). This is an informational message only. No user action is required.
2013-06-22 12:32:45.200 spid22s      Analysis of database 'TESTE' (5) is 93% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2013-06-22 12:32:46.040 spid22s      Analysis of database 'TESTE' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2013-06-22 12:32:46.060 spid22s      Recovery of database 'TESTE' (5) is 0% complete (approximately 1509 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2013-06-22 12:32:50.530 spid22s      Recovery of database 'TESTE' (5) is 1% complete (approximately 267 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2013-06-22 12:32:54.810 spid22s      Recovery of database 'TESTE' (5) is 3% complete (approximately 249 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

 Isso mesmo, a base foi pra recovery devido as transações que estavam abertas quando o problema do log ocorreu, porém o problema não acaba ai, continuando a leitura da log:

2013-06-22 12:34:10.510 spid22s      13977 transactions rolled forward in database 'TESTE' (5). This is an informational message only. No user action is required.
2013-06-22 12:34:12.500 spid22s      Recovery of database 'TESTE' (5) is 48% complete (approximately 88 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2013-06-22 12:34:12.630 spid22s      Error: 17053, Severity: 16, State: 1.
2013-06-22 12:34:12.630 spid22s      C:\TransactionLog\TESTE_LOG2.LDF: Operating system error 112(There is not enough space on the disk.) encountered.

 Veja só, a fase 3 do nosso recovery falhou pelo mesmo motivo das transações, ela não encontrou espaço na log para concluir com sucesso (algumas vezes a operação de recovery necessita de espaço adicional da log para executar), qual o resultado disso? O SQL Server Agent fica no status “Agent XPs Disabled”, vamos olhar o log do SQL Server Agent em busca de mais informações:

20xx-xx-xx xx:xx:xx – ? [x] Waiting for SQL Server to recover databases…

 Veja só, ai esta nosso problema, o Agent está no aguardo do término do recovery daquela base de dados chamada TESTE, e agora?


Saindo do problema

 O log da base está cheio e está sendo utilizado, você provavelmente não irá conseguir manipular o mesmo com operações de backup, o mais indicado nestes casos é liberar espaço na unidade em que o log reside ou expandir o tamanho da mesma. Não efetue o detach da base de dados, você pode ter problemas em recuperar a mesma, ou pode até recupera-la porem assumindo a perda de alguns dados. Uma vez que você resolver este problema de espaço, execute o comando abaixo:

ALTER DATABASE DBNAME SET ONLINE

 Acompanhe no error log o recovery da base:

2013-06-23 06:01:50.170 spid14s      Recovery of database 'TESTE' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2013-06-23 06:01:50.170 spid14s      1 transactions rolled back in database 'TESTE' (5). This is an informational message only. No user action is required.

 Veja só, foi efetuado o rollback de uma transação, quando atualizamos nosso Management Studio o que ocorre com o nosso Agent?

 
Outra situação

 Você também pode passar por este problema caso seu SQL Server sofra com uma queda inesperada e no processo de recovery das bases ele não consiga concluir com sucesso o mesmo.

Evitando este problema

 Evite ter mais de um arquivo de log para as suas bases de dados, com dois arquivos fica mais difícil controlar o crescimento dos mesmos e aumenta a chance de operações de expansão/recovery falharem.
 Tenha sempre discos bem dimensionados para que você nunca tenha esta necessidade de realizar operações de emergência nos arquivos de log, ou tenha que realizar expansão das unidades de disco, o que nem sempre é algo fácil ou viável de fazer.

 Espero que vocês tenham gostado do post pessoal! Até a próxima!

SHRINKDATABASE, SHINRKFILE, AUTO-SHRINK, saiba porque evitá-los!



Olá pessoal, tudo certo? Espero que sim!

 Em minhas passagens pelos fóruns, tenho visto alguns tópicos semelhantes com relação à liberação de espaço não utilizado dos arquivos de dados das bases para o disco através do comando DBCC SHRINKFILE. No post de hoje vou tentar explicar o porquê de sempre se tentar evitar esta prática considerada prejudicial para a performance do SQL Server. 

 Existem muitos links na Internet falando sobre o assunto mas basicamente me apoiei nos links do Paul S. Randal, que pra quem não sabe, é o “pai” dos comandos DBCC: 


http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/  
 
 O objetivo do shrink é trabalhar num arquivo de cada vez, quando executado, ele movimenta as páginas alocadas mais para o final do arquivo para o início, de modo a liberar o máximo do espaço requisitado pelo DBA para o sistema operacional. Como o espaço é alocado em extents, fica a impressão de que extents são movidos, mas na verdade a liberação de espaço é obtida com a movimentação de páginas, nesta operação não existe cuidados com a ordem lógica das páginas e dos índices, o resultado disto? Aumento no consumo das CPU’s, mais I/O, cada página movimentada será registrada no transaction log e o mais grave, muitos índices na base ficarão extremamente fragmentados. Para um melhor entendimento recomendo que você faça o “demo” proposto pelo Paul S. Randal no primeiro link.

 A única situação em que o shrink em um arquivo de dados não causaria problemas, seria utilizando a opção TRUNCATEONLY. Usando esta opção e tendo espaço livre ao final do arquivo, o shrink simplesmente liberaria espaço sem movimentar as páginas. 

 Este modo de operação é o mesmo para os comandos DBCC SHRINKDATABASE, DBCC SHRINKFILE e para a opção das base de dados chamada AUTO-SHRINK. 

 Você pode utilizar a query abaixo para verificar a fragmentação de todos os índices num banco de dados: 

SELECT
     convert(varchar(25),db_name()) as DatabaseName,
convert(varchar(50),OBJECT_NAME (a.object_id)) as ObjectName, 
a.index_id,
convert(varchar(50),b.name) as IndexName, 
avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b   
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <> 0
 
E quanto ao shrink nos arquivos de log?

 O SQL Server não pode movimentar os registros no final do arquivo de log assim como faz nos arquivos de dados. Somente é possível reduzir o tamanho do arquivo de log se ele estiver vazio no final. O shrink dos arquivos de log é feito eliminando virtual log files (VLFs). Os VLFs podem ser vistos através do comando DBCC LOGINFO, cada linha retornada pelo comando é um VLF. Na saída do comando, status = 0 indica que o VLF não está em uso e status = 2 indica que ele está em uso. Ou seja, se o status do último VLF do arquivo de log for 2, não será possível realizar o shrink. Nestes casos, para reduzir o tamanho do arquivo de log, você pode tentar rodar um CHECKPOINT ou realizar backups do log para liberar VLFs e viabilizar os shrinks. Ao contrário dos arquivos de dados, o shrink dos arquivos de log não causa problemas de performance simplesmente com a sua execução: o problema com o arquivo de log são auto-grows sucessivos em pequenos incrementos. Neste cenário, a quantidade de VLFs pode aumentar muito, prejudicando funcionalidades como replicação e recovery. Em particular, com relação ao processo de recovery, também é importante contar com as últimas correções do engenho, apenas para evitar surpresas: 

 
 Minha sugestão é que você sempre busque dimensionar da melhor forma possível os discos em seu ambiente para suportar com tranquilidade a expansão das bases de dados. Além disto, procure ter uma rotina periódica que faça a reindexação das tabelas.

 Espero que tenham gostado, até a próxima!