Árvore de páginas

Versões comparadas

Chave

  • Esta linha foi adicionada.
  • Esta linha foi removida.
  • A formatação mudou.
Dica
titleDica: Links relacionados

Os links descritos a seguir, trazem informações referentes ao assunto abordado nesta página.

(Microsoft) Datafiles e Filegrous no banco de dados(Microsoft) Guia de arquitetura e design de índices: posição do índice em grupos de arquivos ou esquemas de partição

Índice

Ambientes Protheus e o banco de dados MSSQL Server

Ao criar um banco de dados para uso do Protheus, muitos ambientes foram montados com apenas um datafile (arquivo de dados) e um filegroup (grupo de arquivos) no banco de dados MSSQL Server. Com o decorrer do uso do produto, este cenário pode decorrer em perda de performance, já que o arquivo crescerá conforme novos dados são inseridos; consequentemente, o banco levará mais tempo para inserir registros em tabelas, ou para retornar informações necessárias. 

Esta página foi elaborada com o intuito de garantir a melhor experiência de uso do ERP Protheus e a adoção de boas práticas de mercado ao se configurar um banco de dados. Aqui, será demonstrado como mover índices não cluster do filegroup (grupo de arquivos) “Primary” para o filegroupSecondary”, e como mover tabelas de um datafile (arquivo físico) para outros dois arquivos.

A fabricante (Microsoft) indica que ao criar o índice não-cluster em um filegroup diferente, você pode obter ganhos de desempenho se os filegroups estiverem usando unidades físicas diferentes com seus próprios controladores. 

Expandir
titleTabelas informativas: arquivos e grupos de arquivos

Segundo a documentação oficial do MSSQL Server, o SGBD possui três tipos de arquivos:

ArquivoDescriçãoPrimáriaContém informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.SecundárioArquivos de dados opcionais definidos pelo usuário. Os dados podem ser distribuídos em vários discos, colocando cada arquivo em uma unidade de disco diferente. A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.Log de TransaçõesO log contém informações usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados. A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

E também possui diferentes grupos de arquivos:

Grupo de arquivosDescriçãoPrimáriaO grupo de arquivos que contém o arquivo primário. Todas as tabelas do sistema fazem parte do grupo de arquivos primário.Dados otimizados para memóriaUm grupo de arquivos com otimização de memória baseia-se no grupo de arquivos do fluxo de arquivosFluxo de arquivosDefinido pelo usuárioQualquer grupo de arquivos que seja criado pelo usuário quando o usuário cria o banco de dados pela primeira vez ou quando o modifica posteriormente.

(Microsoft) Modelos de Recuperação do SQL Server • (TDN) Compactação de dados: SQL Server 

Informações
titleO que encontrarei nesta página?

Nesta página, você visualizará informações sobre datafiles, filegroups e as recomendações da TOTVS, alinhada com as recomendações da fabricante do banco de dados, Microsoft. Em seguida, há uma demonstração, onde um banco de dados é criado com um datafile, um filegroup é criado, como adicionar datafiles ao filegroup criado, como mover os índices entre os datafiles e como configurar a conexão no DBAccess com um segundo filegroup.

Índice

Ambientes Protheus e o banco de dados MSSQL Server

Ao criar um banco de dados para uso do Protheus, muitos ambientes foram montados com apenas um datafile (arquivo de dados) e um filegroup (grupo de arquivos) no banco de dados MSSQL Server. Com o decorrer do uso do produto, este cenário pode decorrer em perda de performance, já que o arquivo crescerá conforme novos dados são inseridos; consequentemente, o banco levará mais tempo para inserir registros em tabelas, ou para retornar informações necessárias. 

Esta página foi elaborada com o intuito de garantir a melhor experiência de uso do ERP Protheus e a adoção de boas práticas de mercado ao se configurar um banco de dados. Aqui, será demonstrado como mover índices não cluster do filegroup (grupo de arquivos) “Primary” para o filegroupSecondary”, e como mover tabelas de um datafile (arquivo físico) para outros dois arquivos.

