Partilhar via

  • Artigo

Opções ALTER DATABASE SET (Transact-SQL)

Este tópico contém a sintaxe ALTER DATABASE relacionada à configuração das opções de banco de dados. Para obter informações sobre outra sintaxe ALTER DATABASE, consulte ALTER DATABASE (Transact-SQL). O espelhamento e os níveis de compatibilidade de banco de dados são opções SET, mas são descritas em tópicos separados por causa de sua extensão. Para obter mais informações, consulte espelhamento de banco de dados ALTER DATABASE (Transact-SQL) e Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

Ícone de vínculo de tópicoConvenções de sintaxe Transact-SQL

Sintaxe

ALTER DATABASE database_name 
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

<optionspec>::= 
{
    <auto_option> 
  | <change_tracking_option> 
  | <cursor_option> 
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option> 
  | <db_user_access_option>
  | <external_access_option>
  | <parameterization_option>
  | <recovery_option> 
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option> 
}

<auto_option> ::= 

{
    AUTO_CLOSE { ON | OFF } 
  | AUTO_CREATE_STATISTICS { ON | OFF } 
  | AUTO_SHRINK { ON | OFF } 
  | AUTO_UPDATE_STATISTICS { ON | OFF } 
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<change_tracking_option> ::=
{
  CHANGE_TRACKING { 
        = ON [ <change_tracking_option_list > ] |
    <change_tracking_option_list> |
        = OFF
  }
}

<change_tracking_option_list> ::=
{
    ( <change_tracking_option> | <change_tracking_option_list> , 
    <change_tracking_option> )
}
  
<change_tracking_option> ::=
{
  AUTO_CLEANUP = { ON | OFF } 
  | CHANGE_RETENTION = { retention_period { DAYS | HOURS | MINUTES } ]
}

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<database_mirroring_option>ALTER DATABASE Database Mirroring<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }

  | TRUSTWORTHY { ON | OFF }
}
<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<termination>::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Argumentos

<auto_option>::=

Controla opções automáticas.

  • database_name
    É o nome do banco de dados a ser modificado.

  • AUTO_CLOSE { ON | OFF }

    • ON
      O banco de dados é desligado corretamente e seus recursos são liberados depois da saída do último usuário.

      O banco de dados é reaberto automaticamente quando um usuário tenta usá-lo de novo. Por exemplo, quando é emitida uma instrução USE database_name. Se o banco de dados for desligado corretamente enquanto AUTO_CLOSE estiver definido como ON, ele não será reaberto até que um usuário tente usá-lo na próxima vez que o Mecanismo de Banco de Dados for reiniciado. 

    • OFF
      O banco de dados permanecerá aberto depois da saída do último usuário.

    A opção AUTO_CLOSE é útil para bancos de dados desktop porque permite que os arquivos de banco de dados sejam gerenciados como arquivos comuns. Eles podem ser movidos, copiados para fazer backups ou mesmo enviados por email a outros usuários.

    ObservaçãoObservação

    Em versões anteriores do SQL Server, AUTO_CLOSE é um processo síncrono que pode causar uma queda no desempenho quando o banco de dados é acessado por um aplicativo que estabelece e interrompe conexões repetidamente com o Mecanismo de Banco de Dados. Apresentado a partir do SQL Server 2005, o processo AUTO_CLOSE é assíncrono; abrir e fechar o banco de dados repetidamente não mais prejudica o desempenho.

    O status dessa opção pode ser determinado examinando-se a coluna is_auto_close_on na exibição do catálogo sys.databases ou a propriedade IsAutoClose da função DATABASEPROPERTYEX.

    ObservaçãoObservação

    Quando AUTO_CLOSE for ON, algumas colunas na exibição do catálogo sys.databases e na função DATABASEPROPERTYEX retornarão NULL porque o banco de dados não está disponível para recuperar os dados. Para resolver isso, execute uma instrução USE para abrir o banco de dados.
    ObservaçãoObservação

    O espelhamento do banco de dados requer AUTO_CLOSE OFF.

    Quando o banco de dados é definido como AUTOCLOSE = ON, uma operação que inicia o desligamento automático do banco de dados limpa o cache do plano da instância do SQL Server. A limpeza do cache de planos gera uma recompilação de todos os planos de execução subseqüentes e pode causar uma queda repentina e temporária no desempenho de consultas. No SQL Server 2005 Service Pack 2, para cada armazenamento em cache eliminado do cache de planos, o log de erros do SQL Server contém a seguinte mensagem informativa: "O SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache '% s' (parte do cache do esquema) devido à manutenção do banco de dados ou operações de reconfiguração". Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja liberado dentro desse intervalo de tempo.

  • AUTO_CREATE_STATISTICS { ON | OFF }

    • ON
      O otimizador de consulta cria estatísticas em colunas únicas em predicados de consulta, conforme necessário, para melhorar planos e desempenho de consulta. Estas estatísticas de coluna única são criadas quando o otimizador de consulta compila consultas. As estatísticas de coluna única só são criadas em colunas que ainda não são a primeira de um objeto de estatísticas existente.

      O padrão é ON. Nós recomendamos que você use a configuração padrão para a maioria dos bancos de dados.

    • OFF
      O otimizador de consulta não cria estatísticas em colunas únicas em predicados de consulta quando estiver compilando consultas. Definir essa opção como OFF pode acarretar planos de consulta de qualidade inferior e menor desempenho de consulta.

    O status dessa opção pode ser determinado examinando-se a coluna is_auto_create_stats_on na exibição do catálogo sys.databases ou a propriedade IsAutoCreateStatistics da função DATABASEPROPERTYEX.

    Para obter mais informações, consulte a seção "Usando as opções de estatísticas em todo o banco de dados” apropriadas em Usando estatísticas para melhorar o desempenho de consultas.

  • AUTO_SHRINK {ON | OFF}

    • ON
      Os arquivos de banco de dados são candidatos à redução periódica.

      Arquivos de dados e arquivos de log podem ser reduzidos automaticamente. AUTO_SHRINK reduzirá o tamanho do log de transações somente se o banco de dados estiver definido como modelo de recuperação SIMPLE ou se foi feito backup do log. Quando definido como OFF, os arquivos de banco de dados não são reduzidos automaticamente durante as verificações periódicas de espaço não utilizado.

      A opção AUTO_SHRINK faz com que os arquivos sejam reduzidos quando mais que 25% do arquivo contém espaço não utilizado. O arquivo é reduzido de forma que 25% de seu tamanho seja de espaço não utilizado ou ele tenha o mesmo tamanho de quando foi criado, o que for maior.

      Não é possível reduzir um banco de dados somente leitura.

    • OFF
      Os arquivos de banco de dados não são reduzidos automaticamente durante as verificações periódicas de espaço não utilizado.

    O status dessa opção pode ser determinado examinando-se a coluna is_auto_shrink_on na exibição do catálogo sys.databases ou a propriedade IsAutoShrink da função DATABASEPROPERTYEX.

  • AUTO_UPDATE_STATISTICS { ON | OFF }

    • ON
      Especifica que o otimizador de consulta atualiza estatísticas quando elas são usadas por uma consulta e quando elas podem ficar desatualizadas. As estatísticas ficam desatualizadas depois que operações de inserção, atualização, exclusão ou mesclagem alteram a distribuição de dados na tabela ou na exibição indexada. O otimizador de consultas determina quando estatísticas podem estar desatualizadas, contando o número de modificações de dados desde a atualização das últimas estatísticas e comparando o número de modificações feitas em um limite. O limite se baseia no número de linhas na tabela ou na exibição indexada.

      O otimizador de consultas procura estatísticas desatualizadas antes de compilar uma consulta e antes de executar um plano de consulta armazenado em cache. Antes de compilar uma consulta, o otimizador de consulta usa as colunas, as tabelas e as exibições indexadas no predicado de consulta para determinar quais estatísticas podem estar desatualizadas. Antes de executar um plano de consulta armazenado em cache, o Mecanismo de Banco de Dados verifica se o plano de consulta faz referência a estatísticas atualizadas.

      A opção AUTO_UPDATE_STATISTICS se aplica a estatísticas criadas para índices, colunas únicas em predicados de consulta, além de estatísticas criadas usando a instrução CREATE STATISTICS. Essa opção também se aplica a estatísticas filtradas.

      O padrão é ON. Nós recomendamos que você use a configuração padrão para a maioria dos bancos de dados.

      Use a opção AUTO_UPDATE_STATISTICS_ASYNC para especificar se as estatísticas são atualizadas de forma síncrona ou assíncrona.

    • OFF
      Especifica que o otimizador de consulta não atualiza estatísticas quando elas são usadas por uma consulta e quando elas podem ficar desatualizadas. Definir essa opção como OFF pode acarretar planos de consulta de qualidade inferior e menor desempenho de consulta.

    O status dessa opção pode ser determinado examinando-se a coluna is_auto_update_stats_on na exibição do catálogo sys.databases ou a propriedade IsAutoUpdateStatistics da função DATABASEPROPERTYEX.

    Para obter mais informações, consulte a seção "Usando as opções de estatísticas em todo o banco de dados” apropriadas em Usando estatísticas para melhorar o desempenho de consultas.

  • AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

    • ON
      Especifica que atualizações de estatísticas para a opção AUTO_UPDATE_STATISTICS são assíncronas. O otimizador de consulta não aguarda a conclusão das atualizações de estatísticas para compilar consultas.

      Definir essa opção como ON não tem nenhum efeito, a menos que AUTO_UPDATE_STATISTICS seja definida como ON.

      Por padrão, a opção AUTO_UPDATE_STATISTICS_ASYNC é definida como OFF e o otimizador de consulta atualiza estatísticas de forma síncrona.

    • OFF
      Especifica que atualizações de estatísticas para a opção AUTO_UPDATE_STATISTICS são síncronas. O otimizador de consulta aguarda a conclusão das atualizações de estatísticas para compilar consultas.

      Definir essa opção como OFF não tem nenhum efeito, a menos que AUTO_UPDATE_STATISTICS seja definida como ON.

    O status dessa opção pode ser determinado examinando a coluna is_auto_update_stats_async_on na exibição do catálogo sys.databases.

    Para obter mais informações que descrevem quando usar atualizações de estatísticas síncronas ou assíncronas, consulte a seção que "Usando as opções de estatísticas em todo o banco de dados" em Usando estatísticas para melhorar o desempenho de consultas.

