Eu tenho um procedimento armazenado do SQL Server que essencialmente 'nivela' um banco de dados complexo em uma tabela única e simplificada em um banco de dados separado para permitir que os usuários consultem os dados sem precisar entender a estrutura do banco de dados. O SP trunca a tabela de destino, insere a chave primária e outros campos e, em seguida, executa uma série de atualizações para preencher todos os outros campos da tabela usando funções de usuário e diversas consultas para formatar os dados. Existem aprox. 9 milhões de linhas na tabela e o SP levam cerca de 2,5 horas para ser executado e durante esse tempo o arquivo de log cresce para mais de 30 GB. O banco de dados está em um servidor Azure com espaço em disco limitado e recentemente o SP travou porque o arquivo de log ficou sem espaço.
O banco de dados de destino está no modo de recuperação simples e o SP é executado durante um fim de semana, portanto, não há outros processos ou usuários utilizando-o. O SP não contém nenhuma transação ou commit e não fragmenta o trabalho, apenas executa do início ao fim, inserindo, atualizando (e ocasionalmente excluindo) as linhas. Como posso torná-lo mais eficiente e usar menos espaço no arquivo de log? Deveria usar transações, confirmar ou reduzir o arquivo de log à medida que ele é executado? Ou alguma outra coisa?
Tudo isso precisa ser feito em uma única transação enorme? Seu banco de dados fará isso por padrão se você não informar diferente. Lembre-se de que tudo o que você faz em uma transação deve caber no “espaço de log” disponível. Porém, assim que você emitir um commit, esse "espaço de log" se tornará utilizável novamente.
Dependendo de quanto trabalho cada "etapa" realiza, você pode fazer todas as inserções, confirmá-las e depois fazer as atualizações, possivelmente em "pedaços", confirmando cada uma à medida que avança. Colocar os commits significa que você não deve ficar sem espaço no log de transações.
Se todo o processo falhar, você jogaria fora o "outro" banco de dados e o reconstruiria do zero.