ALTER DATABASE (Transact-SQL)

Data aggiornamento: 12 dicembre 2006

Modifica un database oppure i file e i filegroup associati al database. Consente di aggiungere o rimuovere file e filegroup in un database, modificare gli attributi di un database oppure dei relativi file e filegroup, modificare le regole di confronto e impostare le opzioni del database. Non è possibile modificare snapshot di database. Per la modifica delle opzioni di database associate alla replica, utilizzare sp_replicationdboption.

Convenzioni della sintassi Transact-SQL

Sintassi

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
  | <set_database_options>
  | MODIFY NAME = new_database_name 
  | COLLATE collation_name
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = 'os_file_name' ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

<set_database_options>::=
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

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

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

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

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

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

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

<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 }
}

<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

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

<database_mirroring_option> ::= 
{ <partner_option> | <witness_option> }
    <partner_option> ::=
    PARTNER { = 'partner_server' 
            | FAILOVER 
            | FORCE_SERVICE_ALLOW_DATA_LOSS
            | OFF
            | RESUME 
            | SAFETY { FULL | OFF }
            | SUSPEND 
            | TIMEOUT integer
            }
    <witness_option> ::=
    WITNESS { = 'witness_server' 
            | OFF 
            }

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

<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<termination> ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Argomenti

• database_name
  Nome del database da modificare.

• MODIFY NAME **=**new_database_name
  Rinomina il database con il nome specificato in new_database_name.

• COLLATE collation_name
  Specifica le regole di confronto per il database. collation_name può essere un nome di regole di confronto Windows o SQL. Se omesso, al database vengono assegnate le regole di confronto dell'istanza di SQL Server.

  Per ulteriori informazioni sui nomi di regole di confronto Windows e SQL, vedere COLLATE (Transact-SQL).

<add_or_modify_files>::=

Specifica i file da aggiungere, rimuovere o modificare.

• ADD FILE
  Aggiunge un file al database.

  • TO FILEGROUP { filegroup_name }
    Specifica il filegroup in cui aggiungere il file specificato. Per visualizzare i filegroup correnti e determinare il filegroup attualmente predefinito, utilizzare la vista del catalogo sys.filegroups.

• ADD LOG FILE
  Aggiunge un file di log al database specificato.

• REMOVE FILE logical_file_name
  Rimuove la descrizione del file logico da un'istanza di SQL Server ed elimina il file fisico. Il file può essere rimosso solo se è vuoto.

  • logical_file_name
    Nome logico utilizzato in SQL Server per fare riferimento al file.

• MODIFY FILE
  Specifica il file da modificare. È possibile modificare una sola proprietà <filespec> alla volta. L'opzione NAME deve essere sempre specificata in <filespec> per identificare il file da modificare. Se si specifica l'opzione SIZE, le nuove dimensioni del file devono essere superiori a quelle correnti.

  Per modificare il nome logico di un file di dati o di un file di log, specificare il nome del file logico da rinominare nella clausola NAME e specificare il nuovo nome logico per il file nella clausola NEWNAME. Ad esempio:

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Per spostare un file di dati o un file di log in una nuova posizione, specificare il nome di file logico corrente nella clausola NAME e specificare il nuovo percorso e il nome del file nel sistema operativo nella clausola FILENAME. Ad esempio:

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  Per lo spostamento di un catalogo full-text, specificare solo il nuovo percorso nella clausola FILENAME, senza indicare il nome del file nel sistema operativo.

  Per ulteriori informazioni, vedere Spostamento dei file del database.

<filespec>::=

Controlla le proprietà del file.

• NAME logical_file_name
  Specifica il nome logico del file.

  • logical_file_name
    Nome logico utilizzato in un'istanza di SQL Server per fare riferimento al file.

• NEWNAME new_logical_file_name
  Specifica un nuovo nome logico per il file.

  • new_logical_file_name
    Nome con cui sostituire il nome di file logico esistente. Il nome deve essere univoco all'interno del database e conforme alle regole per gli identificatori. Il nome può essere costituito da una costante per valori di carattere o Unicode, da un identificatore regolare o da un identificatore delimitato. Per ulteriori informazioni, vedere Utilizzo degli identificatori come nomi di oggetti.

• FILENAME 'os_file_name'
  Specifica il nome di file (fisico) nel sistema operativo.

  • ' os_file_name '
    Percorso e nome di file utilizzato dal sistema operativo al momento della creazione del file. Il file deve trovarsi nel server in cui è installato SQL Server. Il percorso specificato deve essere esistente prima di eseguire l'istruzione ALTER DATABASE.

    Non è consentita l'impostazione dei parametri SIZE, MAXSIZE e FILEGROWTH se si specifica un percorso UNC per il file.

    I file di dati non dovrebbero essere memorizzati in file system compressi, a meno che tali file non siano file secondari di sola lettura o il database non sia di sola lettura. I file di log non devono mai essere posizionati in file system compressi. Per ulteriori informazioni, vedere Filegroup di sola lettura e compressione.

    Se il file si trova in una partizione non formattata del sistema operativo, nell'argomento os_file_name è necessario specificare solo la lettera dell'unità di una partizione non formattata esistente. In ogni partizione non formattata è possibile inserire un solo file.

