Um plano de manutenção completo para o SQL Server 2008
Otimizando bases 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);
- Backup do banco de dados do servidor ao vivo para uma pasta de backup compartilhada
- Re-Index Database
- Mate todas as conexões e restaure o banco de dados no servidor de relatórios.
- Servidores vinculados estarão em um artigo separado em breve
- Log Shipping será em um artigo separado em breve
Ao fazê-lo cruzar o servidor, você precisará permitir a conta que o SQL Server está executando do acesso ao 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 conjunto de esquema é "maint"
Há uma série de outros artigos que escrevemos abaixo que podem ser do seu interesse.
Plano completo de manutenção
Agora, podemos gerenciar todos esses segmentos de código separados com outro procedimento que os chamará na ordem correta, isso será colocado no servidor ao vivo. O código é executado da seguinte maneira;
- Estime o tempo
- Se é tarde re-indexar o banco de dados
- Execute um backup do banco de dados (adicione onde você precisa)
- Se for de volta as outras bases de dados (adicione onde precisa)
- Mate conexões e restaure o banco de dados (adicione onde você precisa)
Este código foi testado e testado e funcionou por períodos de meses sem problemas.
Observe que o encolhimento de arquivos de log e arquivos de banco de dados deve ser reduzido, executando um backup que está esvaziando o log (embora ele conserve o espaço usado). Se for necessário quando deve ser executado fora do horário normal de trabalho.
SQL Code
GO
CREATE PROC [maint].MaintenancePlan AS BEGIN
DECLARE @BackupType VARCHAR(1)='E'
IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGIN
SET @BackupType='D'
END
--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!
--Re-index Live
IF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'
--Create Backup
BACKUP 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 Night
IF @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= 10
END
--Restore Backups on other server
EXEC [server].[utilities].[maint].KillConnections 'dbname';
IF @BackupType='E' BEGIN
EXEC [server].[utilities].[maint].KillConnections 'dbname2';
GO
Banco de dados de backup
Para obter o código para fazer backup de seu banco de dados, é mais fácil script do código do SSMS.
Siga o processo que você usaria normalmente e, em seguida, selecione "Ação do Script para Nova Janela de Consulta".
Copie este código para o plano de manutenção.
Re-Index Database
Em seguida, podemos adicionar algum código para re-indexar nosso banco de dados, isso novamente é um procedimento armazenado compartilhado onde você só precisa dizer ao sistema o nome.
Para evitar a duplicação de código, você pode ler sobre isso no link abaixo.
Kill Connections
Ao executar uma restauração no banco de dados, você só pode ter 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. Nós criamos novamente um artigo separado para isso.
Banco de dados de restauração
Este código também pode ser roteado a partir do SQL Server Management Studio. Se você adicionar este código a um procedimento armazenado, você pode chamá-lo de outros processos e até mesmo de outros servidores com muita facilidade. Nós mudamos isso para um artigo separado para cobrir mais opções.