<change_tracking_option>::=

Controla as opções de controle de alterações. É possível habilitar o controle de alterações, definir opções, alterar opções e desabilitar o controle de alterações. Para obter exemplos, consulte a seção Exemplos mais adiante neste tópico.

  • ON
    Habilita o controle de alterações no banco de dados. Quando você habilita o controle de alterações, também pode definir as opções AUTO CLEANUP e CHANGE RETENTION.

  • AUTO_CLEANUP = { ON | OFF }

    • ON
      As informações de controle de alterações são removidas automaticamente depois do período de retenção especificado.

    • OFF
      Os dados de controle de alterações não são removidos do banco de dados.

  • CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }
    Especifica o período mínimo para manter as informações de controle de alterações no banco de dados. Os dados serão removidos somente quando o valor AUTO_CLEANUP for ON.

    retention_period é um inteiro que especifica o componente numérico do período de retenção.

    O período de retenção padrão é de 2 dias. O período de retenção mínimo é de 1 minuto.

  • OFF
    Desabilita o controle de alterações no banco de dados. É necessário desabilitar o controle de alterações em todas as tabelas antes de poder desabilitá-lo no banco de dados.

<cursor_option>::=

Controla opções de cursor.

  • CURSOR_CLOSE_ON_COMMIT { ON | OFF }

    • ON
      Qualquer cursor é aberto quando uma transação é confirmada ou quando uma transação revertida é fechada.

    • OFF
      Os cursores permanecem abertos quando uma transação é confirmada; uma transação revertida fechará todos os cursores, exceto aqueles definidos como INSENSITIVE ou STATIC.

    As configurações no nível de conexão que são definidas com o uso da instrução SET substituem a configuração de banco de dados padrão por CURSOR_CLOSE_ON_COMMIT. Por padrão, clientes ODBC e OLE DB emitem uma configuração CURSOR_CLOSE_ON_COMMIT de instrução SET no nível de conexão como OFF para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

    O status dessa opção pode ser determinado examinando-se a coluna is_cursor_close_on_commit_on na exibição do catálogo sys.databases ou a propriedade IsCloseCursorsOnCommitEnabled da função DATABASEPROPERTYEX.

  • CURSOR_DEFAULT { LOCAL | GLOBAL }
    Controla se o escopo do cursor usa LOCAL ou GLOBAL.

    • LOCAL
      Quando LOCAL for especificado e um cursor não for definido como GLOBAL ao ser criado, o escopo do cursor será local para o lote, o procedimento armazenado ou o gatilho nos quais o cursor foi criado. O nome de cursor é válido somente dentro desse escopo. O cursor pode ser referenciado por meio de variáveis de cursor local no lote, no procedimento armazenado ou no gatilho, ou em um parâmetro OUTPUT do procedimento armazenado. O cursor será implicitamente desalocado quando o lote, o procedimento armazenado ou o gatilho for encerrado, a menos que seja repassado como um parâmetro OUTPUT. Se for repassado em um parâmetro OUTPUT, o cursor será desalocado quando a última variável que o referencia for desalocada ou sair do escopo.

    • GLOBAL
      Quando GLOBAL for especificado e um cursor não for definido como LOCAL ao ser criado, o escopo do cursor será global para a conexão. O nome do cursor pode ser referenciado em qualquer procedimento armazenado ou lote executado pela conexão.

    O cursor é implicitamente desalocado somente na desconexão. Para obter mais informações, consulte DECLARE CURSOR (Transact-SQL).

    O status dessa opção pode ser determinado examinando-se a coluna is_local_cursor_default na exibição do catálogo sys.databases ou a propriedade IsLocalCursorsDefault da função DATABASEPROPERTYEX.