• SIZE size
  Specifica le dimensioni del file.

  • size
    Dimensioni del file.

    Quando viene specificato con ADD FILE, size corrisponde alle dimensioni iniziali del file. Se specificato con MODIFY FILE, size corrisponde alle nuove dimensioni del file, che devono essere superiori a quelle correnti.

    Se non si specifica il parametro size per il file primario, Motore di database di SQL Server 2005 utilizza le dimensioni del file primario nel database model. Se si specifica un file di dati o di log secondario senza impostare il parametro size per il file, Motore di database assegna dimensioni di 1 MB al file.

    È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se si desidera specificare una frazione di megabyte, convertire il valore in kilobyte moltiplicando il numero per 1024. Ad esempio, specificare 1536 KB anziché 1,5 MB (1,5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Specifica le dimensioni massime consentite per l'aumento di dimensioni del file.

  • max_size
    Dimensioni massime del file. È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se non si specifica max_size, le dimensioni del file aumenteranno fino a quando il disco risulta pieno.
  • UNLIMITED
    Specifica che le dimensioni del file aumentano fino a quando il disco risulta pieno. Quando si specifica un aumento illimitato, in SQL Server 2005 le dimensioni massime per i file di log sono di 2 TB e le dimensioni massime per i file di dati sono di 16 TB.

• FILEGROWTH growth_increment
  Specifica l'incremento per l'aumento automatico delle dimensioni del file. Il valore impostato per il parametro FILEGROWTH di un file non può essere superiore al valore del parametro MAXSIZE.

  • growth_increment
    Quantità di spazio aggiunta al file ogni volta che è necessario spazio aggiuntivo.

    È possibile specificare il valore in MB, KB, GB, TB oppure come percentuale (%). Se si specifica un valore senza il suffisso MB, KB o %, il suffisso predefinito è MB. Se si utilizza il suffisso %, l'incremento corrisponde alla percentuale delle dimensioni del file specificata quando si verifica l'incremento. Le dimensioni specificate vengono arrotondate al blocco di 64 KB più prossimo.

    Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.

    Se non si specifica FILEGROWTH, il valore predefinito è di 1 MB per i file di dati e del 10% per i file di log. Il valore minimo è 64 KB.

    [!NOTA] In SQL Server 2005, l'incremento predefinito per l'aumento delle dimensioni dei file di dati è stato modificato dal 10% a 1 MB. L'impostazione predefinita del 10% per i file di log è rimasta invariata.

• OFFLINE
  Imposta il file non in linea e rende inaccessibili tutti gli oggetti nel filegroup.

  Attenzione:
  Utilizzare questa opzione solo quando il file è danneggiato e non è possibile ripristinarlo. Un file impostato su OFFLINE può essere riportato in linea solo tramite il ripristino del file dal backup. Per ulteriori informazioni sul ripristino di un singolo file, vedere RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Aggiunge, modifica o rimuove un filegroup nel database.

• ADD FILEGROUP filegroup_name
  Aggiunge un filegroup nel database.

• REMOVE FILEGROUP filegroup_name
  Rimuove un filegroup dal database. Il filegroup può essere rimosso solo se è vuoto. Rimuove tutti i file a partire dal filegroup. Per ulteriori informazioni, vedere "REMOVE FILE logical_file_name" più indietro in questo argomento.

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
  Modifica il filegroup impostando lo stato su READ_ONLY o READ_WRITE, impostando il filegroup come predefinito per il database o modificando il nome del filegroup.

  • <filegroup_updatability_option>
    Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.
  • DEFAULT
    Imposta il filegroup specificato in filegroup_name come nuovo filegroup predefinito del database. In un database può esistere un solo filegroup predefinito. Per ulteriori informazioni, vedere Informazioni su file e filegroup.
  • NAME = new_filegroup_name
    Modifica il nome del filegroup impostando il nome specificato in new_filegroup_name.

<filegroup_updatability_option>::=

Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

• READ_ONLY | READONLY
  Specifica che il filegroup è di sola lettura. Non sono consentiti aggiornamenti degli oggetti nel filegroup. Non è possibile rendere di sola lettura il filegroup primario. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per ulteriori informazioni, vedere la clausola SINGLE_USER.

  I database di sola lettura non consentono modifiche dei dati e pertanto:

  • Non viene eseguito il recupero automatico all'avvio del sistema.
  • La compattazione del database non è possibile.
  • Non vengono attivati blocchi nei database di sola lettura e ciò può portare a migliori prestazioni di esecuzione delle query.

  [!NOTA] La parola chiave READONLY verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitarne l'utilizzo in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare READ_ONLY in alternativa.

• READ_WRITE | READWRITE
  Specifica che il filegroup è di lettura/scrittura. Sono consentiti aggiornamenti degli oggetti contenuti nel filegroup. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per ulteriori informazioni, vedere la clausola SINGLE_USER.

  [!NOTA] La parola chiave READWRITE verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitarne l'utilizzo in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata. Utilizzare READ_WRITE in alternativa.

Per determinare lo stato di queste opzioni, è possibile esaminare la colonna is_read_only nella vista del catalogo sys.databases oppure la proprietà Updateability della funzione DATABASEPROPERTYEX.

<db_state_option>::=

Controlla lo stato del database.

• OFFLINE
  Il database viene chiuso correttamente e contrassegnato come non in linea. Non è possibile modificare il database mentre non è in linea.

• ONLINE
  Il database è aperto e disponibile per l'utilizzo.

• EMERGENCY
  Il database è contrassegnato come READ_ONLY, la registrazione è disattivata e l'accesso è limitato ai membri del ruolo predefinito del server sysadmin. L'opzione EMERGENCY viene utilizzata principalmente per attività di risoluzione dei problemi. È ad esempio possibile impostare lo stato EMERGENCY per un database contrassegnato come sospetto a causa di un file di log danneggiato. In questo modo l'amministratore di sistema potrà accedere in sola lettura al database. Solo i membri del ruolo predefinito del server sysadmin possono impostare lo stato EMERGENCY per un database.

Per determinare lo stato di questa opzione, è possibile esaminare le colonne state e state_desc nella vista del catalogo sys.databases oppure la proprietà Status della funzione DATABASEPROPERTYEX. Per ulteriori informazioni, vedere Stati del database.

Un database contrassegnato come RESTORING non può essere impostato su OFFLINE, ONLINE o EMERGENCY. Lo stato RESTORING può essere impostato durante un'operazione di ripristino attiva o quando l'operazione di ripristino di un database o di un file di log ha esito negativo a causa di un file di backup danneggiato. Per ulteriori informazioni, vedere Risposta agli errori di ripristino di SQL Server provocati da backup danneggiati.

<db_user_access_option> ::=

Controlla l'accesso degli utenti al database.

• SINGLE_USER
  Specifica che l'accesso al database è consentito a un solo utente alla volta. Se si specifica SINGLE_USER e sono presenti altri utenti connessi al database, l'istruzione ALTER DATABASE verrà bloccata fino a quando tutti gli utenti non si disconnettono dal database specificato. Per evitare questa situazione, vedere la clausola WITH <termination>.

  Il database rimane in modalità SINGLE_USER anche se l'utente che ha impostato l'opzione si disconnette. A questo punto, un altro utente (ma solo uno) potrà connettersi al database.

  Prima di impostare il database in modalità SINGLE_USER, verificare che l'opzione AUTO_UPDATE_STATISTICS_ASYNC sia impostata su OFF. Se l'opzione è impostata su ON, il thread in background utilizzato per aggiornare le statistiche stabilisce una connessione con il database che non sarà quindi accessibile in modalità utente singolo. Per visualizzare lo stato di questa opzione, è possibile esaminare la colonna is_auto_update_stats_async_on nella vista del catalogo sys.databases. Se l'opzione è impostata su ON, eseguire le operazioni seguenti:

  1. Impostare AUTO_UPDATE_STATISTICS_ASYNC su OFF.
  2. Verificare la presenza di processi asincroni attivi relativi alle statistiche eseguendo una query nella vista a gestione dinamica sys.dm_exec_background_job_queue.
  3. Se sono presenti processi attivi, consentire il completamento di tali processi o terminarli manualmente utilizzando KILL STATS JOB.

• RESTRICTED_USER
  RESTRICTED_USER consente la connessione al database solo ai membri del ruolo predefinito del database db_owner e ai membri dei ruoli predefiniti del server dbcreator e sysadmin, senza tuttavia imporre un limite al numero delle connessioni. Tutte le connessioni al database vengono interrotte entro l'intervallo di tempo specificato nella clausola di interruzione dell'istruzione ALTER DATABASE. Dopo l'attivazione dello stato RESTRICTED_USER per il database, qualsiasi tentativo di connessione da parte di utenti non qualificati verrà rifiutato.

• MULTI_USER
  Consente la connessione al database a tutti gli utenti con le autorizzazioni appropriate.

Per determinare lo stato di queste opzioni, è possibile esaminare la colonna user_access nella vista del catalogo sys.databases oppure la proprietà UserAccess della funzione DATABASEPROPERTYEX.

<db_update_option>::=

Indica se sono consentiti aggiornamenti nel database.

• READ_ONLY
  Gli utenti possono leggere i dati dal database, ma non modificarlo.

• READ_WRITE
  Il database è disponibile per operazioni di lettura e scrittura.

Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per ulteriori informazioni, vedere la clausola SINGLE_USER.

<external_access_option>::=

Determina se il database è accessibile da risorse esterne, come gli oggetti di un altro database.

• DB_CHAINING { ON | OFF }

  • ON
    Il database può essere l'origine o la destinazione di una catena di proprietà tra database.
  • OFF
    Il database non può partecipare al concatenamento della proprietà tra database.

  Importante:
  Questa impostazione verrà riconosciuta dall'istanza di SQL Server quando l'opzione del server cross db ownership chaining è impostata su 0 (OFF). Se l'opzione cross db ownership chaining è 1 (ON), tutti i database utente possono partecipare a catene di proprietà tra database, indipendentemente dal valore di questa opzione. Questa opzione viene impostata tramite sp_configure.

  Per l'impostazione di questa opzione è richiesta l'appartenenza al ruolo predefinito del server sysadmin. Non è possibile impostare l'opzione DB_CHAINING per i database di sistema master, model e tempdb.

  Per determinare lo stato dell'opzione, è possibile esaminare la colonna is_db_chaining_on nella vista del catalogo sys.databases.

  Per ulteriori informazioni, vedere Catene di proprietà.

• TRUSTWORTHY { ON | OFF }

  • ON
    I moduli di database, ad esempio funzioni definite dall'utente o stored procedure, che utilizzano un contesto di rappresentazione possono accedere a risorse esterne al database.
  • OFF
    I moduli di database in un contesto di rappresentazione non possono accedere a risorse esterne al database.

  TRUSTWORTHY è impostata su OFF ogni volta che il database è collegato.

  Per impostazione predefinita, in tutti i database di sistema, a eccezione del database msdb, TRUSTWORTHY è impostata su OFF. Per i database model e tempdb, questo valore non può essere modificato. È consigliabile evitare di impostare l'opzione TRUSTWORTHY su ON nel database master.

  Per l'impostazione di questa opzione è richiesta l'appartenenza al ruolo predefinito del server sysadmin.

  Per determinare lo stato dell'opzione, è possibile esaminare la colonna is_trustworthy_on nella vista del catalogo sys.databases.

<cursor_option>::=

Controlla le opzioni del cursore.

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    Tutti i cursori che risultano aperti al momento del commit o rollback di una transazione vengono chiusi.
  • OFF
    I cursori rimangono aperti quando viene eseguito il commit di una transazione. Quando si esegue il rollback di una transazione vengono chiusi tutti i cursori, tranne quelli definiti come INSENSITIVE o STATIC.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita del database per CURSOR_CLOSE_ON_COMMIT. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta CURSOR_CLOSE_ON_COMMIT su OFF per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  Per determinare lo stato di queste opzioni, è possibile esaminare la colonna is_cursor_close_on_commit_on nella vista del catalogo sys.databases oppure la proprietà IsCloseCursorsOnCommitEnabled della funzione DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Determina se l'ambito del cursore è LOCAL o GLOBAL.

  • LOCAL
    Se si specifica LOCAL e se un cursore non viene definito come GLOBAL al momento della creazione, l'ambito del cursore è locale rispetto al batch, alla stored procedure o al trigger in cui è stato creato. Il nome del cursore è valido soltanto in questo ambito. È possibile fare riferimento al cursore da variabili di cursore locali nel batch, nella stored procedure o nel trigger oppure da un parametro OUTPUT di stored procedure. Il cursore viene deallocato in modo implicito al termine dell'esecuzione del batch, della stored procedure o del trigger, a meno che non sia stato passato in un parametro OUTPUT. In questo caso, il cursore viene deallocato quando l'ultima variabile che vi fa riferimento viene deallocata o non è più compresa nell'ambito.
  • GLOBAL
    Se si specifica GLOBAL e se un cursore non viene definito come LOCAL al momento della creazione, l'ambito del cursore è globale rispetto alla connessione. È possibile fare riferimento al nome del cursore in qualsiasi stored procedure o batch eseguito tramite la connessione.

  Il cursore viene deallocato in modo implicito soltanto al momento della disconnessione. Per ulteriori informazioni, vedere DECLARE CURSOR (Transact-SQL).

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_local_cursor_default nella vista del catalogo sys.databases oppure la proprietà IsLocalCursorsDefault della funzione DATABASEPROPERTYEX.

<auto_option>::=

Controlla le opzioni automatiche.

• AUTO_CLOSE { ON | OFF }

  • ON
    Il database viene chiuso correttamente e le relative risorse vengono rilasciate dopo la disconnessione dell'ultimo utente.

    Il database viene riaperto automaticamente quando un utente tenta di utilizzarlo nuovamente, ad esempio tramite l'esecuzione di un'istruzione USE database_name. Se il database viene chiuso correttamente quando l'opzione AUTO_CLOSE è impostata su ON, il database non verrà riaperto finché un utente non tenta di utilizzarlo dopo il successivo riavvio di Motore di database.

  • OFF
    Il database rimane aperto dopo la disconnessione dell'ultimo utente.

  L'opzione AUTO_CLOSE è utile per i database desktop perché consente di gestire i file di database come normali file. I file possono essere spostati, copiati per creare backup oppure inviati tramite posta elettronica ad altri utenti.

  [!NOTA] Nelle versioni precedenti di SQL Server, AUTO_CLOSE è un processo sincrono e ciò può portare a un peggioramento delle prestazioni se l'accesso al database viene eseguito da un'applicazione che attiva e interrompe ripetutamente connessioni a Motore di database. In SQL Server 2005, il processo AUTO_CLOSE è asincrono. Operazioni ripetute di apertura e chiusura del database non causano più una riduzione delle prestazioni.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_auto_close_on nella vista del catalogo sys.databases oppure la proprietà IsAutoClose della funzione DATABASEPROPERTYEX.

  [!NOTA] Se l'opzione AUTO_CLOSE è impostata su ON, alcune colonne nella vista del catalogo sys.databases e della funzione DATABASEPROPERTYEX restituiranno NULL perché il database non è disponibile per il recupero dei dati. Per risolvere questo problema, eseguire un'istruzione USE per aprire il database.

  [!NOTA] Per il mirroring del database è necessario che AUTO_CLOSE sia OFF.

  Quando il database è impostato su AUTOCLOSE = ON, un'operazione che avvia una chiusura automatica del database comporta la cancellazione della cache dei piani per l'istanza di SQL Server. La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un improvviso temporaneo peggioramento delle prestazioni di esecuzione delle query. In SQL Server 2005 Service Pack 2 il log degli errori di SQL Server contiene il messaggio informativo seguente per ogni archivio cache cancellato nella cache dei piani: "SQL Server ha rilevato %d occorrenza/e di scaricamento dell'archivio cache '%s' (parte della cache dei piani) a causa di operazioni di manutenzione o riconfigurazione del database". Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    Le eventuali statistiche mancanti necessarie per l'ottimizzazione di una query vengono create automaticamente durante la fase di ottimizzazione.

    L'aggiunta di statistiche ottimizza le prestazioni delle query perché Query Optimizer di SQL Server è in grado di determinare con più precisione come valutare una query. Le statistiche non utilizzate vengono eliminate automaticamente da Motore di database. Se è impostata su OFF, le statistiche non vengono create automaticamente, ma è possibile crearle manualmente. Per ulteriori informazioni, vedere Statistiche dell'indice.

  • OFF
    Le statistiche devono essere create manualmente.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_auto_update_stats_on nella vista del catalogo sys.databases oppure la proprietà IsAutoUpdateStatistics della funzione DATABASEPROPERTYEX.

  [!NOTA] Query Optimizer elabora tutte le tabelle di sistema interne come se l'impostazione di AUTO_CREATE_STATISTICS fosse ON, indipendentemente dall'impostazione effettiva. Tali tabelle includono le tabelle di base di sistema, gli indici XML, gli indici full-text, le tabelle di code di Service Broker e le tabelle di notifica delle query.

• AUTO_SHRINK { ON | OFF }

  • ON
    I file di database vengono compattati periodicamente, se necessario.

    È possibile compattare automaticamente sia i file di dati e che i file di log. AUTO_SHRINK consente di ridurre le dimensioni del log delle transazioni solo se per il database viene utilizzato il modello di recupero SIMPLE o se per il log viene eseguito il backup. Se è impostata su OFF, i file di database non vengono compattati automaticamente durante i controlli periodici della presenza di spazio inutilizzato.

    Con l'opzione AUTO_SHRINK i file vengono compattati quando più del 25% dello spazio del file risulta inutilizzato. Il file viene compattato fino a quando la percentuale di spazio inutilizzato nel file è pari al 25% oppure fino a quando il file raggiunge dimensioni pari a quelle di creazione, a seconda di quale tra questi due è il valore maggiore.

    Non è possibile compattare un database di sola lettura.

  • OFF
    I file di database non vengono compattati automaticamente durante i controlli periodici della presenza di spazio inutilizzato.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_auto_shrink_on nella vista del catalogo sys.databases oppure la proprietà IsAutoShrink della funzione DATABASEPROPERTYEX. .

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Le eventuali statistiche non aggiornate necessarie per l'ottimizzazione di una query vengono aggiornate automaticamente durante la fase di ottimizzazione.
  • OFF
    Le statistiche devono essere aggiornate manualmente.

  [!NOTA] L'istruzione UPDATE STATISTICS riattiva l'aggiornamento automatico delle statistiche nella tabella o vista di destinazione, a meno che non si specifichi la clausola NORECOMPUTE.

  [!NOTA] Query Optimizer elabora tutte le tabelle di sistema interne come se l'impostazione di AUTO_UPDATE_STATISTICS fosse ON, indipendentemente dall'impostazione effettiva. Tali tabelle includono le tabelle di base di sistema, gli indici XML, gli indici full-text, le tabelle di code di Service Broker e le tabelle di notifica delle query.

  Per ulteriori informazioni, vedere Statistiche dell'indice.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Le query che avviano un aggiornamento automatico delle statistiche non aggiornate non attenderanno il completamento dell'aggiornamento delle statistiche prima della compilazione. Le query successive utilizzeranno le statistiche aggiornate, non appena disponibili.
  • OFF
    Le query che avviano un aggiornamento automatico delle statistiche non aggiornate attenderanno che le statistiche aggiornate diventino disponibili per l'utilizzo nel piano di ottimizzazione.

  L'impostazione di questa opzione su ON non produce alcun effetto a meno che AUTO_UPDATE_STATISTICS non sia impostata su ON.

  Per ulteriori informazioni, vedere Statistiche dell'indice.

<sql_option>::=

Controlla le opzioni di conformità ANSI a livello del database.

• ANSI_NULL_DEFAULT { ON | OFF }
  Determina il valore predefinito, NULL o NOT NULL, per una colonna, un tipo di dati alias o un tipo CLR definito dall'utente per cui il supporto di valori Null non è definito in modo esplicito nelle istruzioni CREATE TABLE o ALTER TABLE. Le colonne definite con vincoli seguono le regole dei vincoli indipendentemente da questa impostazione.

  • ON
    Il valore predefinito è NULL.
  • OFF
    Il valore predefinito è NOT NULL.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita a livello di database per ANSI_NULL_DEFAULT. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta ANSI_NULL_DEFAULT su ON per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET ANSI_NULL_DFLT_ON (Transact-SQL).

  Per motivi di compatibilità con ANSI, l'impostazione dell'opzione di database ANSI_NULL_DEFAULT su ON modifica l'impostazione predefinita del database su NULL.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_ansi_null_default_on nella vista del catalogo sys.databases oppure la proprietà IsAnsiNullDefault della funzione DATABASEPROPERTYEX.

• ANSI_NULLS { ON | OFF }

  • ON
    Tutti i confronti con un valore Null restituiscono UNKNOWN.
  • OFF
    I confronti di valori non UNICODE con un valore Null restituiscono TRUE se entrambi i valori sono NULL.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita a livello di database per ANSI_NULLS. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta ANSI_NULLS su ON per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET ANSI_NULLS (Transact-SQL).

  È inoltre necessario che l'opzione SET ANSI_NULLS sia impostata su ON durante la creazione o la modifica di indici in colonne calcolate o viste indicizzate.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_ansi_nulls_on nella vista del catalogo sys.databases oppure la proprietà IsAnsiNullsEnabled della funzione DATABASEPROPERTYEX.

• ANSI_PADDING { ON | OFF }

  • ON
    Le stringhe vengono riempite fino a ottenere stringhe di lunghezza uguale, prima della conversione o dell'inserimento in un tipo di dati varchar o nvarchar.

    Gli spazi vuoti finali in valori di tipo carattere inseriti in colonne varchar o nvarchar e gli zeri finali in valori binari inseriti in colonne varbinary non vengono eliminati. I valori non vengono riempiti fino all'intera lunghezza della colonna.

  • OFF
    Vengono eliminati gli spazi vuoti finali per i dati di tipo varchar o nvarchar e gli zeri per i dati di tipo varbinary.

  Se si specifica OFF, questa impostazione ha effetto solo sulla definizione di nuove colonne.

  char(n Le colonne di tipo ) e binary(n) che supportano valori Null vengono riempite fino alla lunghezza della colonna se l'opzione ANSI_PADDING è impostata su ON, ma gli spazi vuoti finali e gli zeri vengono eliminati se ANSI_PADDING è OFF. Le colonne di tipo char(n) e binary(n) che non consentono valori Null vengono sempre riempite fino alla lunghezza della colonna.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita a livello di database per ANSI_PADDING. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta ANSI_PADDING su ON per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET ANSI_PADDING (Transact-SQL).

  Importante:
  È consigliabile impostare l'opzione ANSI_PADDING sempre su ON. È necessario che l'opzione ANSI_PADDING sia impostata su ON durante la creazione o la modifica di indici su colonne calcolate o viste indicizzate.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_ansi_padding_on nella vista del catalogo sys.databases oppure la proprietà IsAnsiPaddingEnabled della funzione DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    Vengono generati errori o avvisi se si verificano condizioni quali la divisione per zero oppure in presenza di valori Null nelle funzioni di aggregazione.
  • OFF
    Non vengono generati avvisi e vengono restituiti valori Null quando si verificano condizioni come la divisione per zero.

  È necessario che l'opzione ANSI_WARNINGS sia impostata su ON durante la creazione o la modifica di indici su colonne calcolate o viste indicizzate.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita a livello di database per ANSI_WARNINGS. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta ANSI_WARNINGS su ON per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET ANSI_WARNINGS (Transact-SQL).

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_ansi_warnings_on nella vista del catalogo sys.databases oppure la proprietà IsAnsiWarningsEnabled della funzione DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Interrompe una query quando si verifica un errore di divisione per zero o di overflow durante l'esecuzione della query stessa.
  • OFF
    Viene visualizzato un messaggio di avviso quando si verifica uno di questi errori, ma l'elaborazione della query, del batch o della transazione prosegue come se non si fosse verificato alcun errore.

  È necessario che l'opzione ARITHABORT sia impostata su ON durante la creazione o la modifica di indici su colonne calcolate o viste indicizzate.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_arithabort_on nella vista del catalogo sys.databases oppure la proprietà IsArithmeticAbortEnabled della funzione DATABASEPROPERTYEX.

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    Il risultato di un'operazione di concatenamento è NULL quando uno degli operandi è NULL. Ad esempio, il concatenamento della stringa di caratteri "Questo è" con NULL restituisce il valore NULL, anziché il valore "Questo è".
  • OFF
    Il valore Null viene gestito come una stringa di caratteri vuota.

  È necessario che l'opzione CONCAT_NULL_YIELDS_NULL sia impostata su ON durante la creazione o la modifica di indici su colonne calcolate o viste indicizzate.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione predefinita a livello di database per CONCAT_NULL_YIELDS_NULL. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta CONCAT_NULL_YIELDS_NULL su ON per la sessione quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_concat_null_yields_null_on nella vista del catalogo sys.databases oppure la proprietà IsNullConcat della funzione DATABASEPROPERTYEX.

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    È possibile racchiudere gli identificatori delimitati tra virgolette doppie.

    Tutte le stringhe racchiuse tra virgolette doppie vengono interpretate come identificatori di oggetto. Gli identificatori tra virgolette non devono necessariamente essere conformi alle regole di Transact-SQL per gli identificatori. Possono essere parole chiave e includere caratteri normalmente non consentiti negli identificatori Transact-SQL. Se una virgoletta singola (') fa parte della stringa letterale, può essere rappresentata tramite virgolette doppie (").

  • OFF
    Gli identificatori non possono essere racchiusi tra virgolette e devono essere conformi a tutte le regole di Transact-SQL per gli identificatori. È possibile delimitare i valori letterali con virgolette singole o doppie.

  SQL Server consente inoltre di racchiudere gli identificatori tra parentesi quadre ([ ]). Gli identificatori tra parentesi quadre possono essere sempre utilizzati, indipendentemente dall'impostazione di QUOTED_IDENTIFIER. Per ulteriori informazioni, vedere Identificatori delimitati (Motore di database).

  Durante la creazione di una tabella, l'opzione QUOTED IDENTIFIER viene sempre archiviata con l'impostazione ON nei metadati della tabella, anche se l'opzione viene impostata su OFF quando si crea la tabella.

  Le impostazioni a livello di connessione (impostate utilizzando l'istruzione SET) sono prioritarie rispetto all'impostazione di database predefinita per QUOTED_IDENTIFIER. Per impostazione predefinita, i client ODBC e OLE DB eseguono un'istruzione SET a livello di connessione che imposta QUOTED_IDENTIFIER su ON quando viene attivata una connessione a un'istanza di SQL Server. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

  Per determinare lo stato di queste opzioni, è possibile esaminare la colonna is_quoted_identifier_on nella vista del catalogo sys.databases oppure la proprietà IsQuotedIdentifiersEnabled della funzione DATABASEPROPERTYEX.

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    Viene generato un errore quando si verifica una perdita di precisione in un'espressione.
  • OFF
    In seguito alla perdita di precisione non viene visualizzato alcun messaggio di errore e il risultato viene arrotondato alla precisione della colonna o della variabile in cui viene archiviato.

  È necessario che l'opzione NUMERIC_ROUNDABORT sia impostata su OFF quando vengono creati o modificati indici su colonne calcolate o viste indicizzate.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_numeric_roundabort_on nella vista del catalogo sys.databases oppure la proprietà IsNumericRoundAbortEnabled della funzione DATABASEPROPERTYEX.

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    È consentita l'attivazione ricorsiva di trigger AFTER.
  • OFF
    Solo l'attivazione ricorsiva diretta di trigger AFTER non è consentita. Per disattivare anche la ricorsione indiretta dei trigger AFTER , impostare l'opzione del server nested triggers su 0 tramite sp_configure.

  [!NOTA] Se RECURSIVE_TRIGGERS è impostata su OFF, viene impedita esclusivamente la ricorsione diretta. Per disattivare la ricorsione indiretta, è inoltre necessario impostare l'opzione del server nested triggers su 0.

  Per determinare lo stato di questa opzione, è possibile esaminare la colonna is_recursive_triggers_on nella vista del catalogo sys.databases oppure la proprietà IsRecursiveTriggersEnabled della funzione DATABASEPROPERTYEX.

<recovery_option> ::=

Controlla le opzioni per il recupero del database e il controllo degli errori di I/O su disco.

• FULL
  Consente il recupero completo in caso di errori dei supporti tramite i backup del log delle transazioni. Se un file di dati risulta danneggiato, il recupero dei supporti consente di ripristinare tutte le transazioni di cui è stato eseguito il commit. Per ulteriori informazioni, vedere Backup con il modello di recupero con registrazione completa.

• BULK_LOGGED
  Consente il recupero in caso di errori dei supporti, cercando di ottenere i migliori risultati a livello di prestazioni e di utilizzo della quantità minima di spazio del log per determinate operazioni su larga scala o di massa. Per informazioni sulle operazioni incluse nella registrazione minima delle transazioni di massa, vedere Operazioni con registrazione minima. Con il modello di recupero BULK_LOGGED vengono registrate informazioni minime per queste operazioni. Per ulteriori informazioni, vedere Backup in base al modello di recupero con registrazione minima delle transazioni di massa.

• SIMPLE
  Viene implementata una strategia di backup semplice che utilizza una quantità minima dello spazio del log. Lo spazio del log può essere riutilizzato automaticamente quando non è più necessario per il recupero di errori del server. Per ulteriori informazioni, vedere Backup in base al modello di recupero con registrazione minima.

  Importante:
  La gestione del modello di recupero con registrazione minima risulta più semplice rispetto agli altri due modelli, ma comporta rischi maggiori di perdita dei dati in caso di danni a un file di dati. In questi casi, infatti, tutte le modifiche apportate dopo l'ultimo backup completo o differenziale del database vanno perdute ed è necessario immetterle nuovamente in modo manuale.

Il modello di recupero predefinito dipende dal modello di recupero impostato per il database model. Per ulteriori informazioni sulla scelta del modello di recupero più appropriato, vedere Scelta del modello di recupero per un database.

Per determinare lo stato di questa opzione, è possibile esaminare le colonne recovery_model e recovery_model_desc nella vista del catalogo sys.databases oppure la proprietà Recovery della funzione DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Le pagine incomplete possono essere rilevate da Motore di database.
  • OFF
    Le pagine incomplete non possono essere rilevate da Motore di database.

  Importante:
  La struttura di sintassi TORN_PAGE_DETECTION ON | OFF verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare pertanto di utilizzarla in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente utilizzano questa struttura. Utilizzare l'opzione PAGE_VERIFY in alternativa.

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  Individua le pagine del database danneggiate in seguito a errori di percorso di I/O su disco. Gli errori di percorso di I/O su disco possono essere la causa di danneggiamenti del database e sono in genere la conseguenza di interruzioni dell'alimentazione o di errori hardware a livello del disco, che si verificano durante la scrittura della pagina su disco.

  • CHECKSUM
    Calcola un checksum sul contenuto dell'intera pagina e archivia il valore nell'intestazione della pagina quando questa viene scritta su disco. In fase di lettura della pagina dal disco, il checksum viene ricalcolato e confrontato con il valore di checksum archiviato nell'intestazione della pagina. Se i valori non corrispondono, viene segnalato il messaggio di errore 824 (che indica un errore di checksum) sia nel log degli errori di SQL Server che nel registro eventi di Windows. Un errore di checksum indica un problema di percorso di I/O. Per determinare la causa principale del problema, è necessaria un'analisi accurata di hardware, driver firmware, BIOS, driver di filtro (ad esempio software antivirus) e degli altri componenti del percorso di I/O.
  • TORN_PAGE_DETECTION
    Salva un bit specifico per ogni settore da 512 byte della pagina di database da 8 kilobyte (KB) e archivia tali bit nell'intestazione della pagina quando questa viene scritta su disco. In fase di lettura della pagina dal disco, i bit per il rilevamento di pagine incomplete archiviati nell'intestazione della pagina vengono confrontati con le informazioni effettive sui settori della pagina. La presenza di valori non corrispondenti indica che la pagina è stata scritta su disco solo in parte. In questa situazione viene segnalato il messaggio di errore 824 (che indica un errore di pagina incompleta) sia nel log degli errori di SQL Server che nel registro eventi di Windows. Le pagine incomplete vengono generalmente rilevate durante il recupero del database, se si tratta effettivamente di un problema di scrittura incompleta di una pagina. Tuttavia, altri errori di percorso di I/O possono causare in qualsiasi momento pagine incomplete.
  • NONE
    Per le scritture di pagine del database non verrà generato un valore CHECKSUM o TORN_PAGE_DETECTION. SQL Server non esegue un controllo del checksum o della presenza di pagine incomplete durante una lettura, anche se nell'intestazione della pagina è presente un valore CHECKSUM o TORN_PAGE_DETECTION.

  Per l'utilizzo dell'opzione PAGE_VERIFY è importante tenere presente quanto segue:

  • In SQL Server 2005, l'impostazione predefinita è CHECKSUM. L'impostazione predefinita di SQL Server 2000 è TORN_PAGE_DETECTION.
  • Quando si aggiorna un database utente o di sistema a SQL Server 2005, il valore di PAGE_VERIFY, ovvero NONE o TORN_PAGE_DETECTION, rimane invariato. È consigliabile utilizzare CHECKSUM.
  • TORN_PAGE_DETECTION può consentire l'utilizzo di un numero più limitato di risorse, ma offre una protezione minima rispetto all'opzione CHECKSUM.
  • È possibile impostare PAGE_VERIFY senza attivare la modalità non in linea per il database, senza bloccarlo o senza impedire in altro modo attività simultanee.
  • Le opzioni CHECKSUM e TORN_PAGE_DETECTION si escludono a vicenda. Non è possibile attivare contemporaneamente entrambe le opzioni.

  Se viene rilevato un errore di pagina incompleta o di checksum, è possibile eseguire il recupero tramite il ripristino dei dati o potenzialmente tramite la ricostruzione dell'indice se l'errore è limitato alle pagine dell'indice. Se si verifica un errore di checksum, eseguire DBCC CHECKDB per determinare la pagina o le pagine del database interessate dal problema. Per ulteriori informazioni sulle opzioni di ripristino, vedere Argomenti dell'istruzione RESTORE (Transact-SQL). Sebbene il ripristino dei dati consenta di risolvere il problema di danneggiamento dei dati, è necessario individuare il prima possibile la causa principale, ad esempio un errore hardware del disco, per eseguire i necessari interventi di correzione ed evitare che gli errori si ripresentino.

  SQL Server eseguirà quattro tentativi per qualsiasi operazione di lettura non riuscita a causa di un errore di checksum, pagina incompleta o di I/O. Se la lettura viene completata correttamente durante uno di questi tentativi, verrà scritto un messaggio nel log degli errori e verrà continuata l'esecuzione del comando che ha attivato la lettura. Se tutti i tentativi hanno esito negativo, il comando verrà interrotto con il messaggio di errore 824.

  Per ulteriori informazioni sul checksum, le pagine incomplete, i tentativi di lettura, i messaggi di errore 823 e 824, nonché su altre funzionalità di controllo dell'I/O di SQL Server, vedere il sito Web Microsoft.

  Per determinare l'impostazione corrente di questa opzione, è possibile esaminare la colonna page_verify_option nella vista del catalogo sys.databases oppure la proprietà IsTornPageDetectionEnabled della funzione DATABASEPROPERTYEX.

<database_mirroring_option>::=

Controlla il mirroring del database per un database. I valori specificati per le opzioni di mirroring del database vengono applicati a entrambe le copie del database e alla sessione di mirroring del database nella sua globalità. In ogni istruzione ALTER DATABASE è consentita l'impostazione di una sola opzione <database_mirroring_option>, ovvero { SET PARTNER <partner_option> | SET WITNESS <witness_option>}.

Importante:
È possibile che un comando SET PARTNER o SET WITNESS venga completato correttamente al momento dell'immissione, ma non riesca in un secondo momento.

[!NOTA] È consigliabile configurare il mirroring del database durante le fasce orarie di minore attività, dato che la configurazione può influire sulle prestazioni.

Per ulteriori informazioni sul mirroring del database, vedere Mirroring del database.

• PARTNER <partner_option>
  Controlla le proprietà del database che definiscono i partner di failover di una sessione di mirroring del database e il relativo funzionamento. Alcune opzioni SET PARTNER possono essere impostate indifferentemente in uno dei partner. Altre sono supportate solo nel server principale o nel server mirror. Per ulteriori informazioni, vedere le descrizioni seguenti delle singole opzioni PARTNER. La clausola SET PARTNER influisce su entrambe le copie del database, indipendentemente dal partner in cui viene specificata.

  Per eseguire un'istruzione SET PARTNER, l'opzione STATE degli endpoint di entrambi i partner deve essere impostata su STARTED. Si noti, inoltre, che l'opzione ROLE dell'endpoint di mirroring del database di ogni istanza del server partner deve essere impostata su PARTNER o su ALL. Per informazioni sull'impostazione di un endpoint, vedere Procedura: Creazione di un endpoint del mirroring per l'autenticazione Windows (Transact-SQL). Per recuperare informazioni sul ruolo e lo stato dell'endpoint di mirroring del database per un'istanza del server, utilizzare l'istruzione Transact-SQL seguente in tale istanza:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  <partner_option> ::=

  [!NOTA] È consentita una sola occorrenza di <partner_option> per ogni clausola SET PARTNER.

  • 'partner_server'
    Specifica l'indirizzo di rete del server di un'istanza di SQL Server che fungerà da partner di failover in una nuova sessione di mirroring del database. Ogni sessione deve includere due partner, uno avviato come server principale e l'altro come server mirror. È consigliabile che tali partner risiedano in computer diversi.

    Questa opzione viene specificata una sola volta per sessione in ogni partner. Per iniziare una sessione di mirroring del database sono necessarie due istruzioni ALTER DATABASE database SET PARTNER ='partner_server'. L'ordine con cui vengono specificate è significativo. Connettersi innanzitutto al server mirror e specificare l'istanza del server principale come partner_server (SET PARTNER ='principal_server'). Connettersi poi al server principale e specificare l'istanza del server mirror come partner_server (SET PARTNER ='mirror_server'). In questo modo viene avviata una sessione di mirroring del database tra i due partner. Per ulteriori informazioni, vedere Impostazione del mirroring del database.

    Il valore di partner_server è un indirizzo di rete del server. La sintassi è la seguente:

    TCP**://<system-address>:**<port>

    dove

    • <system-address> è una stringa, ad esempio un nome di sistema, un nome di dominio completo o un indirizzo IP, che identifica in modo univoco il computer di destinazione.
    • <port> è il numero di porta associato all'endpoint del mirroring dell'istanza del server partner.

    Per ulteriori informazioni, vedere Impostazione di un indirizzo di rete del server (mirroring del database).

    Nell'esempio seguente viene illustrata la clausola SET PARTNER ='partner_server':

    SET PARTNER = 'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'
    

    Importante:

    Se una sessione viene configurata tramite l'istruzione ALTER DATABASE anziché con SQL Server Management Studio, per la sessione verrà specificato il livello di protezione completo delle transazioni per impostazione predefinita (opzione SAFETY impostata su FULL) e tale sessione sarà eseguita in modalità a protezione elevata senza failover automatico. Per consentire il failover automatico, configurare un server di controllo del mirroring. Per l'esecuzione in modalità a prestazioni elevate, disattivare la protezione delle transazioni (opzione SAFETY impostata su OFF).

  • FAILOVER
    Esegue manualmente il failover del server principale sul server mirror. È possibile specificare l'opzione FAILOVER solo nel server principale. Questa opzione è valida solo con l'impostazione FULL per SAFETY (impostazione predefinita).

    Per l'opzione FAILOVER è necessario utilizzare il database master come contesto di database.

    Per ulteriori informazioni, vedere Failover manuale.

  • FORCE_SERVICE_ALLOW_DATA_LOSS
    Forza il servizio di database nel database mirror in seguito a errori del server principale con il database in stato non sincronizzato o in stato sincronizzato, quando il failover automatico non si verifica.

    È consigliabile forzare il servizio solo se il server principale non è più in esecuzione. In caso contrario, alcuni client potrebbero continuare ad accedere al database principale originale anziché al nuovo database principale.

    FORCE_SERVICE_ALLOW_DATA_LOSS è disponibile solo nel server mirror ed esclusivamente quando sono valide tutte le condizioni seguenti:

    • Il server principale non è disponibile.
    • WITNESS è impostato su OFF o il server di controllo del mirroring è connesso al server mirror.

    Forzare il servizio solo se il rischio di perdita parziale dei dati è accettabile al fine di ripristinare immediatamente il database. Per informazioni sulle alternative disponibili per evitare di forzare il servizio, vedere Mirroring asincrono del database (modalità a prestazioni elevate).

    Quando si forza il servizio, la sessione viene sospesa mantenendo temporaneamente tutti i dati nel database principale originale. Dopo che il server principale originale viene attivato ed è in grado di comunicare con il nuovo server principale, l'amministratore del database può ripristinare il servizio. Alla ripresa della sessione, tutti i record di log non inviati e gli aggiornamenti corrispondenti vengono persi.

    Per ulteriori informazioni sui rischi derivanti dalla forzatura del servizio, vedere Servizio forzato (con possibile perdita di dati).

  • OFF
    Rimuove una sessione di mirroring del database e rimuove il mirroring dal database. È possibile specificare l'opzione OFF in qualsiasi partner. Per informazioni sull'impatto della rimozione del mirroring, vedere Rimozione del mirroring del database.
  • RESUME
    Riprende una sessione di mirroring del database sospesa. È possibile specificare l'opzione RESUME solo nel server principale.

  • SAFETY { FULL | OFF }
    Imposta il livello di protezione delle transazioni. È possibile specificare l'opzione SAFETY solo nel server principale.

    Il valore predefinito è FULL. Con la protezione completa, la sessione di mirroring del database viene eseguita in modo sincrono, ovvero in modalità a protezione elevata. Se l'opzione SAFETY è impostata su OFF, la sessione di mirroring del database viene eseguita in modo asincrono, ovvero in modalità a prestazioni elevate.

    Il comportamento della modalità a protezione elevata dipende in parte dal server di controllo del mirroring, come indicato di seguito:

    • Se la protezione è impostata su FULL e per la sessione è impostato un server di controllo del mirroring, la sessione viene eseguita in modalità a protezione elevata con failover automatico. Se il server principale viene perso, viene eseguito il failover automatico della sessione se il database è sincronizzato e se l'istanza del server mirror e il server di controllo del mirroring sono ancora connessi tra loro, ovvero se hanno una relazione di quorum. Per ulteriori informazioni, vedere Quorum: Impatto di un server di controllo del mirroring sulla disponibilità del database.
      Se per la sessione è impostato un server di controllo del mirroring ma quest'ultimo è disconnesso, il server principale si arresta a causa dell'indisponibilità del server mirror.
    • Se la protezione è impostata su FULL e il server di controllo del mirroring è impostato su OFF, la sessione viene eseguita in modalità a protezione elevata senza failover automatico. Un eventuale arresto dell'istanza del server mirror non influisce sul server principale. Se l'istanza del server principale si arresta, è possibile forzare il servizio verso l'istanza del server mirror, con una possibile perdita di dati.

    Se l'opzione SAFETY è impostata su OFF, la sessione viene eseguita in modalità a prestazioni elevate, in cui non sono supportati né il failover automatico né quello manuale . I problemi del server mirror, tuttavia, non influiscono sul server principale. Se l'istanza del server principale si arresta e l'opzione WITNESS è impostata su OFF o il server di controllo del mirroring è connesso al server mirror, se necessario è possibile forzare il servizio sull'istanza del server mirror, con una possibile perdita di dati. Per ulteriori informazioni sulla forzatura del servizio, vedere "FORCE_SERVICE_ALLOW_DATA_LOSS" più indietro in questa sezione.

    Importante:
    La modalità a prestazioni elevate non è destinata all'utilizzo con un server di controllo del mirroring. Tuttavia, ogni volta che l'opzione SAFETY viene impostata su OFF, è consigliabile verificare che WITNESS sia impostata su OFF.

    Per ulteriori informazioni, vedere Impostazioni di Transact-SQL e modalità operative del mirroring del database.

  • SUSPEND
    Sospende una sessione di mirroring del database.

    È possibile specificare l'opzione SUSPEND in qualsiasi partner.

  • TIMEOUT integer
    Specifica il periodo di timeout in secondi. Il periodo di timeout indica l'intervallo di attesa massimo rispettato dall'istanza del server per la ricezione di un messaggio PING da un'altra istanza nella sessione di mirroring, prima che l'altra istanza venga considerata disconnessa.

    È possibile specificare l'opzione TIMEOUT solo nel server principale. Se non si specifica questa opzione, il periodo di timeout predefinito è di 10 secondi. Se si specifica un valore maggiore o uguale a 5, il periodo di timeout viene impostato sul numero di secondi specificato. Se si specifica un valore di timeout compreso tra 0 e 4 secondi, l'intervallo viene impostato automaticamente su 5 secondi.

    Importante:
    È consigliabile utilizzare un periodo di timeout di almeno 10 secondi. In caso contrario può verificarsi un sovraccarico del sistema, con perdita di PING e generazione di falsi errori.

    Per ulteriori informazioni, vedere Possibili errori durante il mirroring del database.

• WITNESS <witness_option>
  Controlla le proprietà del database che definiscono un server di controllo del mirroring del database. La clausola SET WITNESS influisce su entrambe le copie del database, ma è possibile specificare SET WITNESS solo nel server principale. Se per una sessione è impostato un server di controllo del mirroring, per l'utilizzo del database è necessaria una relazione di quorum, indipendentemente dall'impostazione di SAFETY. Per ulteriori informazioni, vedere Quorum: Impatto di un server di controllo del mirroring sulla disponibilità del database.

  È consigliabile che il server di controllo del mirroring e i partner di failover risiedano in computer diversi. Per informazioni sul server di controllo del mirroring, vedere Server di controllo del mirroring del database. Per informazioni sul failover automatico, vedere Failover automatico.

  Per eseguire un'istruzione SET WITNESS, l'opzione STATE degli endpoint deve essere impostata su STARTED sia nell'istanza del server principale che nell'istanza del server di controllo del mirroring. Si noti, inoltre, che l'opzione ROLE dell'endpoint di mirroring del database di un'istanza del server di controllo del mirroring deve essere impostata su WITNESS o su ALL. Per informazioni sull'impostazione di un endpoint, vedere Endpoint del mirroring del database.

  Per recuperare informazioni sul ruolo e lo stato dell'endpoint di mirroring del database per un'istanza del server, utilizzare l'istruzione Transact-SQL seguente in tale istanza:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  [!NOTA] Non è possibile impostare proprietà del database nel server di controllo del mirroring.

  <witness_option> ::=

  [!NOTA] È consentita una sola occorrenza di <witness_option> per ogni clausola SET WITNESS.

  • 'witness_server'
    Specifica un'istanza di Motore di database da utilizzare come server di controllo del mirroring per una sessione di mirroring del database. È possibile specificare istruzioni SET WITNESS solo nel server principale.

    In un'istruzione SET WITNESS ='witness_server', la sintassi di witness_server è uguale alla sintassi di partner_server.

  • OFF
    Rimuove il server di controllo del mirroring da una sessione di mirroring del database. L'impostazione del server di controllo del mirroring su OFF disattiva il failover automatico. Se per il database l'opzione SAFETY è impostata su FULL e il server di controllo del mirroring è impostato su OFF, in caso di errore nel server mirror, il server principale rende il database non disponibile.

<service_broker_option>::=

Controlla le opzioni relative a Service Broker.

• ENABLE_BROKER
  Indica che Service Broker è attivato per il database specificato. Il flag is_broker_enabled viene impostato su true nella vista del catalogo sys.databases e viene avviato il recapito dei messaggi.

  [!NOTA] Per attivare SQL Server Service Broker in un database, è necessario un blocco a livello di database. Per attivare Service Broker nel database msdb, arrestare SQL Server Agent per consentire a Service Broker di ottenere il blocco necessario.

• DISABLE_BROKER
  Indica che Service Broker è disattivato per il database specificato. Il flag is_broker_enabled viene impostato su false nella vista del catalogo sys.databases e viene interrotto il recapito dei messaggi.

• NEW_BROKER
  Specifica che al database deve essere assegnato un nuovo identificatore di Service Broker. Poiché il database viene considerato una nuova istanza di Service Broker, tutte le conversioni esistenti nel database vengono rimosse immediatamente senza generare messaggi di fine dialogo.

• ERROR_BROKER_CONVERSATIONS
  Specifica che le conversioni nel database devono ricevere un messaggio di errore quando il database viene collegato. Ciò consente alle applicazioni di eseguire operazioni regolari di pulitura per le conversazioni esistenti.

<date_correlation_optimization_option> ::=

Controlla l'opzione date_correlation_optimization.

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    SQL Server mantiene statistiche di correlazione per qualsiasi coppia di tabelle nel database collegata tramite un vincolo FOREIGN KEY e con colonne di tipo datetime. Per ulteriori informazioni, vedere Ottimizzazione di query che accedono a colonne datetime correlate.
  • OFF
    Non vengono mantenute statistiche di correlazione.

  Per impostare DATE_CORRELATION_OPTIMIZATION su ON, è necessario che non siano presenti connessioni attive al database, ad eccezione della connessione che esegue l'istruzione ALTER DATABASE. In seguito, sono supportate più connessioni.

  Per determinare l'impostazione corrente dell'opzione, è possibile esaminare la colonna is_date_correlation_on nella vista del catalogo sys.databases.

<parameterization_option> ::=

Controlla l'opzione di parametrizzazione.

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    Le query vengono parametrizzate in base al funzionamento predefinito del database. Per ulteriori informazioni, vedere Parametrizzazione semplice.
  • FORCED
    SQL Server esegue la parametrizzazione di tutte le query nel database. Per ulteriori informazioni, vedere Parametrizzazione forzata.

  Per determinare l'impostazione corrente dell'opzione, è possibile esaminare la colonna is_parameterization_forced nella vista del catalogo sys.databases.

<snapshot_option>::=

Determina il livello di isolamento delle transazioni.

• ALLOW_SNAPSHOT_ISOLATION { ON| OFF }

  • ON
    Le transazioni possono specificare il livello di isolamento SNAPSHOT. Nelle transazioni eseguite con il livello di isolamento SNAPSHOT, tutte le istruzioni possono accedere a uno snapshot dei dati corrispondente allo stato dei dati al momento dell'avvio della transazione. Per una transazione eseguita con il livello di isolamento SNAPSHOT che deve accedere ai dati in più database, è necessario impostare ALLOW_SNAPSHOT_ISOLATION su ON in tutti i database oppure ogni istruzione della transazione deve utilizzare hint di blocco per qualsiasi riferimento in una clausola FROM a una tabella di un database per cui ALLOW_SNAPSHOT_ISOLATION è OFF.
  • OFF
    Le transazioni non possono specificare il livello di isolamento SNAPSHOT.

  Quando si modifica l'impostazione dell'opzione ALLOW_SNAPSHOT_ISOLATION (da ON a OFF oppure da OFF a ON), ALTER DATABASE restituisce il controllo al chiamante solo dopo il completamento del commit di tutte le transazioni esistenti nel database. Se per il database è già attivo lo stato specificato nell'istruzione ALTER DATABASE, il controllo viene restituito immediatamente al chiamante. Se l'istruzione ALTER DATABASE non restituisce il controllo rapidamente, utilizzare sys.dm_tran_active_snapshot_database_transactions per verificare se sono presenti transazioni con esecuzione prolungata. Se l'istruzione ALTER DATABASE viene annullata, il database rimane nello stato attivo al momento dell'avvio dell'istruzione ALTER DATABASE. La vista del catalogo sys.databases indica lo stato delle transazioni di isolamento dello snapshot nel database. Se snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF attenderà sei secondi prima di ritentare l'operazione.

  Non è possibile modificare lo stato di ALLOW_SNAPSHOT_ISOLATION se il database è OFFLINE.

  Se si imposta ALLOW_SNAPSHOT_ISOLATION in un database READ_ONLY, tale impostazione viene mantenuta anche se il database viene in seguito impostato su READ_WRITE.

  È possibile modificare le impostazioni ALLOW_SNAPSHOT_ISOLATION per i database master, model, msdb e tempdb. Se si modifica l'impostazione per il database tempdb, questa viene mantenuta per ogni interruzione e riavvio dell'istanza di Motore di database. Se si modifica l'impostazione per il database model, questa diventerà l'impostazione predefinita per i nuovi database creati, con l'eccezione di tempdb.

  L'impostazione predefinita dell'opzione è ON per i database master e msdb.

  Per determinare l'impostazione corrente dell'opzione, è possibile esaminare la colonna snapshot_isolation_state nella vista del catalogo sys.databases.

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    Le transazioni per cui è impostato il livello di isolamento Read committed utilizzano il controllo delle versioni delle righe anziché il blocco. Quando una transazione viene eseguita con il livello di isolamento Read committed, tutte le istruzioni possono accedere a uno snapshot dei data corrispondente allo stato dei dati all'avvio dell'istruzione.
  • OFF
    Le transazioni con il livello di isolamento READ_COMMITTED utilizzano il blocco.

  Per poter impostare READ_COMMITTED_SNAPSHOT su ON o OFF, è necessario che non siano presenti connessioni attive al database con l'eccezione della connessione utilizzata per eseguire il comando ALTER DATABASE. Non è tuttavia necessario che il database sia in modalità utente singolo. Non è possibile modificare lo stato di questa opzione quando il database è OFFLINE.

  Se si imposta READ_COMMITTED_SNAPSHOT in un database READ_ONLY, tale impostazione viene mantenuta anche se il database viene in seguito impostato su READ_WRITE.

  Non è possibile impostare READ_COMMITTED_SNAPSHOT su ON per i database di sistema master, tempdb o msdb. Se si modifica l'impostazione per il database model, questa diventerà l'impostazione predefinita per i nuovi database creati, con l'eccezione di tempdb.

  Per determinare l'impostazione corrente dell'opzione, è possibile esaminare la colonna is_read_committed_snapshot_on nella vista del catalogo sys.databases.

WITH <termination>::=

Specifica quando eseguire il rollback di transazioni incomplete quando il database cambia stato. Se questa clausola viene omessa, l'attesa da parte dell'istruzione ALTER DATABASE è illimitata in presenza di qualsiasi blocco attivo sul database. È possibile specificare una sola clausola di terminazione, che deve seguire le clausole SET.

[!NOTA] Non tutte le opzioni di database utilizzano la clausola WITH <termination>. Per ulteriori informazioni, vedere la tabella in "Impostazione delle opzioni" nella sezione Osservazioni.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  Specifica se eseguire il rollback dopo il numero di secondi specificato o immediatamente.

• NO_WAIT
  Specifica che la richiesta avrà esito negativo se non è possibile completare immediatamente la modifica di opzione o di stato del database richiesta senza attendere il regolare commit o rollback delle transazioni.

Osservazioni

Per rimuovere un database, utilizzare DROP DATABASE.

Per rinominare un database, utilizzare l'opzione MODIFY NAME = new_database_name con ALTER DATABASE.

Per ridurre le dimensioni di un database, utilizzare DBCC SHRINKDATABASE.

Non è possibile aggiungere o rimuovere file durante l'esecuzione di un'istruzione BACKUP.

Per ogni database è possibile creare al massimo 32.767 file e 32.767 filegroup.

L'istruzione ALTER DATABASE deve essere eseguita in modalità autocommit (modalità predefinita di gestione delle transazioni) e non è consentita in una transazione esplicita o implicita. Per ulteriori informazioni, vedere Transazioni con autocommit.

In SQL Server 2005, lo stato di un file di database, ad esempio in linea o non in linea, viene mantenuto indipendentemente dallo stato del database. Per ulteriori informazioni, vedere Stati dei file. Lo stato dei file all'interno di un filegroup determina la disponibilità dell'intero filegroup. Perché un filegroup sia disponibile, è necessario che tutti i file in esso inclusi siano in linea. Se un filegroup non è in linea, qualsiasi tentativo di accesso al filegroup tramite un'istruzione SQL avrà esito negativo e verrà generato un errore. Per la creazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup non in linea. Ciò consente la corretta esecuzione di tali istruzioni. Tuttavia, se il filegroup non in linea contiene l'heap o l'indice cluster della tabella di destinazione, l'istruzione SELECT avrà esito negativo. Avranno inoltre esito negativo anche tutte le eventuali istruzioni INSERT, UPDATE o DELETE che modificano una tabella con qualsiasi indice in un filegroup non in linea.

Quando un database è nello stato RESTORING, la maggior parte delle istruzioni ALTER DATABASE avrà esito negativo. Un'alternativa consiste nell'impostare le opzioni di mirroring del database. Lo stato RESTORING può essere impostato durante un'operazione di ripristino attiva o quando l'operazione di ripristino di un database o di un file di log ha esito negativo a causa di un file di backup danneggiato. Per ulteriori informazioni, vedere Risposta agli errori di ripristino di SQL Server provocati da backup danneggiati.

Impostazione delle opzioni

Per recuperare le impostazioni correnti delle opzioni del database, utilizzare la vista del catalogo sys.databases o la funzione DATABASEPROPERTYEX. Per un elenco dei valori predefiniti assegnati al database al momento della creazione, vedere Impostazione delle opzioni di database.

Dopo aver impostato un'opzione di database, la modifica diventa effettiva immediatamente.

Per modificare i valori predefiniti di qualsiasi opzione di database per tutti i nuovi database, modificare l'opzione di database appropriata nel database model.

Non tutte le opzioni di database supportano la clausola WITH <termination> o possono essere specificate in combinazione con altre opzioni. Nella tabella seguente sono elencate tali opzioni con indicazione del supporto della clausola di terminazione o dell'impostazione in combinazione con altre opzioni.

Categoria di opzioni	Impostazione in combinazione con altre opzioni	Supporto della clausola WITH <termination>
<db_state_option>	Sì	Sì
<db_user_access_option>	Sì	Sì
db_update_option>	Sì	Sì
<external_access_option>	Sì	No
<cursor_option>	Sì	No
<auto_option>	Sì	No
<sql_option>	Sì	No
<recovery_option>	Sì	No
<database_mirroring_option>	No	No
ALLOW_SNAPSHOT_ISOLATION	No	No
READ_COMMITTED_SNAPSHOT	No	Sì
<service_broker_option>	Sì	No
DATE_CORRELATION_OPTIMIZATION	Sì	Sì
<parameterization_option>	Sì	Sì

La cache dei piani per l'istanza di SQL Server viene cancellata quando si imposta di una delle opzioni seguenti:

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY

La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un improvviso temporaneo peggioramento delle prestazioni di esecuzione delle query. In SQL Server 2005 Service Pack 2 il log degli errori di SQL Server contiene il messaggio informativo seguente per ogni archivio cache cancellato nella cache dei piani: "SQL Server ha rilevato %d occorrenza/e di scaricamento dell'archivio cache '%s' (parte della cache dei piani) a causa di operazioni di manutenzione o riconfigurazione del database". Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache.

Spostamento di file

In SQL Server 2005, è possibile spostare file di dati e di log, di sistema o definiti dall'utente, specificando la nuova posizione in FILENAME. Questa procedura può risultare utile nei casi seguenti:

• Recupero in caso di errore. Ad esempio, quando il database è in modalità sospetta o viene chiuso a causa di un errore hardware.
• Spostamento pianificato.
• Spostamento per attività pianificate di manutenzione dei dischi.

Per ulteriori informazioni, vedere Spostamento dei file del database.

Inizializzazione dei file

Per impostazione predefinita, i file di dati e di log vengono inizializzati tramite il riempimento con zeri quando si esegue una delle operazioni seguenti:

• Creazione di un database.
• Aggiunta di file a un database esistente.
• Aumento delle dimensioni di un file esistente.
• Ripristino di un database o un filegroup.

In SQL Server 2005, i file di dati possono essere inizializzati istantaneamente. Ciò consente l'esecuzione rapida di queste operazioni sui file. Per ulteriori informazioni, vedere Inizializzazione di file di database.

Modifica delle regole di confronto del database

Prima di applicare regole di confronto diverse a un database, verificare che siano soddisfatte le condizioni seguenti:

1. Nessun altro utente sta utilizzando il database.
2. Nessun oggetto associato a schema dipende dalle regole di confronto del database.
   Se il database contiene gli oggetti seguenti che dipendono dalle regole di confronto del database, l'istruzione ALTER DATABASE database_name COLLATE avrà esito negativo. SQL Server restituirà un messaggio di errore per ogni oggetto che blocca l'azione ALTER:
   • Funzioni definite dall'utente e viste create con SCHEMABINDING.
   • Colonne calcolate.
   • Vincoli CHECK.
   • Funzioni valutate a livello di tabella che restituiscono tabelle contenenti colonne di tipo carattere con regole di confronto ereditate dalle regole di confronto predefinite del database.
3. La modifica delle regole di confronto del database non comporta la creazione di duplicati per i nomi di sistema degli oggetti di database.
   Se la modifica delle regole di confronto genera nomi duplicati, gli spazi dei nomi seguenti potrebbero impedire tale modifica:
   • Nomi di oggetti, quali stored procedure, tabelle, trigger e viste.
   • Nomi di schemi.
   • Entità, come gruppi, ruoli o utenti.
   • Nomi di tipi di dati scalari, come i tipi di dati di sistema e definiti dall'utente.
   • Nomi di cataloghi full-text.
   • Nomi di colonne o parametri in un oggetto.
   • Nomi di indici in una tabella.
     Se vengono generati nomi duplicati in seguito all'applicazione delle nuove regole di confronto, l'azione di modifica avrà esito negativo e in SQL Server verrà visualizzato un messaggio di errore che indica lo spazio dei nomi in cui è stato identificato il duplicato.

Visualizzazione di informazioni sul database

È possibile utilizzare le viste del catalogo, le funzioni di sistema e le stored procedure di sistema per restituire informazioni su database, file e filegroup. Per ulteriori informazioni, vedere Visualizzazione dei metadati dei database.

Autorizzazioni

È richiesta l'autorizzazione ALTER per il database.

Esempi

A. Aggiunta di un file a un database

Nell'esempio seguente viene aggiunto un file di dati da 5 MB al database AdventureWorks.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = '''+ @data_path + 't1dat2.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

B. Aggiunta di un filegroup con due file a un database

Nell'esempio seguente viene creato il filegroup Test1FG1 nel database AdventureWorks e vengono aggiunti due file da 5 MB al filegroup.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = '''+ @data_path + 't1dat3.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = '''+ @data_path + 't1dat4.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO

C. Aggiunta di due file di log a un database

Nell'esempio seguente vengono aggiunti due file di log da 5 MB al database AdventureWorks.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = '''+ @data_path + 'test2log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = '''+ @data_path + 'test3log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

D. Rimozione di un file da un database

Nell'esempio seguente viene rimosso uno dei file aggiunti nell'esempio B.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

E. Modifica di un file

Nell'esempio seguente vengono aumentate le dimensioni di uno dei file aggiunti nell'esempio B.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. Spostamento di un file in un diverso percorso

Nell'esempio seguente il file Test1dat2 creato nell'esempio A viene spostato in una nuova directory.

[!NOTA] È necessario spostare fisicamente il file nella nuova directory prima di eseguire l'esempio. In seguito, interrompere e avviare l'istanza di SQL Server oppure impostare il database AdventureWorks su OFFLINE e quindi su ONLINE per implementare la modifica.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Spostamento del database tempdb in un diverso percorso

Nell'esempio seguente viene spostato il database tempdb dal percorso corrente nel disco a un'altro percorso nel disco. Poiché tempdb viene ricreato a ogni avvio del servizio MSSQLSERVER, non è necessario spostare fisicamente i dati e i file di log. I file vengono creati quando il servizio viene riavviato durante il passaggio 3. Fino al riavvio del servizio, tempdb continua a funzionare nel percorso esistente.

1. Determinare i nomi di file logici del database tempdb e il rispettivo percorso corrente su disco.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. Modificare il percorso di ogni file tramite ALTER DATABASE.

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE  tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. Interrompere e riavviare l'istanza di SQL Server.

4. Verificare la modifica dei file.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. Eliminare i file tempdb.mdf e templog.ldf dai percorsi originali.

H. Impostazione di un filegroup come predefinito

Nell'esempio seguente il filegroup Test1FG1 creato nell'esempio B viene impostato come filegroup predefinito. Il filegroup PRIMARY viene quindi reimpostato come filegroup predefinito. Si noti che il nome PRIMARY deve essere delimitato da parentesi quadre o virgolette.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Impostazione di opzioni in un database

Nell'esempio seguente vengono impostate le opzioni relative al modello di recupero e ai controlli delle pagine di dati per il database di esempio AdventureWorks .

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

J. Impostazione del database su READ_ONLY

Per modificare lo stato di un database o un filegroup e impostare READ_ONLY o READ_WRITE, è necessario l'accesso esclusivo al database. Nell'esempio seguente viene impostata la modalità SINGLE_USER per il database in modo da ottenere l'accesso esclusivo. Nell'esempio, lo stato del database AdventureWorks viene quindi impostato su READ_ONLY e viene ripristinato l'accesso per tutti gli utenti.

[!NOTA] In questo esempio viene utilizzata l'opzione di terminazione WITH ROLLBACK IMMEDIATE nella prima istruzione ALTER DATABASE. Verrà eseguito il rollback di tutte le transazioni incomplete e qualsiasi connessione al database di esempio AdventureWorks verrà interrotta immediatamente.

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

K. Attivazione dell'isolamento dello snapshot in un database

Nell'esempio seguente viene attivata l'opzione relativa al framework di isolamento dello snapshot per il database 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

Il set di risultati indica che il framework di isolamento dello snapshot è attivato.

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

L. Creazione di una sessione di mirroring del database con un server di controllo del mirroring

Per configurare il mirroring del database con un server di controllo del mirroring, è necessario configurare la protezione e preparare il database mirror, nonché utilizzare l'istruzione ALTER DATABASE per l'impostazione dei partner. Per un esempio del processo di configurazione completo, vedere Impostazione del mirroring del database.

M. Failover manuale di una sessione di mirroring del database

È possibile avviare il failover manuale da qualsiasi partner di mirroring del database. Prima di eseguire il failover, è necessario verificare che il server che si ritiene essere il server principale corrente, sia effettivamente il server principale. Nel caso del database AdventureWorks, ad esempio, eseguire la query seguente nell'istanza del server che si ritiene essere il server principale corrente:

SELECT db.name, m.mirroring_role_desc 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks' 
GO

Se l'istanza del server in oggetto è effettivamente il server principale, il valore di mirroring_role_desc è Principal. Se questa istanza del server fosse invece il server di controllo del mirroring, l'istruzione SELECT restituirebbe Mirror.

Nell'esempio seguente si presuppone che il server sia il server principale corrente.

1. Eseguire il failover manuale sul partner di mirroring del database:

   ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
   GO
   

2. Per verificare i risultati del failover nel nuovo server mirror, eseguire la query seguente:

   SELECT name, mirroring_role_desc 
   FROM sys.databases WHERE name = N'AdventureWorks';
   GO
   

   Il valore corrente di mirroring_role_desc è ora Mirror.

Vedere anche

Riferimento

CREATE DATABASE (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
DROP DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_configure (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)

Altre risorse

Attivazione dei livelli di isolamento basati sul controllo delle versioni delle righe
Database di sistema.

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Nuovo contenuto: Aggiunta di informazioni sulle opzioni che comportano la cancellazione della cache dei piani nella sottosezione "Impostazione delle opzioni" della sezione Osservazioni e nella definizione di AUTO_CLOSE. Aggiunta di informazioni sull'utilizzo dell'opzione AUTO_UPDATE_STATISTICS_ASYNC in modalità utente singolo nella definizione di SINGLE_USER.
14 aprile 2006	Contenuto modificato: Aggiornamento della descrizione dell'opzione FAILOVER per indicare che è necessario utilizzare il database master come contesto di database. Aggiunta di una nota Importante nell'introduzione della sezione "<database_mirroring_option>". Nell'argomento ALLOW_SNAPSHOT_ISOLATION, aggiornamento delle informazioni sulla determinazione dello stato delle transazioni di isolamento dello snapshot nel database e sull'influenza di tale stato sul comportamento di SQL Server quando si modifica questa opzione. Correzione della definizione di DATE_CORRELATION_OPTIMIZATION.
5 dicembre 2005	Nuovo contenuto: Aggiunta di una nota alla definizione di ENABLE_BROKER. Aggiunta di un'indicazione per la modifica dell'opzione PAGE_VERIFY nei database aggiornati. Contenuto modificato: Rimozione dell'opzione SUPPLEMENTAL_LOGGING. Correzione dell'esempio G. Correzione delle informazioni sull'opzione TRUSTWORTHY nei database di sistema. Aggiornamento della definizione di READ_COMMITTED_SNAPSHOT per indicare che la modalità utente singolo non è obbligatoria. Aggiornamento della definizione di <db_state_option> per indicare che le opzioni OFFLINE, ONLINE e EMERGENCY non possono essere impostate quando il database è nello stato RESTORING.