A fabricante (Microsoft) indica que ao criar o índice não-cluster em um filegroup diferente, você pode obter ganhos de desempenho se os filegroups estiverem usando unidades físicas diferentes com seus próprios controladores. 

Expandir
titleTabelas informativas: arquivos e grupos de arquivos

Segundo a documentação oficial do MSSQL Server, o SGBD possui três tipos de arquivos:

ArquivoDescrição
PrimáriaContém informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.
SecundárioArquivos de dados opcionais definidos pelo usuário. Os dados podem ser distribuídos em vários discos, colocando cada arquivo em uma unidade de disco diferente. A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.
Log de TransaçõesO log contém informações usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados. A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

E também possui diferentes grupos de arquivos:

Grupo de arquivosDescrição
PrimáriaO grupo de arquivos que contém o arquivo primário. Todas as tabelas do sistema fazem parte do grupo de arquivos primário.
Dados otimizados para memóriaUm grupo de arquivos com otimização de memória baseia-se no grupo de arquivos do fluxo de arquivos
Fluxo de arquivos
Definido pelo usuárioQualquer grupo de arquivos que seja criado pelo usuário quando o usuário cria o banco de dados pela primeira vez ou quando o modifica posteriormente.
Informações
titleDatafiles (arquivos de dados), filegroups (grupos de arquivos) e tipos de filegroups

Os datafiles (arquivos de dados) e logfiles (arquivos de log) são arquivos do banco de dados; datafiles armazenam dados e objetos, e logfiles armazenam informações sobre as transações no banco de dados. Os filegroups (grupos de arquivos) são separadores dos datafiles, e podem ser utilizados para facilitar a administração e alocação de recursos. Mais informações podem ser encontradas no site da fabricante.  

A indicação da TOTVS, assim como da Microsoft, é manter um filegroup específico para índices não cluster

Para exemplificar a separação dos índices e dos dados, criamos uma demonstração com o Database “TPPRD”, exibindo a mudança de índices para outro filegroup e distribuição das tabelas em mais de um datafile.

Preparo do ambiente de exemplo

  • Banco de dados criado: TPPRD
  • Datafile: “TPPRD_Data01”
  • Filegroup: “PRIMARY”

Print de exemplo do banco de dados criado:

Image Added

No DBAccess, o ambiente está configurado informando apenas os dados de conexão com o banco de dados, conforme print:

Image Added

Antes de começar...

  1. Realize e garanta que o backup da base seja realizado com sucesso antes de iniciar o processo;
  2. Realize e garanta que o backup seja extraído após o término do processo;
  3. Aplique todas as recomendações em ambientes de desenvolvimento e de homologação antes de aplicar em ambiente de produção;
  4. Faça a validação e marcação de tempo para comparativo das principais rotinas do ambiente, antes e depois de realizar o procedimento de mudança de índices;
  5. Garanta que o Protheus e serviços do DBAccess estejam parados durante a atividade;
  6. A atividade deve ser acompanhada por um profissional especializado em Banco de Dados.

Alterando o modo de recuperação do banco de dados

Coloque o banco de dados em modo Simple para evitar o crescimento excessivo do arquivo de log do SQL Server durante esta manutenção. O print a seguir mostra como selecionar a opção por meio do SQL Server Management Studio:

Image Added

Alternativamente, execute o seguinte comando:

Bloco de código
languagesql
titleScript para alteração do modo de recuperação da database
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY SIMPLE
GO

Adicionando arquivos ao filegroup Primary