<database_mirroring>

Para obter descrições de argumentos, consulte espelhamento de banco de dados ALTER DATABASE (Transact-SQL).

<date_correlation_optimization_option> ::=

Controla a opção date_correlation_optimization.

  • DATE_CORRELATION_OPTIMIZATION { ON | OFF }

    Para que seja possível definir DATE_CORRELATION_OPTIMIZATION como ON, não deve haver nenhuma conexão ativa com o banco de dados exceto aquela que está executando a instrução ALTER DATABASE. Depois, há suporte a várias conexões.

    A configuração atual dessa opção pode ser determinada examinando-se a coluna is_date_correlation_on na exibição do catálogo sys.databases.

<db_encryption_option>::=

Controla o estado de criptografia do banco de dados.

Quando a criptografia estiver habilitada no nível de banco de dados, todos os grupos de arquivos serão criptografados. Qualquer novo grupo de arquivos herdará a propriedade criptografada. Se algum grupo de arquivos do banco de dados for definido como READ ONLY, haverá falha na operação de criptografia de banco de dados.

É possível ver o estado da criptografia do banco de dados usando a exibição de gerenciamento dinâmico sys.dm_database_encryption_keys.

<db_state_option>::=

Controla o estado do banco de dados.

  • OFFLINE
    O banco de dados é fechado, desligado corretamente e marcado como offline. Não é possível modificar o banco de dados enquanto ele estiver offline.

  • ONLINE
    O banco de dados está aberto e disponível para uso.

  • EMERGENCY
    O banco de dados está marcado como READ_ONLY, o log está desabilitado e o acesso é limitado aos membros da função de servidor fixa sysadmin. EMERGENCY é usado principalmente para a solução de problemas. Por exemplo, um banco de dados marcado como suspeito devido a um arquivo de log corrompido pode ser definido com o estado EMERGENCY. Isso permite habilitar o acesso somente leitura do administrador do sistema ao banco de dados. Apenas membros da função de servidor fixa sysadmin podem definir um banco de dados com o estado EMERGENCY.

O status dessa opção pode ser determinado examinando-se as colunas state e state_desc na exibição do catálogo sys.databases ou a propriedade Status da função DATABASEPROPERTYEX. Para obter mais informações, consulte Estados de banco de dados.

Um banco de dados marcado como RESTORING não pode ser definido como OFFLINE, ONLINE ou EMERGENCY. Um banco de dados pode estar no estado RESTORING durante uma operação de restauração ativa ou quando uma operação de restauração de um banco de dados ou arquivo de log falhar devido a um arquivo de backup corrompido. Para obter mais informações, consulte Respondendo a erros de restauração dos SQL Server causado por backups danificados.

<db_update_option>::=

Controla se atualizações são permitidas no banco de dados.

  • READ_ONLY
    Os usuários podem ler dados do banco de dados, mas não modificá-los.

  • READ_WRITE
    O banco de dados está disponível para operações de leitura e gravação.

Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

<db_user_access_option> ::=

Controla o acesso de usuários ao banco de dados.

  • SINGLE_USER
    Especifica que somente um usuário por vez pode acessar o banco de dados. Se SINGLE_USER for especificado e houver outros usuários conectados ao banco de dados, a instrução ALTER DATABASE será bloqueada até que todos os usuários se desconectem do banco de dados especificado. Para substituir esse comportamento, consulte a cláusula WITH <termination>.

    O banco de dados permanecerá no modo SINGLE_USER, mesmo que o usuário que definiu a opção faça logoff. Nesse momento, um usuário diferente, mas somente um, poderá se conectar ao banco de dados.

    Antes de definir o banco de dados como SINGLE_USER, verifique se a opção AUTO_UPDATE_STATISTICS_ASYNC está definida como OFF. Quando definida como ON, o thread em segundo plano usado para atualizar estatísticas estabelece uma conexão com o banco de dados e não é possível acessar o banco de dados em modo de usuário único. Para exibir o status dessa opção, consulte a coluna is_auto_update_stats_async_on na exibição do catálogo sys.databases. Se a opção for definida como ON, execute as seguintes tarefas:

    1. Defina AUTO_UPDATE_STATISTICS_ASYNC como OFF.

    2. Verifique se há estatísticas assíncronas ativas, examinando a exibição de gerenciamento dinâmico sys.dm_exec_background_job_queue.

    Se houver tarefas ativas, permita que as tarefas sejam concluídas ou as encerre manualmente usando KILL STATS JOB.

  • RESTRICTED_USER
    RESTRICTED_USER permite que somente os membros da função de banco de dados db_owner e das funções de servidor fixas dbcreator e sysadmin conectem-se ao banco de dados, mas não limita seu número. Todas as conexões com o banco de dados são desconectadas no período especificado pela cláusula de término da instrução ALTER DATABASE. Depois que o banco de dados fizer a transição para o estado RESTRICTED_USER, as tentativas de conexão realizadas por usuários não qualificados serão recusadas.

  • MULTI_USER
    Todos os usuários com permissões apropriadas para se conectar ao banco de dados são permitidos.

