Júlio Battisti Livros e Cursosa sua sala de aula na internet

Comando DBCC DBREINDEX

Utilizamos este comando para reconstruir um ou mais índices em uma tabela de um Banco de Dados.

Sintaxe conforme Books OnLine:

DBCC DBREINDEX

( [ ‘database.owner.table_name’

[ , index_name

[ , fillfactor ]

]

]

) [ WITH NO_INFOMSGS ]

Na nossa missão de simplificar a sintaxe e o entendimento dos comandos da linguagem T-SQL, vamos a alguns exemplos práticos. Porém antes dos exemplos, vamos fazer algumas observações a respeito deste comando:

• Não podemos utilizar este comando em tabelas do sistema (master, msdb, etc.).
• Por padrão, somente a role de servidor sysadmin e as roles de Banco de Dados db_owner e db_ddladmin têm permissão para executar este comando.
• É mais vantajosa a utilização do comando DBCC DBREINDEX, do que a utilização dos comandos DROP INDEX e CREATE INDEX. Por exemplo, em uma tabela com diversos índices, podemos utilizar o comando DBCC DBREINDEX para reconstruir todos os índices de uma única vez, ao invés de utilizar vários comandos DROP INDEX e CREATE INDEX, um para cada índice. Além disso, o comando DBCC DBREINDEX é executado com sucesso ou falha como um todo, ou seja, atende o conceito de Atomicidade.

Vamos a alguns exemplos práticos.

Reconstruir o índice UPKCL_auidind, da tabela authors do Banco de Dados pubs.

Use pubs

DBCC DBREINDEX (‘authors’,  UPKCL_auidind, 50)

Observe que o nome da tabela vai entre apóstrofos e o nome do índice não. O terceiro parâmetro é a definição para o FILL FACTOR, que conforme descrevemos no Capítulo 4, é uma medida para o percentual de espaço a ser deixado em branco, nas páginas do Banco de Dados, quando da construção do índice.

Para reconstruir todos os índices de uma tabela, basta não especificar um nome para o índice, apenas coloque dois apóstrofas, conforme indicado no exemplo a seguir, onde são reconstruídos todos os índices da tabela titles do Banco de Dados pubs:

Use pubs

DBCC DBREINDEX (‘titles’, ‘ ‘,  60)

Importante: O Comando DBCC DBREINDEX será descontinuado nas futuras versões do SQL Server. É importante que você conheça este comando, para poder entender códigos e scripts já existentes, porém não é recomendado que você utilize o comando DBCC DBREINDEX, na criação de novos scripts e códigos. Ao invés do comando DBCC DBREINDEX, você deve utilizar o comando ALTER INDEX, o qual descreverei logo a seguir.

Comando ALTER INDEX

Utilizamos este comando para alterar os índices de uma tabela ou view (quer seja de dados relacionais ou no padrão XML). O comando ALTER INDEX, pode ser utilizado para desabilitar um índice, reorganizar ou recriar um índice. Este comando também pode ser utilizado para configurar opções de um índice.

Para poder executar o comando ALTER INDEX você deve ter, no mínimo, permissão de alteração ALTER, na tabela ou view a qual pertence o índice que está sendo alterado. Esta permissão é garantida, por padrão, para a role de servidor sysadmin e para as roles de banco de dados db_ddladmin e db_owner.

O comando ALTER INDEX não pode ser utilizado para particionar um índice ou para mover um índice de um filegroup para outro. Este comando também não pode ser utilizado para alterar a definição de um índice, tal como adicionar ou excluir colunas do índice ou alterar a ordem das colunas, no índice. Para executar estas operações você deve executar o comando CREATE INDEX, com a opção DROP EXISTING.

A seguir apresento alguns exemplos de uso do comando ALTER INDEX.

No exemplo a seguir, utilizo o comando ALTER INDEX, para recriar o índice NúmeroPedido_PK, da tabela dbo.Pedidos, do banco de dados NorthWind

USE NorthWind;

GO

ALTER  INDEX NúmeroPedido_PK ON dbo.Pedidos