Utilizando a área gráfica do SQL Server Management Studio (SSMS):

  1. Clique com o botão direito no banco de dados, abra propriedades;
  2. Selecione Files;
  3. Adicione dois datafiles, clicando em Add;
  4. Duas linhas serão adicionadas logo abaixo do arquivo de Log. Atribua o nome lógico destes datafiles;
  5. Altere o tamanho inicial dos Filegroups para 1024MB (variável dependo do tamanho do banco de dados);
  6. Nos três pontinhos (...), altere o tamanho de Autogrowth para 128MB (variável dependo do tamanho do banco de dados). Clique em Ok;
  7. Especifique o path (caminho físico dos datafiles; recomenda-se utilizar uma
Informações
titleDatafiles (arquivos de dados), filegroups (grupos de arquivos) e tipos de filegroups

Os datafiles (arquivos de dados) e logfiles (arquivos de log) são arquivos do banco de dados; datafiles armazenam dados e objetos, e logfiles armazenam informações sobre as transações no banco de dados. Os filegroups (grupos de arquivos) são separadores dos datafiles, e podem ser utilizados para facilitar a administração e alocação de recursos. Mais informações podem ser encontradas no site da fabricante.  

A indicação da TOTVS e da Microsoft é manter um filegroup específico para índices não cluster

Para exemplificar a separação dos índices e dos dados, criamos uma demonstração com o Database “TPPRD”, exibindo a mudança de índices para outro filegroup e distribuição das tabelas em mais de um datafile.

Preparo do ambiente de exemplo

  • Banco de dados criado: TPPRD
  • Datafile: “TPPRD_Data01”
  • Filegroup: “PRIMARY”

Print de exemplo do banco de dados criado:

Image Removed

No DBAccess, o ambiente está configurado informando apenas os dados de conexão com o banco de dados, conforme print:

Image Removed

Premissas:

  1. Realizar e garantir que o backup da base seja realizada com sucesso antes de iniciar o processo;
  2. Realizar e garantir que o backup seja extraído após o término do processo;
  3. Aplicar todas as recomendações em ambientes de desenvolvimento e de homologação antes de aplicar em ambiente de produção;
  4. Indicamos a validação e marcação de tempo para efeito comparativo das principais rotinas do ambiente, antes e depois de realizar o procedimento de mudança de índices;
  5. Garantir que o Protheus e serviços do DBAccess estejam parados durante a atividade;
  6. A atividade deve ser acompanhada por um profissional especializado em Banco de Dados.

Os passos desta demonstração serão:

  1. Colocar o Banco de Dados em modo Simple;
  2. Adicionar arquivos de dados ao Grupo de Arquivos Primary;
  3. Criar um Filegroup: “SECONDARY”;
  4. Adicionar arquivos de dados ao Grupo de Arquivos Secondary;
  5. Mover os índices não cluster do Grupo de Arquivos Primary para Secondary;
  6. Colocar o Banco de Dados em Modo Full.

Alterando o modo de backup do banco de dados

Vamos colocar o banco de dados em modo Simple para evitar o aumento excessivo do tamanho do arquivo de log no SQL Server.

Ou rode o seguinte comando:

Bloco de código
languagesql
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY SIMPLE
GO

Adicionando arquivos ao filegroup Primary

Crie usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS). 

  1. Clique com o botão direito no banco de dados, abra propriedades;
  2. Selecione Files;
  3. Adicione dois datafiles, clicando em Add;
  4. Duas linhas serão adicionadas logo abaixo do arquivo de Log, criar o nome “Lógico” para esses Datafiles;
  5. Alterar o tamanho inicial dos Filegroups para 1024MB (pode ser maior ou menor dependo do tamanho do banco de dados);
  6. Nos três pontinhos (), alterar o tamanho de Autogrowth para 128MB (pode ser maior ou menor dependo do tamanho do banco de dados) clique em Ok;
  7. Especificar o path (caminho físico dos datafiles, é indicado unidade diferente que os datafiles Primary);
  8. Alterar Altere o nome "Físico" físico dos dois arquivos.

Ou rode o seguinte comandoPrint de exemplo:

Image Added

Caso queira utilizar T-SQL, execute o seguinte comando, alterando o nome da database para o nome de sua database:

Bloco de código
languagesql
titleScript para adicionar datafiles ao banco de dados
Bloco de código
languagesql
USE [master]
GO
ALTER DATABASE [TPPRD] ADD FILE 
( NAME = N'TPPRD_Data02', FILENAME = N'H:\MSSQLSERVER\TPPRD\TPPRD_Data02.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Data03', FILENAME = N'H:\MSSQLSERVER\TPPRD\TPPRD_Data03.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB )
TO FILEGROUP [PRIMARY]
GO

Criando filegroup SECONDARY

Podemos É possível criar o filegroup usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS). 