O status dessa opção pode ser determinado examinando-se a coluna user_access na exibição do catálogo sys.databases ou a propriedade UserAccess da função DATABASEPROPERTYEX.

<external_access_option>::=

Controla se o banco de dados pode ser acessado por recursos externos, como objetos de outro banco de dados.

  • DB_CHAINING { ON | OFF }

    • ON
      O banco de dados pode ser a origem ou o destino de um encadeamento de propriedades de bancos de dados.

    • OFF
      O banco de dados não pode participar do encadeamento de propriedades de bancos de dados.

    Observação importanteImportante

    A instância do SQL Server reconhecerá essa configuração quando a opção do servidor cross db ownership chaining for 0 (OFF). Quando cross db ownership chaining for 1 (ON), todos os bancos de dados de usuário poderão participar de cadeias de propriedades de bancos de dados, independentemente do valor dessa opção. Essa opção é definida usando sp_configure.

    Para configurar essa opção, é necessário que se tenha a permissão CONTROL SERVER no banco de dados. A opção DB_CHAINING não pode ser definida nesses bancos de dados do sistema: master, model e tempdb.

    O status dessa opção pode ser determinado examinando a coluna is_db_chaining_on na exibição do catálogo sys.databases.

    Para obter mais informações, consulte Cadeias de propriedade.

  • TRUSTWORTHY { ON | OFF }

    • ON
      Os módulos de banco de dados (por exemplo, funções definidas pelo usuário ou procedimentos armazenados) que usam um contexto de representação poderão acessar recursos fora do banco de dados.

    • OFF
      Os módulos de banco de dados em um contexto de representação não poderão acessar recursos fora do banco de dados.

    TRUSTWORTHY é definido como OFF sempre que o banco de dados está anexado.

    Por padrão, todos os bancos de dados do sistema, exceto o banco de dados msdb, têm TRUSTWORTHY definido como OFF. O valor não pode ser alterado para os bancos de dados model e tempdb. É recomendável nunca definir a opção TRUSTWORTHY como ON para o banco de dados master.

    Para configurar essa opção, é necessário que se tenha a permissão CONTROL SERVER no banco de dados.

    O status dessa opção pode ser determinado examinando a coluna is_trustworthy_on na exibição do catálogo sys.databases.

<parameterization_option> ::=

Controla a opção parametrização.

  • PARAMETERIZATION { SIMPLE | FORCED }

    • SIMPLE
      As consultas são parametrizadas com base no comportamento padrão do banco de dados. Para obter mais informações, consulte Parametrização simples.

    • FORCED
      O SQL Server parametriza todas as consultas feitas no banco de dados. Para obter mais informações, consulte Parametrização forçada.

    A configuração atual dessa opção pode ser determinada examinando-se a coluna is_parameterization_forced na exibição do catálogo sys.databases.

<recovery_option> ::=

Controla as opções de recuperação de banco de dados e a verificação de erros de E/S do disco.

  • FULL
    Fornece recuperação completa depois de falha de mídia usando backups do log de transação. Se um arquivo de dados for danificado, a recuperação de mídia poderá recuperar todas as transações confirmadas. Para obter mais informações, consulte Backup com o modelo de recuperação completa.

  • BULK_LOGGED
    Fornece uma recuperação após uma falha de mídia, combinando o melhor desempenho e a quantidade mínima de uso do espaço de log para determinadas operações em larga escala ou em massa. Para obter informações sobre quais operações são bulk-logged, consulte Operações que podem ser minimamente registradas em log. No modelo de recuperação BULK_LOGGED, o registro para essas operações é mínimo. Para obter mais informações, consulte Backup no modelo de recuperação com log de operações em massa.

  • SIMPLE
    Uma estratégia simples de backup que usa um espaço de log mínimo é fornecida. O espaço de log poderá ser reutilizado automaticamente quando não for mais necessário à recuperação de falha de servidor. Para obter mais informações, consulte Backup com o modelo de recuperação simples.

    Observação importanteImportante

    O modelo de recuperação simples é mais fácil de gerenciar que os outros dois modelos, mas às custas de uma exposição maior à perda de dados se um arquivo de dados for danificado. Todas as alterações desde o backup mais recente do banco de dados ou diferencial serão perdidas e terão que ser reinseridas manualmente.

O modelo de recuperação padrão é determinado pelo modelo de recuperação do banco de dados model. Para obter mais informações sobre como selecionar o modelo de recuperação apropriado, consulte Escolhendo o modelo de recuperação para um banco de dados.

