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!