Clique com o botão direito no banco de dados, abra propriedades e selecione Filegroups, logo após adicione um Filegroup, uma linha será adicionada logo abaixo de Primary, nomear esse . Nomeie este Filegroup para Secondary, e clique em OK:

Image Added

Ou rode execute o seguinte comando:

Bloco de código
languagesql
titleScript para adicionar filegroup ao banco de dados
USE [master] 
GO 
ALTER DATABASE [TPPRD] ADD FILEGROUP [SECONDARY] 
GO
ADICIONAR ARQUIVOS AO GRUPO DE ARQUIVOS SECONDARY

Adicionando arquivos ao filegroup secondary

Crie usando T-SQL (linha de comando) ou na área gráfica do SQL Server Management Studio (SSMS). 

  1. Clique com o botão direito no banco de dados, abra propriedades;
  2. Selecione Files;
  3. Adicione três datafiles, clicando em Add;
  4. Três linhas serão adicionadas logo abaixo do arquivo de Log, criar . Atribua o nome “Lógico” lógico para esses Datafiles;
  5. Alterar Altere o Filegroup dos três arquivos para SECONDARY;
  6. Alterar Altere o tamanho inicial dos Filegroups para 1024MB (pode ser maior ou menor valor variável, dependo do tamanho do banco de dados);
  7. Nos três pontinhos (...), alterar altere o tamanho de Autogrowth para 128MB (pode ser maior ou menor valor variável, dependo do tamanho do banco de dados) clique . Clique em Ok;
  8. Especificar Especifique o path (caminho físico dos datafiles, é indicado unidade diferente que os ; alinhando-se à boas práticas de mercado, utilize uma unidade diferente dos datafiles Primary);
  9. Alterar Altere o nome "Físico" físico dos três arquivos.

Image Added

Ou rode execute o seguinte comando:

Bloco de código
languagesql
titleScript para adicionar os datafiles ao filegroup
USE [master]
GO
ALTER DATABASE [TPPRD] ADD FILE
( NAME = N'TPPRD_Index01', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index01.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Index02', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index02.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ),
( NAME = N'TPPRD_Index03', FILENAME =N'F:\MSSQLSERVER\TPPRD\TPPRD_Index03.ndf' , SIZE = 1048576KB , FILEGROWTH = 131072KB ) 
TO FILEGROUP [SECONDARY]
GO

Após criar três arquivos para índices, vamos prossiga para realizar a distribuição dos índices para o datafile "SECONDARY".

MOVER ÍNDICES NÃO CLUSTER PARA FILEGROUP SECONDARY

Movendo índices não cluster para filegroup secondary

Após criar o filegroup e datafiles de índices, vamos realizar o move dos os índices não cluster serão movidos para o novo filegroup criado.

Com o O script a seguir, ele irá gerar ao ser executado, gera os comandos para movimentar os a operação de move dos índices:

Expandir
titleScript para CREATE INDEX...
Bloco de código
languagesql
titleScript para gerar o DML dos índices
declare @SchemaName varchar(100)declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique = 1 then 'UNIQUE ' else '' END 
 , ix.type_desc,
 case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
 + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
 + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
 + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
 + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + '90'
 , ix.is_disabled , 'SECONDARY'
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 -- and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams' and ix.type_desc NOT LIKE 'CLUSTERED' and ix.name NOT LIKE '%UNQ'
 order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 
 set @IndexColumns=''
 set @IncludedColumns=''

 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id

 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn

 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 
  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end
 close CursorIndexColumn
 deallocate CursorIndexColumn

 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end

 --  print @IndexColumns

 --  print @IncludedColumns

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ 
  case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH ( DROP_EXISTING = ON,' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  
 if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) 
 print @TSQLScripCreationIndex
 print @TSQLScripDisableIndex
 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
end

close CursorIndex
deallocate CursorIndex

O script acima irá gerar o DML do move dos índices:

Copiar os DMLs e rodar em uma nova janela do SSMS, movendo assim os índices para o novo filegroup Secondary.

ALTERAR BANCO DE DADOS PARA MODO FULL

Vamos voltar o banco de dados em modo Full do qual foi alterado no começo deste artigo.

Ou rode o seguinte comando:

Bloco de código
languagesql
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY FULL
GO

CONFIGURAÇÃO DBACCESS

Após a criação do filegroup Secondary e move dos índices, abrir o Monitor do DBAccess, na aba Configurações, Microsoft SQL, Tablespace Índices e incluir a chave Secondary, logo após clique em salvar:

DATABASE

Antes da Manutenção:

Antes da manutenção podemos verificar um único arquivo com 85GB reservado e 74GB efetivamente utilizado:

SHRINK DATAFILE:

Após a manutenção realizamos a criação de dois novos arquivos de dados, e a distribuição dos índices nos datafiles do filegroup Secondary, realizamos o shrink do datafile Primário da seguinte forma:

Após liberar o espaço do datafile Primário, realizamos a manutenção de Compress de dados e índices conforme indicação para boa performance do Ambiente ERP.

Print de exemplo de resultado da geração do DML: 

Image Added

Copie os DMLs gerados como resultado do script anterior e salve em um arquivo.

Após isto, realize a deleção dos índices que serão criados novamente. 

Expandir
titleScript para DROP INDEX...
Aviso
titleAtenção

Apenas execute este procedimento se tiver certeza de que os DMLs gerados para as operações de CREATE INDEX foram salvos. 

Bloco de código
languagesql
DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
 SELECT schema_name(t.schema_id), t.name,  i.name 
 FROM sys.indexes i
 INNER JOIN sys.tables t ON t.object_id= i.object_id
 WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 and (is_primary_key=0 and is_unique_constraint=0) and i.type_desc NOT LIKE 'CLUSTERED' and i.name NOT LIKE '%UNQ'

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
 PRINT @TSQLDropIndex
 FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEALLOCATE CursorIndex

Copie os DMLs gerados para o drop dos índices e execute em uma nova janela do SSMS. Esta operação deletará os índices do filegroup primary.

Após isto, execute os DMLs salvos como resultado do script de criação em uma nova janela do SSMS. Esta operação criará os índices no novo filegroup Secondary.

Até aqui, apenas as informações de índices que já existiam no banco de dados foram distribuídas entre filegroups

Alterar banco de dados para modo full

Reconfigure o banco de dados para modo Full, revertendo a alteração sugerida do início.

Image Added

Ou execute o comando:

Bloco de código
languagesql
titleRevertendo o modo de recuperação do banco de dados
USE master
GO
ALTER DATABASE [TPPRD]
SET RECOVERY FULL
GO

Configuração do DBAccess

Após a criação do filegroup Secondary e a operação de move dos índices, abra o Monitor do DBAccess. Na aba Configurações, em Microsoft SQL, há a seção de Tablespaces. Em índices, inclua a chave Secondary (referente ao filegroup criado para índices), conforme print. Clique em salvar:

Image Added

A partir deste momento, o DBAccess irá gerenciar os novos dados que forem inseridos, ou dados que sejam alterados, de maneira separada das informações relativas a índices. 

Manutenção na base de dados

Aviso

Não é necessário executar o Shrink com alta frequência. Este procedimento de manutenção foi realizado, neste caso, para liberar mais de 70% de espaço na base de dados, conforme evidências apresentadas.

Após a manutenção dos índices, datafiles e filegroups, foi executado o shrink do datafile Primário da seguinte forma:

Image Added

Image Added

Foi executada a compactação de dados e índices, conforme indicação para boa performance do Ambiente ERP.

O print a seguir mostra o consumo da database após o procedimento.

Image Added

Links de Referência:

Database Files e Filegroups

https://docs.microsoft.com/pt-br/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15

Recovery Model

https://docs.microsoft.com/pt-br/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

Compress de Dados e Índices:

https://tdn.totvs.com/pages/releaseview.action?pageId=564330500