O status dessa opção pode ser determinado examinando-se as colunas recovery_model e recovery_model_desc no catálogo de exibição sys.databases ou a propriedade Recovery da função DATABASEPROPERTYEX.

  • TORN_PAGE_DETECTION { ON | OFF }

    • ON
      Páginas incompletas podem ser detectadas pelo Mecanismo de Banco de Dados.

    • OFF
      Páginas incompletas não podem ser detectadas pelo Mecanismo de Banco de Dados.

    Observação importanteImportante

    A estrutura de sintaxe TORN_PAGE_DETECTION ON | OFF será removida em uma versão futura do SQL Server. Evite usar essa estrutura de sintaxe em novos trabalhos de desenvolvimentos e planeje modificar os aplicativos que a utilizam atualmente. Use a opção PAGE_VERIFY em seu lugar.

  • PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
    Descobre páginas de banco de dados danificadas por erros de caminho de E/S do disco. Os erros de caminho de E/S do disco podem ser a causa de problemas de corrupção de banco de dados e, em geral, são causados por quedas de energia ou falhas no disco que ocorrem no momento da gravação da página no disco.

    • CHECKSUM
      Calcula uma soma de verificação com base no conteúdo da página inteira e armazena o valor no cabeçalho da página quando a página é gravada em disco. Quando a página é lida a partir do disco, a soma de verificação é recalculada e comparada ao valor da soma de verificação armazenado no cabeçalho da página. Se os valores não forem correspondentes, a mensagem de erro 824 (indicando uma falha na soma de verificação) será informada no log de erros do SQL Server e no log de eventos do Windows. Uma falha de soma de verificação indica um problema no caminho de E/S. Para determinar a causa principal, é necessária uma inspeção de hardware, drivers de firmware, BIOS, drivers de filtro (como software antivírus) e outros componentes de caminho de E/S.

    • TORN_PAGE_DETECTION
      Salva um padrão específico de 2 bits para cada setor de 512 bytes na página de banco de dados de 8 quilobytes (KB) e o armazena no cabeçalho da página do banco de dados quando a página é gravada em disco. Quando a página for lida pelo disco, os bits desativados armazenados no cabeçalho da página serão comparados às informações do setor da página real. Valores não correspondentes indicam que apenas parte da página foi gravada em disco. Nessa situação, a mensagem de erro 824 (indicando um erro de página interrompida) é relatada ao log de erros do SQL Server e ao log de eventos do Windows. Páginas interrompidas serão detectadas normalmente através da recuperação de banco de dados se realmente for uma gravação incompleta de uma página. Entretanto, outras falhas de caminho de E/S podem gerar uma página interrompida a qualquer momento.

    • NONE
      A página do banco de dados não gerará um valor CHECKSUM ou TORN_PAGE_DETECTION. O SQL Server não verificará uma soma de verificação ou uma página interrompida durante uma leitura, mesmo que um valor CHECKSUM ou TORN_PAGE_DETECTION esteja presente no cabeçalho da página.

    Considere os seguintes pontos importantes ao usar a opção PAGE_VERIFY:

    • No SQL Server 2005 e SQL Server 2008, o padrão é CHECKSUM. No SQL Server 2000, TORN_PAGE_DETECTION é o padrão.

    • Quando um usuário ou banco de dados do sistema é atualizado para o SQL Server 2005 ou SQL Server 2008, o valor PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) é retido. Recomendamos o uso de CHECKSUM. 

      ObservaçãoObservação

      Em versões anteriores do SQL Server, a opção de banco de dados PAGE_VERIFY é definida como NONE para o banco de dados tempdb e não pode ser modificada. No SQL Server 2008, o valor padrão do banco de dados tempdb é CHECKSUM para instalações novas do SQL Server. Ao atualizar uma instalação do SQL Server, o valor padrão permanece NONE. A opção pode ser modificada. É recomendável usar CHECKSUM para o banco de dados tempdb.

    • TORN_PAGE_DETECTION pode usar menos recursos, mas fornece um subconjunto mínimo da proteção CHECKSUM.

    • PAGE_VERIFY pode ser definido sem que o banco de dados fique offline, seja bloqueado ou a simultaneidade de usuário seja impedida nele.

    • CHECKSUM é mutuamente exclusivo com TORN_PAGE_DETECTION. As duas opções não podem ser habilitadas ao mesmo tempo.

    Quando a falha em uma página interrompida ou soma de verificação é detectada, é possível recuperá-las restaurando os dados ou recriando o índice se a falha estiver limitada apenas a páginas de índice. Se você encontrar uma falha de soma de verificação, para determinar o tipo de página de banco de dados ou páginas afetadas, execute DBCC CHECKDB. Para obter mais informações sobre opções de restauração, consulte Argumentos de RESTORE (Transact-SQL). Embora a restauração de dados resolva o problema de corrupção de dados, sua causa, por exemplo, falha do hardware de disco, deve ser diagnosticada e corrigida assim que possível para evitar a repetição dos erros.

    O SQL Server repetirá mais quatro vezes qualquer leitura que falhe com uma soma de verificação, página interrompida ou outro erro de E/S. Se a leitura tiver êxito em qualquer uma das novas tentativas, uma mensagem será gravada no log de erros e o comando que disparou a leitura continuará. Se as novas tentativas falharem, o comando falhará com a mensagem de erro 824.

    Para obter mais informações sobre soma de verificação, página interrompida, nova tentativa de leitura, mensagens de erro 823 e 824 e outros recursos de auditoria de E/S do SQL Server, consulte este site da Microsoft.

    A configuração atual dessa opção pode ser determinada examinando-se a coluna page_verify_option na exibição do catálogo sys.databases ou a propriedade IsTornPageDetectionEnabled da função DATABASEPROPERTYEX.

<service_broker_option>::=

Controla as seguintes opções do Service Broker: habilita ou desabilita a entrega de mensagens, define um novo identificador do Service Broker ou define as prioridades de conversa como ON ou OFF. Para obter mais informações sobre a entrega de mensagens e identificadores do Service Broker, consulte Gerenciando identidades do Service Broker. Para obter mais informações sobre os níveis de prioridade de conversação, consulte Prioridades de conversação. Para obter exemplos que mostram como usar a opção HONOR_BROKER_PRIORITY, consulte Gerenciando as prioridades de conversação.

  • ENABLE_BROKER
    Especifica que o Service Broker está habilitado para o banco de dados especificado. A entrega de mensagens é iniciada, e a sinalização is_broker_enabled é definida como true na exibição do catálogo sys.databases. O banco de dados retém o identificador do Service Broker existente.

    ObservaçãoObservação

    ENABLE_BROKER requer um bloqueio de banco de dados exclusivo. Se outras sessões bloquearam recursos no banco de dados, ENABLE_BROKER esperará até que as outras sessões liberem os bloqueios. Para habilitar o Service Broker em um banco de dados de usuário, verifique se nenhuma outra sessão está usando o banco de dados antes de executar a instrução ALTER DATABASE SET ENABLE_BROKER, por exemplo, colocando o banco de dados no modo de usuário único. Para habilitar o Service Broker no banco de dados msdb, primeiro interrompa o agente SQL Server de modo que Service Broker possa obter o bloqueio necessário.

  • DISABLE_BROKER
    Especifica que o Service Broker está desabilitado para o banco de dados especificado. A entrega de mensagens é interrompida, e a sinalização is_broker_enabled é definida como false na exibição do catálogo sys.databases. O banco de dados retém o identificador do Service Broker existente.

  • NEW_BROKER
    Especifica que o banco de dados deve receber um novo identificador do Broker. Como o banco de dados é considerado como um novo Service Broker, todas as conversações existentes nele são imediatamente removidas sem produzir mensagens de caixa de diálogo de término. Qualquer rota que referencia o antigo identificador do Service Broker deve ser recriada novamente com o novo identificador.

  • ERROR_BROKER_CONVERSATIONS
    Especifica que a entrega de mensagens do Service Broker está habilitada. Isso preserva o identificador do Service Broker existente no banco de dados. O Service Broker encerra todas as conversações no banco de dados com um erro. Isso permite que os aplicativos executem a limpeza regular das conversações existentes.

  • HONOR_BROKER_PRIORITY {ON | OFF}

    • ON
      As operações de envio levam em conta os níveis de prioridade atribuídos às conversações. As mensagens de conversações com altos níveis de prioridade são enviadas antes das mensagens de conversações com baixos níveis de prioridade atribuídos.

    • OFF
      As operações de envio são executadas como se todas as conversações tivessem o nível de prioridade padrão.

    As alterações na opção HONOR_BROKER_PRIORITY entram em vigor imediatamente para os novos diálogos ou diálogos que não tenham mensagens esperando para serem enviadas. As caixas de diálogo com mensagens esperando para serem enviadas quando ALTER DATABASE for executado não assumirão a nova configuração até que algumas das mensagens da caixa de diálogo sejam enviadas. O tempo necessário para que todas as caixas de diálogo comecem a usar a nova configuração pode variar consideravelmente.

    A configuração atual dessa propriedade é relatada na coluna is_broker_priority_honored na exibição do catálogo sys.databases.

