Um plano de manutenção completo para o SQL Server 2008
Otimizando Bancos de Dados
Esta seção é dedicada a manter os bancos de dados otimizados. Neste exemplo, vamos configurar o código para fazer o seguinte (veja um diagrama aproximado de como os servidores estão conectados);
- Banco de dados de backup do servidor ativo para uma pasta de backup compartilhada
- Reindexar banco de dados
- Mate todas as conexões e restaure o banco de dados no servidor de relatórios.
- Os servidores vinculados estarão em um artigo separado em breve
- Log Shipping estará em um artigo separado em breve
Ao fazer isso entre servidores, você precisará permitir que a conta que o SQL Server está executando acesse o sistema de arquivos dos servidores de destino.
Todo o código SQL é executado a partir de um banco de dados de utilitários e o esquema é definido como "maint"
Há uma série de outros artigos que escrevemos abaixo que podem ser do seu interesse.
Plano de Manutenção Completo
Agora podemos gerenciar todos esses segmentos de código separados com outro procedimento que os chamará na ordem correta, isso será colocado no servidor ativo. O código é executado da seguinte forma;
- Trabalhe o tempo
- Se for à noite, reindexe o banco de dados
- Execute um backup do banco de dados (adicione onde precisar)
- Se for à noite, faça backup dos outros bancos de dados (adicione onde precisar)
- Elimine conexões e restaure o banco de dados (adicione onde precisar)
Este código foi testado e executado por períodos de meses sem problemas.
Observe que a redução de arquivos de log e arquivos de banco de dados deve ser mínima, executando um backup você está esvaziando o log (embora ele retenha o espaço usado). Se for necessário quando deve ser executado fora do horário normal de trabalho.
SQL Code
Use [utilities]GOCREATE PROC [maint].MaintenancePlan AS BEGINDECLARE @BackupType VARCHAR(1)='E'IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGINSET @BackupType='D'END--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!--Re-index LiveIF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'--Create BackupBACKUP DATABASE TO DISK=N'{backuplocation}{dbname}.bak'WITH NOFORMAT, INIT, NAME =N'{dbname}', SKIP, NOREWIND, NOUNLOAD, STATS= 10;--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!--Backup Other Files at NightIF @BackupType='E' BEGIN EXEC [maint].DatabaseReIndex 'dbname' --Backup Others BACKUP DATABASE [databasename] TO DISK=N'{backuplocation}{dbname2}.bak' WITH FORMAT,INIT, NAME =N'{dbname2}',SKIP, NOREWIND, NOUNLOAD, STATS= 10END--Restore Backups on other serverEXEC [server].[utilities].[maint].KillConnections 'dbname';EXEC [server].[utilities].[maint].RestoreDatabase_{dbname};--Restore Backups on other server for db_2 etcIF @BackupType='E' BEGIN EXEC [server].[utilities].[maint].KillConnections 'dbname2'; EXEC [server].[utilities].[maint].RestoreDatabase_{dbname2};ENDENDGOBanco de dados de backup
Para obter o código para fazer backup de seu banco de dados, é mais fácil fazer o script do código do SSMS.
Siga o processo que você normalmente usaria e selecione "Ação de script para nova janela de consulta".
Copie este código no plano de manutenção.
Reindexar banco de dados
Em seguida, podemos adicionar algum código para reindexar nosso banco de dados, novamente este é um procedimento armazenado de compartilhamento onde você só precisa informar ao sistema o nome.
Para evitar a duplicação de código, você pode ler sobre isso no link abaixo.
Eliminar conexões
Ao realizar uma restauração no banco de dados, você pode ter apenas uma conexão com ele (o processo que executa a restauração), portanto, podemos criar um procedimento armazenado para fechar todas as conexões além do processo atual. Criamos novamente um artigo separado para isso.
Restaurar banco de dados
Esse código também pode ser script do SQL Server Management Studio. Se você adicionar esse código a um procedimento armazenado, poderá chamá-lo de outros processos e até de outros servidores com muita facilidade. Movemos isso para um artigo separado para cobrir mais opções.