REBUILD;

GO

A seguir apresento um exemplo do Books Online, onde o comando ALTER INDEX é utilizado para alterar diversas opções de um índice. Este comando define as opções STATISTICS_NORECOMPUTE, IGNORE_DUP_KEY e ALLOW_PAGE_LOCKS, do índice:

USE AdventureWorks;

GO

ALTER INDEX  AK_SalesOrderHeader_SalesOrderNumber ON

    Sales.SalesOrderHeader

SET (

    STATISTICS_NORECOMPUTE = ON,

    IGNORE_DUP_KEY = ON,

    ALLOW_PAGE_LOCKS = ON

    ) ;

GO

Daria para escrever um capítulo inteiro, só sobre o comando ALTER INDEX. Mas nada do que eu possa escrever aqui, acrescentarei em relação ao que já tem no Books Online, Por isso, para uma referência completa sobre todas as opções do comando ALTER INDEX e dezenas de outros exemplos de uso deste comando, consulte o BOOKS ONLINE. Você verá que, na prática, todos os comandos DBCC que eram utilizados para alterar índices, podem ser substituídos pelas opções do comando ALTER INDEX.

Comando DBCC DBREPAIR

Este comando era utilizado, nas versões anteriores do SQL Server, para excluir um Banco de Dados danificado. Este comando somente era fornecido para compatibilidade com versões anteriores do SQL Server. Por exemplo, você pode ter criado Stored Procedures ou Scripts que utilizam este comando e que não iriam funcionar, sem alterações, no SQL SERVER 2002. No SQL Server 2005 não existe mais suporte para este comando. Ao invés do comando DBCC DBREPAIR, você utiliza o comando DROP DATABASE, já descrito no Capítulo 3.

Comando DBCC INDEXDEFRAG

Utilizamos este comando para desfragmentar Clustered e Secondary indexes de uma tabela. Este comando também será descontinuado em futuras versões do SQL Server. Ao invés do comando DBCC INDEXDEFRAG, você pode utilizar o comando ALTER INDEX, conforme mostrarei mais adiante.

Sintaxe conforme Books OnLine:

DBCC INDEXDEFRAG

( { database_name | database_id | 0 }

, { table_name | table_id | ‘view_name’  | view_id }

, { index_name | index_id }

) [ WITH NO_INFOMSGS ]

Vamos simplificar a sintaxe e o entendimento dos comandos da linguagem T-SQL através de alguns exemplos práticos. Porém antes dos exemplos, vamos fazer algumas observações a respeito deste comando:

• Não podemos utilizar este comando em tabelas do sistema (master, msdb, etc.).
• Por padrão, somente a role de servidor sysadmin e as roles de Banco de Dados db_owner e db_ddladmin têm permissão para executar este comando.
• Este comando além de desfragmentar os índices, compacta suas páginas, levando em conta o valor original do parâmetro FILL FACTOR, quando da criação do índice.

Vamos a um exemplo prático.

Desfragmentar o índice UPKCL_auidind, da tabela authors do Banco de Dados pubs.

Use pubs

DBCC INDEXDEFRAG (pubs, authors,  UPKCL_auidind)

Ao ser executado com sucesso, este comando informa, de 5 em 5 minutos, o andamento da desfragmentação dos índices.

Observe que simplificando a sintaxe, poderíamos colocar da seguinte maneira:

Use  banco_de_dados

DBCC  INDEXDEFRAG (banco_de_dados, tabela, índice)

Comando DBCC SHRINKDATABASE

Este comando é utilizado para que possamos reduzir o tamanho de um ou mais arquivos de dados de um Banco de Dados.

Sintaxe conforme Books OnLine:

DBCC SHRINKDATABASE