<snapshot_option>::=

Determina o nível de isolamento da transação.

  • ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

    • ON
      Habilita a opção Instantâneo no nível do banco de dados. Quando ela está habilitada, as instruções DML começam a gerar versões de linha mesmo quando nenhuma transação está utilizando o Isolamento de Instantâneos. Ao habilitar essa opção, as transações podem especificar o nível de isolamento da transação SNAPSHOT. Ao executar uma transação no nível de isolamento SNAPSHOT, todas as instruções consultam um instantâneo de dados, se houver um no início da instrução. Se uma transação que executa o nível de isolamento SNAPSHOT acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deverá ser definido como ON em todos os bancos de dados ou cada instrução na transação deverá usar dicas de bloqueio em qualquer referência em uma cláusula FROM para uma tabela em um banco de dados em que ALLOW_SNAPSHOT_ISOLATION seja OFF.

    • OFF
      Desabilita a opção Instantâneo no nível do banco de dados. As transações não podem especificar o nível de isolamento da transação SNAPSHOT.

    Ao definir ALLOW_SNAPSHOT_ISOLATION para um novo estado (de ON para OFF, ou de OFF, para ON), ALTER DATABASE não retorna o controle para o chamador até que todas as transações existentes no banco de dados sejam confirmadas. Se o banco de dados já estiver no estado especificado na instrução ALTER DATABASE, o controle será retornado ao chamador imediatamente. Se a instrução ALTER DATABASE não for retornada rapidamente, use sys.dm_tran_active_snapshot_database_transactions para determinar se há transações de longa duração. Se a instrução ALTER DATABASE for cancelada, o banco de dados permanecerá no estado que estava quando ALTER DATABASE foi iniciada. A exibição do catálogo sys.databases indica o estado de transações de isolamento de instantâneo no banco de dados. Se snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF fará uma pausa de seis segundos e tentará novamente executar a operação.

    Não é possível alterar o estado de ALLOW_SNAPSHOT_ISOLATION se o banco de dados for OFFLINE.

    Se você definir ALLOW_SNAPSHOT_ISOLATION em um banco de dados READ_ONLY, a configuração será retida se o banco de dados for definido mais tarde como READ_WRITE.

    É possível alterar as configurações ALLOW_SNAPSHOT_ISOLATION para os bancos de dados master, model, msdb e tempdb. Se você alterar a configuração para tempdb, ela será retida sempre que a instância do Mecanismo de Banco de Dados for interrompida e reiniciada. Ao alterar a configuração para model, ela se tornará o padrão para qualquer novo banco de dados que for criado, exceto para tempdb.

    A opção é ON, por padrão, para os bancos de dados master e msdb.

    A configuração atual dessa opção pode ser determinada examinando-se a coluna snapshot_isolation_state na exibição do catálogo sys.databases.

  • READ_COMMITTED_SNAPSHOT { ON | OFF }

    • ON
      Habilita a opção Instantâneo de Leitura Confirmado no nível do banco de dados. Quando ela está habilitada, as instruções DML começam a gerar versões de linha mesmo quando nenhuma transação está utilizando o Isolamento de Instantâneos. Quando essa opção é habilitada, as transações especificando o nível de isolamento de leitura confirmado usam a versão de linhas em vez do bloqueio. Quando uma transação é executada no nível de isolamento de leitura confirmado, todas as instruções consultam um instantâneo de dados, se houver um no início da instrução.

    • OFF
      Desabilita a opção Instantâneo de Leitura Confirmado no nível do banco de dados. As transações que especificam o nível de isolamento READ COMMITTED usam bloqueio.

    Para definir READ_COMMITTED_SNAPSHOT como ON ou OFF, não deve haver nenhuma conexão ativa com o banco de dados exceto para a que está executando o comando ALTER DATABASE. Entretanto, o banco de dados não precisa estar no modo de usuário único. Não é possível alterar o estado dessa opção quando o banco de dados for OFFLINE.

    Se você definir READ_COMMITTED_SNAPSHOT em um banco de dados READ_ONLY, a configuração será retida quando o banco de dados for definido mais tarde como READ_WRITE.

    READ_COMMITTED_SNAPSHOT não pode ser definido como ON para os bancos de dados de sistema master, tempdb ou msdb. Se você alterar a configuração para model, ela se tornará o padrão para qualquer novo banco de dados que for criado, exceto para tempdb.

    A configuração atual dessa opção pode ser determinada examinando-se a coluna is_read_committed_snapshot_on na exibição do catálogo sys.databases.

<sql_option>::=

Controla as opções de conformidade ANSI no nível de banco de dados.

  • ANSI_NULL_DEFAULT { ON | OFF }
    Determina o valor padrão, NULL ou NOT NULL, de uma coluna, tipo de dados de alias ou tipo de dados CLR definido pelo usuário para as quais a nulidade não é definida explicitamente nas instruções CREATE TABLE ou ALTER TABLE. As colunas definidas com restrições seguem as regras de restrição apesar dessa configuração.

    • ON
      O valor padrão é NULL.

    • OFF
      O valor padrão é NOT NULL.

    As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_NULL_DEFAULT. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_NULL_DEFAULT de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_NULL_DFLT_ON (Transact-SQL).

    Para compatibilidade ANSI, definir a opção de banco de dados ANSI_NULL_DEFAULT como ON altera o banco de dados padrão para NULL.

    O status dessa opção pode ser determinado examinando-se a coluna is_ansi_null_default_on na exibição do catálogo sys.databases ou a propriedade IsAnsiNullDefault da função DATABASEPROPERTYEX.

  • ANSI_NULLS { ON | OFF }

    • ON
      Todas as comparações com um valor nulo são avaliadas como UNKNOWN.

    • OFF
      As comparações de valores não UNICODE com um valor nulo são avaliadas como TRUE se ambos os valores forem NULL.

    Observação importanteImportante

    Em uma versão futura do SQL Server, ANSI_NULLS sempre estará ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

    As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_NULLS. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_NULLS de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_NULLS (Transact-SQL).

    SET ANSI_NULLS também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

    O status dessa opção pode ser determinado examinando-se a coluna is_ansi_nulls_on na exibição do catálogo sys.databases ou a propriedade IsAnsiNullsEnabled da função DATABASEPROPERTYEX.

  • ANSI_PADDING { ON | OFF }

    • ON
      As cadeias de caracteres são preenchidas com o mesmo comprimento antes da conversão ou inserção em um tipo de dados varchar ou nvarchar.

      Espaços em branco à direita nos valores de caracteres inseridos nas colunas varchar ou nvarchar e os zeros à direita nos valores binários inseridos nas colunas varbinary não serão cortados. Os valores não são preenchidos com o tamanho da coluna.

    • OFF
      Espaços em branco à direita para varchar ou nvarchar e zeros para varbinary são cortados.

    Quando OFF é especificado, essa configuração afeta apenas a definição de novas colunas.

    Observação importanteImportante

    Em uma versão futura do SQL Server, ANSI_PADDING sempre será ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que atualmente o utilizam.

    As colunas char(n) e binary(n) que permitem valores nulos são preenchidas com o tamanho da coluna quando ANSI_PADDING é definido como ON, mas os espaços em branco à direita e os zeros são cortados quando ANSI_PADDING é definido como OFF. As colunas char(n) e binary(n) que não permitem valores nulos são preenchidas com o tamanho da coluna.

    As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_NULL_PADDING. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_PADDING de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_PADDING (Transact-SQL).

    Observação importanteImportante

    Recomenda-se que ANSI_PADDING sempre seja definido como ON. SET ANSI_PADDING deve ser ON ao criar ou manipular índices em colunas computadas ou exibições indexadas.

    O status dessa opção pode ser determinado examinando-se a coluna is_ansi_padding_on na exibição do catálogo sys.databases ou a propriedade IsAnsiPaddingEnabled da função DATABASEPROPERTYEX.

  • ANSI_WARNINGS { ON | OFF }

    • ON
      Erros ou avisos são emitidos quando condições como “divisão por zero” ou “valores nulos” aparecerem em funções de agregação.

    • OFF
      Nenhum aviso é emitido e os valores nulos são retornados quando condições como “dividir por zero” ocorrem.

    SET ANSI_WARNINGS também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

    As configurações no nível de conexão que são definidas usando uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_WARNINGS. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_WARNINGS de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_WARNINGS (Transact-SQL).

    O status dessa opção pode ser determinado examinando-se a coluna is_ansi_warnings_on na exibição do catálogo sys.databases ou a propriedade IsAnsiWarningsEnabled da função DATABASEPROPERTYEX.

  • ARITHABORT { ON | OFF }

    • ON
      Uma consulta é encerrada quando ocorre um estouro ou erro de divisão por zero durante a execução da consulta.

    • OFF
      Uma mensagem de aviso é exibida quando um desses erros ocorre, mas a consulta, o lote ou a transação continuam a ser processados como se nenhum erro tivesse ocorrido.

    SET ARITHABORT também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

    O status dessa opção pode ser determinado examinando-se a coluna is_arithabort_on na exibição do catálogo sys.databases ou a propriedade IsArithmeticAbortEnabled da função DATABASEPROPERTYEX.

  • COMPATIBILITY_LEVEL { 80 | 90 | 100 }
    Para obter mais informações, consulte Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

  • CONCAT_NULL_YIELDS_NULL { ON | OFF }

    • ON
      O resultado de uma operação de concatenação será NULL quando qualquer operando for NULL. Por exemplo, concatenar a cadeia de caracteres "This is" e NULL gera o valor NULL, em vez do valor "This is".

    • OFF
      O valor nulo é tratado como uma cadeia de caracteres vazia.

    CONCAT_NULL_YIELDS_NULL deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

    Observação importanteImportante

    Em uma versão futura do SQL Server, CONCAT_NULL_YIELDS_NULL sempre estará ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

    As configurações no nível de conexão que são definidas usando uma instrução SET substituem a configuração no nível de banco de dados padrão para CONCAT_NULL_YIELDS_NULL. Por padrão, clientes ODBC e OLE DB emitem uma configuração CONCAT_NULL_YIELDS_NULL de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

    O status dessa opção pode ser determinado examinando-se a coluna is_concat_null_yields_null_on na exibição do catálogo sys.databases ou a propriedade IsNullConcat da função DATABASEPROPERTYEX.

  • QUOTED_IDENTIFIER { ON | OFF }

    • ON
      As aspas duplas podem ser utilizadas para delimitar identificadores.

      Todas as cadeias de caracteres delimitadas por aspas duplas são interpretadas como identificadores de objeto. Os identificadores entre aspas não precisam seguir as regras Transact-SQL para identificadores. Eles podem ser palavras-chave e incluir caracteres geralmente não permitidos nos identificadores Transact-SQL. Se o sinal de aspas simples (') fizer parte da cadeia de caracteres literal, ele poderá ser representado por aspas duplas (").

    • OFF
      Os identificadores não podem estar entre aspas e devem seguir todas as regras de identificadores Transact-SQL. Literais podem ser delimitados por aspas simples ou duplas.

    O SQL Server também permite que os identificadores sejam delimitados por colchetes ([ ]). Os identificadores entre colchetes podem sempre ser usados, seja qual for a configuração de QUOTED_IDENTIFIER. Para obter mais informações, consulte Identificadores delimitados (Mecanismo de Banco de Dados).

    Quando uma tabela é criada, a opção QUOTED IDENTIFIER sempre é armazenada como ON nos metadados da tabela, mesmo que a opção esteja definida como OFF quando a tabela é criada.

    As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para QUOTED_IDENTIFIER. Por padrão, clientes ODBC e OLE DB emitem uma configuração QUOTED_IDENTIFIER de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

    O status dessa opção pode ser determinado examinando-se a coluna is_quoted_identifier_on na exibição do catálogo sys.databases ou a propriedade IsQuotedIdentifiersEnabled da função DATABASEPROPERTYEX.

  • NUMERIC_ROUNDABORT { ON | OFF }

    • ON
      Um erro é gerado quando ocorre perda de precisão em uma expressão.

    • OFF
      As perdas de precisão não geram mensagens de erro e o resultado é arredondado de acordo com a precisão da coluna ou variável que armazena o resultado.

    NUMERIC_ROUNDABORT deve ser definido como OFF ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

    O status dessa opção pode ser determinado examinando-se a coluna is_numeric_roundabort_on na exibição do catálogo sys.databases ou a propriedade IsNumericRoundAbortEnabled da função DATABASEPROPERTYEX.

  • RECURSIVE_TRIGGERS { ON | OFF }

    • ON
      O disparo recursivo de gatilhos AFTER é permitido.

    • OFF
      Apenas o disparo recursivo direto de gatilhos AFTER não é permitido. Para desabilitar também a recursão indireta de gatilhos AFTER, defina a opção do servidor de gatilhos aninhados como 0 usando sp_configure.

    ObservaçãoObservação

    Somente a recursão direta será evitada quando RECURSIVE_TRIGGERS for definido como OFF. Para desabilitar a recursão indireta, é necessário definir também a opção do servidor nested triggers como 0.

    O status dessa opção pode ser determinado examinando-se a coluna is_recursive_triggers_on na exibição do catálogo sys.databases ou a propriedade IsRecursiveTriggersEnabled da função DATABASEPROPERTYEX.

WITH <termination>::=

Especifica quando reverter transações incompletas quando há transição do banco de dados de um estado para outro. Se a cláusula de término for omitida, a instrução ALTER DATABASE aguardará indefinidamente se houver algum bloqueio no banco de dados. Somente uma cláusula de término pode ser especificada e ela sucede as cláusulas SET.

ObservaçãoObservação

Nem todas as opções de banco de dados usam a cláusula WITH <termination>. Para obter mais informações, consulte a tabela em "Opções de configuração" na seção Comentários.

  • ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
    Especifica se a reversão deve ser feita após o número de segundos especificado ou imediatamente.

  • NO_WAIT
    Especifica que, se a alteração de estado do banco de dados ou da opção solicitada não puder ser concluída imediatamente sem esperar que as transações sejam confirmadas ou revertidas por si mesmas, a solicitação falhará.

Comentários

Opções de configuração

Para recuperar as configurações atuais das opções de banco de dados, use a exibição do catálogo sys.databases ou DATABASEPROPERTYEX. Para obter uma lista de valores padrão atribuídos ao banco de dados quando ele é criado pela primeira vez, consulte Definindo opções do banco de dados.

Depois de definir uma opção de banco de dados, a modificação entra em vigor imediatamente.

Para alterar os valores padrão de qualquer uma das opções do banco de dados para todos os bancos de dados criados recentemente, altere a opção do banco de dados apropriada no banco de dados model.

Nem todas as opções de banco de dados usam a cláusula WITH <termination> ou podem ser especificadas em combinação com outras opções. A tabela a seguir lista essas opções e seu status de opção e término.

Categoria de opções

Pode ser especificado com outras opções

Pode usar a cláusula WITH <termination>

<db_state_option>

Sim

Sim

<db_user_access_option>

Sim

Sim

<db_update_option>

Sim

Sim

<external_access_option>

Sim

Não

<cursor_option>

Sim

Não

<auto_option>

Sim

Não

<sql_option>

Sim

Não

<recovery_option>

Sim

Não

<database_mirroring_option>

Não

Não

ALLOW_SNAPSHOT_ISOLATION

Não

Não

READ_COMMITTED_SNAPSHOT

Não

Sim

<service_broker_option>

Sim

Não

DATE_CORRELATION_OPTIMIZATION

Sim

Sim

<parameterization_option>

Sim

Sim

<change_tracking_option>

Sim

Sim

<db_encryption>

Sim

Não

O plano de cache para a instância do SQL Server é limpo definindo-se uma das seguintes opções:

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

 

A limpeza do cache do esquema gera uma recompilação de todos os planos de execução subseqüentes e pode provocar uma queda repentina e temporária no desempenho da consulta. Para cada armazenamento em cache limpo no cache de plano, o log de erros do SQL Server contém a seguinte mensagem informativa: "SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache '% s' (parte do cache de plano) devido a alguma manutenção de banco de dados ou a operações de reconfiguração". Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja liberado dentro desse intervalo de tempo. 

Exemplos

A. Opções de configuração em um banco de dados

O exemplo a seguir define o modelo de recuperação e as opções de verificação de página de dados para o banco de dados de exemplo AdventureWorks .

USE master;
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

A. Configurando o banco de dados como READ_ONLY

Alterar o estado de um banco de dados ou grupo de arquivos para READ_ONLY ou READ_WRITE requer acesso exclusivo ao banco de dados. O exemplo a seguir define o banco de dados como o modo SINGLE_USER para obter acesso exclusivo. Em seguida, o exemplo define o estado do banco de dados AdventureWorks como READ_ONLY e retorna o acesso ao banco de dados para todos os usuários.

ObservaçãoObservação

Este exemplo usa a opção de término WITH ROLLBACK IMMEDIATE na primeira instrução ALTER DATABASE. Todas as transações incompletas serão revertidas e qualquer outra conexão com o banco de dados de exemplo AdventureWorks será desconectada imediatamente.

 
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

C. Habilitando o isolamento de instantâneo em um banco de dados

O exemplo a seguir habilita a opção de estrutura de isolamento de instantâneo para o banco de dados AdventureWorks.

USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO

O conjunto de resultados mostra que a estrutura de isolamento de instantâneo está habilitada.

name            snapshot_isolation_state  description
--------------- ------------------------  -----------
AdventureWorks  1                         ON

D. Ativando, modificando e desabilitando o controle de alterações

O exemplo a seguir habilita o controle de alterações no banco de dados AdventureWorks e define o período de retenção para 4 dias.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

O exemplo a seguir mostra como alterar o período de retenção para 3 dias.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

O exemplo a seguir mostra como desabilitar o controle de alterações no banco de dados AdventureWorks.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

Histórico de Alterações para o Documento

Conteúdo atualizado

Revisadas as descrições de AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS e AUTO_UPDATE_STATISTICS_ASYNC tendo em vista maior precisão.