( database_name [ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

Para simplificar a sintaxe e o entendimento dos comandos da linguagem T-SQL, vamos a alguns exemplos práticos. Porém antes dos exemplos, vamos fazer algumas observações a respeito deste comando:

• Não podemos reduzir o tamanho de um Banco de Dados a menos do que o tamanho do Banco de Dados model. Por exemplo, se o model tiver o tamanho de 5 MB, este será o menor tamanho ao qual um Banco de Dados poderá ser reduzido.
• Por padrão, somente a role de servidor sysadmin e a role de Banco de Dados db_owner têm permissão para executar este comando.
• Este comando não irá reduzir um arquivo do Banco de Dados a um tamanho menor do que o tamanho dos seus dados. Por exemplo, se um arquivo de dados, primário ou secundário, contiver 7 MB em dados, este é o menor tamanho ao qual este arquivo poderá ser reduzido.

Vamos a alguns exemplos práticos.

Reduzir o tamanho dos arquivos do Banco de Dados Exemplo1, mantendo um espaço livre de 25% em cada arquivo.

Use Exemplo1

DBCC  SHRINKDATABASE (Exemplo1, 25)

Ao ser executado com sucesso, este comando emite a seguinte mensagem:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

O segundo parâmetro 25, indica o percentual de espaço livre que deve ser mantido, em cada arquivo de dados, após a execução do comando. Por exemplo, um arquivo de dados possui 20 MB, dos quais 10 MB estão ocupados com dados. Após a execução do comando, serão mantidos, evidentemente, os 10 MB de dados, mais 2,5 MB (25%) de espaço livre. Na verdade o SQL Server irá arredondar para 13 MB.

Para os arquivos de log, o valor percentual de espaço livre é contabilizado como um todo e não para cada arquivo individualmente. No nosso exemplo, após executado o comando, somando o tamanho de todos os arquivos de log, o espaço livre seria de 25%.

Com o comando DBCC SHRINKDATABASE, podemos utilizar as opções NOTRUNCATE ou TRUNCATE ONLY. A opção NOTRUNCATE faz com que o espaço liberado, seja mantido no Banco de Dados e não liberado para o Sistema Operacional. Já com a opção TRUNCATE ONLY, o espaço liberado é retornado para o Sistema Operacional. A seguir, temos um exemplo de utilização da opção TRUNCATE:

Use Exemplo1

DBCC SHRINKDATABASE (Exemplo1, 25,NOTRUNCATE)

Comando DBCC SHRINKFILE

Utilizamos este comando para reduzir o tamanho de um arquivo de dados (primário ou secundário), ou de um arquivo de log do Banco de Dados.

Sintaxe conforme Books OnLine:

DBCC SHRINKFILE

( { file_name | file_id }

{ [ , target_size ]

| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]

}

)

Algumas observações a respeito deste comando:

• Não podemos reduzir o tamanho de um Banco de Dados a menos do que o tamanho do Banco de Dados model. Por exemplo, se o model tiver o tamanho de 5 MB, este será o menor tamanho ao qual qualquer Banco de Dados poderá ser reduzido.
• Por padrão, somente a role de servidor sysadmin e a role de Banco de Dados db_owner têm permissão para executar este comando.
• Este comando não irá reduzir um arquivo do Banco de Dados a um tamanho menor do que o tamanho dos seus dados. Por exemplo, se um arquivo de dados, primário ou secundário, contiver 7 MB em dados, este é o menor tamanho ao qual este arquivo poderá ser reduzido.

Vamos a alguns exemplos práticos.

Reduzir o tamanho do arquivo primário de dados, do Banco de Dados Exemplo1 a 7 MB.

USE Exemplo1

DBCC SHRINKFILE (‘exemplo1-prim’, 7)

O segundo parâmetro 7, já indica o tamanho final do arquivo de dados, em MB. exemplo1-prim é o nome lógico associado com o arquivo primário de dados.

Com o comando DBCC SHRINKFILE, podemos utilizar as opções NOTRUNCATE, TRUNCATE ONLY ou EMPTYFILE. A opção NOTRUNCATE faz com que o espaço liberado, seja mantido no Banco de Dados e não liberado para o Sistema Operacional. Já com a opção TRUNCATE ONLY, o espaço liberado é retornado para o Sistema Operacional. A opção EMPTYFILE migra todos os dados do arquivo especificado, para outros arquivos de dados no mesmo Filegroup. Novos dados não poderão ser gravados em um arquivo em que a opção EMPTYFILE foi especificada; com isso poderemos excluir o arquivo, utilizando o comando ALTER DATABASE. A seguir, temos um exemplo de utilização da opção EMPTYFILE. Depois utilizamos o comando ALTER DATABASE para excluir o arquivo:

USE Exemplo1

DBCC SHRINKFILE (‘exemplo1-sec1’,EMPTYFILE)

GO

ALTER DATABASE Exemplo1

REMOVE FILE ‘exemplo1-sec1’

Comando DBCC UPDATEUSAGE

Este comando informa e corrige erros nas informações e estatísticas sobre o espaço utilizado em disco. Estes erros podem fazer com que o comando sp_spaceused retorne informações incorretas.

Sintaxe conforme Books OnLine:

DBCC UPDATEUSAGE

( { ‘database_name’ | 0 }

[ , { ‘table_name’ | ‘view_name’ }

[ , { index_id | ‘index_name’ } ] ]

)

[ WITH [ COUNT_ROWS ] [ , NO_INFOMSGS ]

]

Algumas observações a respeito deste comando:

• Se não existirem problemas nas informações e estatísticas de uso de espaço em disco, este comando não retornará nenhuma mensagem. Este comando tenta corrigir erros nas seguintes colunas da tabela sysindexes: rows, used, reserved e dpages.
• Por padrão, somente a role de servidor sysadmin e as roles de Banco de Dados db_owner e db_ddladmin têm permissão para executar este comando.

Vamos a alguns exemplos práticos.

Utilizar o comando DBCC UPDATEUSAGE no Banco de Dados Northwind.

DBCC UPDATEUSAGE (‘Northwind’)

Ao ser executado com sucesso, este comando emite a seguinte mensagem:

DBCC UPDATEUSAGE: sysindexes row updated for table ‘syscolumns’ (index ID 2):

        USED pages: Changed from (4) to (5) pages.

        RSVD pages: Changed from (8) to (10) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘syscolumns’ (index ID 1):

        RSVD pages: Changed from (26) to (27) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘sysdepends’ (index ID 2):

        USED pages: Changed from (2) to (4) pages.

        RSVD pages: Changed from (2) to (4) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Orders’ (index ID 4):

        USED pages: Changed from (2) to (4) pages.

        RSVD pages: Changed from (2) to (4) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Orders’ (index ID 5):

        USED pages: Changed from (2) to (4) pages.

        RSVD pages: Changed from (2) to (4) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Orders’ (index ID 6):

        USED pages: Changed from (2) to (5) pages.

        RSVD pages: Changed from (2) to (5) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Orders’ (index ID 7):

        USED pages: Changed from (2) to (5) pages.

        RSVD pages: Changed from (2) to (5) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Orders’ (index ID 8):

        USED pages: Changed from (2) to (4) pages.

        RSVD pages: Changed from (2) to (4) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Order Details’ (index ID 2):

        USED pages: Changed from (2) to (6) pages.

        RSVD pages: Changed from (2) to (6) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Order Details’ (index ID 3):

        USED pages: Changed from (2) to (6) pages.

        RSVD pages: Changed from (2) to (6) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Order Details’ (index ID 4):

        USED pages: Changed from (2) to (6) pages.

        RSVD pages: Changed from (2) to (6) pages.

DBCC UPDATEUSAGE: sysindexes row updated for table ‘Order Details’ (index ID 5):

        USED pages: Changed from (2) to (6) pages.

        RSVD pages: Changed from (2) to (6) pages.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

No exemplo do servidor que estou utilizando, este comando fez uma série de correções. Isto aconteceu porque fiz muitas alterações no Banco de Dados Northwind. Na verdade, como dizemos no dia-a-dia: “Fucei bastante neste Banco de Dados”. Ao rodar este comando em um Banco de Dados que não foi muito alterado, é provável que não existam correções ou que as correções sejam mínimas. Mas a título de exemplo, foi uma feliz coincidência, uma vez que a mensagem mostra as diversas correções efetuadas por este comando.