ALTER DATABASE (Transact-SQL)

Mis à jour : 12 décembre 2006

Modifie une base de données ou les fichiers et groupes de fichiers associés à celle-ci. Ajoute ou supprime des fichiers et des groupes de fichiers d'une base de données, modifie ses attributs ou ceux de ses fichiers et groupes de fichiers, modifie le classement de la base de données et définit des options de base de données. Les captures instantanées de base de données ne peuvent pas être modifiées. Pour modifier les options de base de données associées à la réplication, utilisez sp_replicationdboption.

Conventions de la syntaxe de Transact-SQL

Syntaxe

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
}

Arguments

• database_name
  Nom de la base de données à modifier.

• MODIFY NAME **=**new_database_name
  Remplace le nom de la base de données par le nom spécifié comme new_database_name.

• COLLATE collation_name
  Spécifie le classement de la base de données. L'argument collation_name peut être un nom de classement Windows ou SQL. S'il n'est pas spécifié, le classement de l'instance de SQL Server est attribué à la base de données.

  Pour plus d'informations sur les noms de classements Windows et SQL , consultez COLLATE (Transact-SQL).

<add_or_modify_files>::=

Spécifie le fichier à ajouter, supprimer ou modifier.

• ADD FILE
  Ajoute un fichier à la base de données.

  • TO FILEGROUP { filegroup_name }
    Précise le groupe de fichiers auquel le fichier spécifié doit être ajouté. Pour afficher les groupes de fichiers et le groupe de fichiers par défaut actuels, utilisez l'affichage catalogue sys.filegroups.

• ADD LOG FILE
  Ajoute un fichier journal à la base de données spécifiée.

• REMOVE FILE logical_file_name
  Supprime la description du fichier logique d'une instance de SQL Server et supprime le fichier physique. Le fichier ne peut pas être supprimé s'il n'est pas vide.

  • logical_file_name
    Nom logique utilisé pour référencer le fichier dans SQL Server.

• MODIFY FILE
  Spécifie le fichier à modifier. Vous ne pouvez modifier qu'une seule propriété <filespec> à la fois. La clause NAME doit toujours être spécifiée dans <filespec> pour identifier le fichier à modifier. Si vous définissez l'option SIZE, la nouvelle taille doit être supérieure à la taille actuelle du fichier.

  Pour modifier le nom logique d'un fichier de données ou d'un fichier journal, spécifiez le nom logique du fichier à renommer dans la clause NAME et indiquez le nouveau nom logique à appliquer dans la clause NEWNAME. Par exemple :

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Pour déplacer un fichier de données ou un fichier journal vers un nouvel emplacement, spécifiez le nom de fichier logique actuel dans la clause NAME et les nouveaux chemin d'accès et nom de fichier de système d'exploitation dans la clause FILENAME. Par exemple :

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

  Lorsque vous déplacez un catalogue de texte intégral, spécifiez uniquement le nouveau fichier dans la clause FILENAME. N'indiquez pas le nom de fichier du système d'exploitation.

  Pour plus d'informations, consultez Déplacement des fichiers de bases de données.

<filespec>::=

Contrôle les propriétés des fichiers.

• NAME logical_file_name
  Spécifie le nom logique du fichier.

  • logical_file_name
    Nom logique utilisé pour référencer le fichier dans une instance de SQL Server.

• NEWNAME new_logical_file_name
  Spécifie un nouveau nom logique pour le fichier.

  • new_logical_file_name
    Nom remplaçant le nom de fichier logique existant. Le nom doit être unique dans la base de données et doit respecter les règles relatives aux identificateurs. Il peut s'agir d'une constante de type caractère ou Unicode, d'un identificateur régulier ou d'un identificateur délimité. Pour plus d'informations, consultez Utilisation des identificateurs comme noms d'objet.

• FILENAME 'os_file_name'
  Spécifie un nom de fichier du système d'exploitation (physique).

  • ' os_file_name '
    Chemin et nom de fichier utilisés par le système d'exploitation lorsque vous créez le fichier. Le fichier doit résider sur le serveur hébergeant SQL Server. Le chemin d'accès spécifié doit exister avant l'exécution de l'instruction ALTER DATABASE.

    Les paramètres SIZE, MAXSIZE et FILEGROWTH ne peuvent pas être définis lorsqu'un chemin UNC est spécifié pour le fichier.

    Les fichiers de données ne doivent pas être placés sur des systèmes de fichiers compressés à moins qu'il ne s'agisse de fichiers secondaires en lecture seule ou que la base de données soit en lecture seule. Les fichiers journaux ne doivent jamais être placés sur des systèmes de fichiers compressés. Pour plus d'informations, consultez La compression et les groupes de fichiers en lecture seule.

    Si le fichier se trouve sur une partition brute, os_file_name doit spécifier uniquement la lettre d'une unité correspondant à une partition brute existante. Chaque partition brute ne peut contenir qu'un seul fichier.

• SIZE size
  Spécifie la taille du fichier.

  • size
    Taille du fichier.

    Lorsqu'elle est spécifiée avec l'instruction ADD FILE, l'option size représente la taille initiale du fichier. Lorsqu'elle est spécifiée avec MODIFY FILE, size représente la nouvelle taille du fichier et doit avoir une valeur supérieure à la taille actuelle du fichier.

    Lorsque vous ne précisez pas le paramètre size pour le fichier primaire, le moteur de base de données SQL Server 2005 utilise la taille du fichier primaire de la base de données model. Lorsqu'un fichier journal ou fichier de données secondaire est spécifié mais que size n'est pas spécifié pour ce fichier, le moteur de base de données lui donne une taille de 1 Mo.

    Les indications Ko, Mo, Go et To peuvent être utilisées pour indiquer qu'il s'agit de kilo-octets, mégaoctets, gigaoctets ou téraoctets. La taille est exprimée par défaut en mégaoctets. Précisez un nombre entier sans aucune décimale. Pour spécifier une taille égale à une fraction d'un mégaoctet, convertissez la valeur en kilo-octets en multipliant le nombre par 1024. Indiquez, par exemple, 1536 kilo-octets au lieu de 1,5 mégaoctet (1,5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Taille maximale que peut atteindre le fichier.

  • max_size
    Taille maximale du fichier. Les indications Ko, Mo, Go et To peuvent être utilisées pour indiquer qu'il s'agit de kilo-octets, mégaoctets, gigaoctets ou téraoctets. La taille est exprimée par défaut en mégaoctets. Précisez un nombre entier sans aucune décimale. Si l'option max_size n'est pas spécifiée, la taille du fichier augmente jusqu'à ce que le disque soit saturé.
  • UNLIMITED
    Précise que la taille du fichier peut croître jusqu'à ce que le disque soit saturé. Dans SQL Server 2005, un fichier journal spécifié avec une croissance illimitée a une taille maximale de 2 To et un fichier de données une taille maximale de 16 To.

• FILEGROWTH growth_increment
  Spécifie l'incrément de croissance automatique du fichier. Le paramètre FILEGROWTH d'un fichier ne peut dépasser le paramètre MAXSIZE.

  • growth_increment
    Quantité d'espace ajoutée au fichier chaque fois qu'un espace supplémentaire s'avère nécessaire.

    La valeur peut être exprimée en Mo, Ko, Go, To ou pourcentage (%). Si un nombre est mentionné sans précision aucune, la valeur par défaut est en mégaoctets. Lorsque % est spécifié, la taille de l'incrément de croissance est le pourcentage choisi de la taille du fichier au moment où l'incrémentation a lieu. La taille spécifiée est arrondie à la valeur multiple de 64 Ko la plus proche.

    Une valeur 0 indique que la croissance automatique est désactivée et qu'aucun espace supplémentaire n'est autorisé.

    Si vous ne précisez pas FILEGROWTH, la valeur par défaut est de 1 Mo pour les fichiers de données et de 10 % pour les fichiers journaux et la valeur minimale est de 64 Ko.

    Remarque :
    Dans SQL Server 2005, l'incrément de croissance par défaut pour les fichiers de données est passé de 10 % à 1 Mo. La valeur par défaut de 10 % est maintenue pour les fichiers journaux.

• OFFLINE
  Place le fichier en mode hors connexion et rend tous les objets du groupe de fichiers inaccessibles.

  Attention :
  Utilisez cette option uniquement lorsque le fichier est endommagé et peut être restauré. Un fichier configuré avec l'option OFFLINE ne peut être remis en ligne qu'en le restaurant à partir d'une sauvegarde. Pour plus d'informations sur la restauration d'un seul fichier, consultez RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Ajoute, modifie ou supprime un groupe de fichiers à partir de la base de données.

• ADD FILEGROUP filegroup_name
  Ajoute un groupe de fichiers à la base de données.

• REMOVE FILEGROUP filegroup_name
  Supprime un groupe de fichiers de la base de données. Le groupe de fichiers ne peut pas être supprimé s'il n'est pas vide. Commencez par supprimer tous les fichiers du groupe. Pour plus d'informations, consultez « REMOVE FILE logical_file_name », plus haut dans cette rubrique.

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
  Modifie le groupe de fichiers en lui affectant l'état READ_ONLY ou READ_WRITE, en sélectionnant le groupe de fichiers comme groupe par défaut pour la base de données ou en modifiant le nom du groupe de fichiers.

  • <filegroup_updatability_option>
    Définit la propriété read-only ou read/write du groupe de fichiers.
  • DEFAULT
    Modifie le groupe de fichiers par défaut de la base de données pour filegroup_name. Dans une base de données, un seul groupe de fichiers peut être choisi comme groupe de fichiers par défaut. Pour plus d'informations, consultez Présentation des fichiers et des groupes de fichiers.
  • NAME = new_filegroup_name
    Modifie le nom du groupe de fichiers pour new_filegroup_name.

<filegroup_updatability_option>::=

Définit la propriété read-only ou read/write du groupe de fichiers.

• READ_ONLY | READONLY
  Précise que le groupe de fichiers est en mode lecture seule. La mise à jour des objets n'est pas autorisée. Le groupe de fichiers principal ne peut pas être en mode lecture seule. Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

  Étant donné qu'une base de données en lecture seule interdit la modification des données :

  • La récupération automatique est ignorée au démarrage du système.
  • Le compactage de la base de données est impossible.
  • Tout verrouillage est impossible dans les bases de données en lecture seule, ce qui améliore les performances des requêtes.

  Remarque :
  Le mot clé READONLY sera supprimé de la prochaine version de Microsoft SQL Server. Évitez d'utiliser READONLY dans le développement de nouvelles applications et prévoyez de modifier celles qui utilisent actuellement READONLY. Utilisez à la place READ_ONLY.

• READ_WRITE | READWRITE
  Spécifie que le groupe a l'option READ_WRITE. Les objets du groupe de fichiers peuvent être mis à jour. Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

  Remarque :
  Le mot clé READWRITE sera supprimé de la prochaine version de Microsoft SQL Server. Évitez d'utiliser READWRITE dans le développement de nouvelles applications et prévoyez de modifier celles qui utilisent actuellement READWRITE. Utilisez à la place READ_WRITE.

Vous pouvez déterminer l'état de ces options en consultant la colonne is_read_only de l'affichage catalogue sys.databases ou la propriété Updateability de la fonction DATABASEPROPERTYEX.

<db_state_option>::=

Contrôle l'état de la base de données.

• OFFLINE
  La base de données est fermée et arrêtée correctement, puis marquée comme étant déconnectée. Tant que la base de données est hors connexion, elle ne peut pas être modifiée.

• ONLINE
  La base de données est ouverte et peut être utilisée.

• EMERGENCY
  La base de données est en lecture seule (READ_ONLY), la journalisation désactivée et l'accès limité aux membres du rôle de serveur fixe sysadmin. EMERGENCY est principalement utilisé à des fins de dépannage. Par exemple, une base de données marquée SUSPECT en raison d'un fichier journal corrompu peut être définie à l'état EMERGENCY. L'administrateur système peut alors accéder en lecture seule à la base de données. Seuls les membres du rôle de serveur fixe sysadmin peuvent définir l'état EMERGENCY pour une base de données.

Vous pouvez déterminer l'état de cette option en consultant les colonnes state et state_desc de l'affichage catalogue sys.databases ou la propriété Status de la fonction DATABASEPROPERTYEX. Pour plus d'informations, consultez États d'une base de données.

Une base de données marquée RESTORING ne peut pas être définie à OFFLINE, ONLINE ou EMERGENCY. Une base de données peut être dans l'état RESTORING durant une opération de restauration active, ou lorsqu'une opération de restauration d'un fichier de base de données ou d'un fichier journal échoue car un fichier de sauvegarde est corrompu. Pour plus d'informations, consultez Réponse aux erreurs de restauration SQL Server provoquées par des sauvegardes endommagées.

<db_user_access_option> ::=

Contrôle l'accès utilisateur à la base de données.

• SINGLE_USER
  Indique que l'accès à la base de données n'est autorisé qu'à un seul utilisateur à la fois. Si SINGLE_USER est spécifié et que d'autres utilisateurs sont connectés à la base de données, l'instruction ALTER DATABASE est bloquée jusqu'à ce que tous les autres utilisateurs se déconnectent de cette base de données. Pour substituer ce comportement, consultez la clause WITH <termination>.

  La base de données demeure en mode SINGLE_USER même si l'utilisateur qui a défini l'option se déconnecte. À ce stade, un autre utilisateur (et un seul) peut se connecter à la base de données.

  Avant d'affecter la valeur SINGLE_USER à la base de données, vérifiez que l'option AUTO_UPDATE_STATISTICS_ASYNC a la valeur OFF. Si la valeur est ON, le thread d'arrière-plan utilisé pour mettre à jour les statistiques se connecte à la base de données et vous ne pourrez pas accéder à celle-ci en mode mono-utilisateur. Pour afficher l'état de cette option, interrogez la colonne is_auto_update_stats_async_on dans l'affichage catalogue sys.databases. Si l'option a la valeur ON, effectuez les tâches suivantes :

  1. Affectez la valeur OFF à AUTO_UPDATE_STATISTICS_ASYNC.
  2. Recherchez les travaux des statistiques asynchrones actifs en interrogeant la vue de gestion dynamique sys.dm_exec_background_job_queue.
  3. S'il y a des travaux actifs, laissez ces travaux s'achever ou terminez-les manuellement à l'aide de KILL STATS JOB.

• RESTRICTED_USER
  RESTRICTED_USER permet uniquement aux membres du rôle de base de données fixe db_owner et des rôles de serveur fixes dbcreator et sysadmin de se connecter à la base de données, mais n'en limite pas le nombre. Toutes les connexions à la base de données sont déconnectées dans la plage de temps spécifiée par la clause d'arrêt de l'instruction ALTER DATABASE. Après que la base est passée à l'état RESTRICTED_USER, toute tentative de connexion par des utilisateurs non qualifiés est refusée.

• MULTI_USER
  Tous les utilisateurs bénéficiant des autorisations appropriées peuvent se connecter à la base de données.

Vous pouvez déterminer l'état de cette option en consultant la colonne user_access de l'affichage catalogue sys.databases ou la propriété UserAccess de la fonction DATABASEPROPERTYEX.

<db_update_option>::=

Contrôle si des mises à jour sont autorisées dans la base de données.

• READ_ONLY
  Les utilisateurs peuvent lire des données dans la base de données mais ils n'ont pas le droit de la modifier.

• READ_WRITE
  La base de données est accessible aux opérations de lecture et d'écriture.

Pour modifier cet état, vous devez bénéficier d'un accès exclusif à la base de données. Pour plus d'informations, consultez la clause SINGLE_USER.

<external_access_option>::=

Contrôle si des ressources externes, par exemple des objets d'une autre base de données, peuvent accéder à la base de données.

• DB_CHAINING { ON | OFF }

  • ON
    La base de données peut être la source ou la cible d'une chaîne des propriétés des bases de données croisées.
  • OFF
    La base de données ne peut prendre part à un chaînage des propriétés des bases de données croisées.

  Important :
  L'instance de SQL Server reconnaîtra ce paramètre lorsque l'option de serveur cross db ownership chaining a la valeur 0 (OFF). Lorsque cross db ownership chaining a la valeur 1 (ON), toutes les bases de données utilisateur peuvent participer aux chaînages des propriétés des bases de données croisées, quelle que soit la valeur de cette option. Cette option est configurée à l'aide de sp_configure.

  Pour définir cette option, l'appartenance en tant que membre du rôle serveur fixe sysadmin est nécessaire. L'option DB_CHAINING ne peut pas être définie sur ces bases de données système : master, model et tempdb.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_db_chaining_on de l'affichage catalogue sys.databases.

  Pour plus d'informations, consultez Chaînes de propriétés.

• TRUSTWORTHY { ON | OFF }

  • ON
    Les modules de base de données (par exemple, les procédures stockées ou les fonctions définies par l'utilisateur) qui utilisent un contexte d'emprunt d'identité peuvent accéder à des ressources en dehors de la base de données.
  • OFF
    Les modules de base de données qui utilisent l'emprunt d'identité ne peuvent pas accéder à des ressources externes à la base de données;

  TRUSTWORTHY prend la valeur OFF chaque fois que la base de données est attachée.

  Par défaut, pour toutes les bases de données système, sauf pour la base msdb, l'option TRUSTWORTHY est définie à OFF (désactivé). La valeur ne peut pas être modifiée pour les bases de données model et tempdb. Nous vous recommandons de ne jamais définir l'option TRUSTWORTHY à ON (activé) pour la base de données master.

  Pour définir cette option, l'appartenance en tant que membre du rôle serveur fixe sysadmin est nécessaire.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_trustworthy_on de l'affichage catalogue sys.databases.

<cursor_option>::=

Contrôle les options du curseur.

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    Tout curseur ouvert au moment où une transaction est validée ou annulée est fermé.
  • OFF
    Les curseurs restent ouverts lorsqu'une transaction est validée. L'annulation d'une transaction ferme tous les curseurs à l'exception de ceux définis avec la valeur INSENSITIVE ou STATIC.

  Les paramètres de niveau connexion définis à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de CURSOR_CLOSE_ON_COMMIT. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui désactive l'option CURSOR_CLOSE_ON_COMMIT pour la session (valeur OFF), lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_cursor_close_on_commit_on de l'affichage catalogue sys.databases ou la propriété IsCloseCursorsOnCommitEnabled de la fonction DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Détermine si la portée du curseur utilise la valeur LOCAL ou GLOBAL.

  • LOCAL
    Si LOCAL est défini et qu'aucun curseur n'est défini comme GLOBAL lors de sa création, le curseur est d'étendue locale pour le lot d'instructions, la procédure stockée ou le déclencheur dans lequel il a été créé. Le nom du curseur n'est valide que dans cette étendue. Le curseur peut être référencé par des variables de curseur locales du lot d'instructions, de la procédure stockée ou du déclencheur, ou bien par un paramètre OUTPUT d'une procédure stockée. Le curseur est désalloué implicitement à la fin du lot d'instructions, de la procédure stockée ou du déclencheur, à moins d'avoir été renvoyé dans un paramètre OUTPUT. S'il a été renvoyé dans un paramètre OUTPUT, le curseur est désalloué lorsque la dernière variable qui y fait référence est désallouée, ou est hors de portée.
  • GLOBAL
    Si GLOBAL est spécifié et qu'aucun curseur n'est défini comme LOCAL lors de sa création, le curseur est d'étendue globale pour la connexion. Toute procédure stockée ou tout lot d'instructions exécuté par la connexion peut faire référence au nom du curseur.

  Le curseur n'est désalloué implicitement qu'au moment de la déconnexion. Pour plus d'informations, consultez DECLARE CURSOR (Transact-SQL).

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_local_cursor_default de l'affichage catalogue sys.databases ou la propriété IsLocalCursorsDefault de la fonction DATABASEPROPERTYEX.

<auto_option>::=

Contrôle les options automatiques.

• AUTO_CLOSE { ON | OFF }

  • ON
    La base de données est arrêtée correctement et ses ressources sont libérées dès que le dernier utilisateur l'a quittée.

    La base de données est rouverte automatiquement lorsqu'un utilisateur tente de l'utiliser à nouveau. par exemple, en exécutant une instruction USE database_name. Si la base de données a été fermée correctement et que AUTO_CLOSE a la valeur ON, elle ne se rouvre qu'au moment où un utilisateur tente de l'utiliser au redémarrage suivant du .

  • OFF
    La base de données reste ouverte après que le dernier utilisateur l'a quitté.

  L'option AUTO_CLOSE est utile pour les bases de données bureautiques, puisqu'elle permet aux fichiers de base de données d'être gérés comme des fichiers normaux. Ceux-ci peuvent être déplacés, copiés en vue d'une sauvegarde ou même transmis par messagerie électronique à d'autres utilisateurs.

  Remarque :

  Dans les précédentes versions de SQL Server, AUTO_CLOSE est un processus synchrone susceptible d'affecter les performances si l'application qui accède à la base de données, établit et interrompt à plusieurs reprises les connexions au . Dans SQL Server 2005, le processus AUTO_CLOSE est asynchrone ; l'ouverture et la fermeture répétées de la base de données n'ont plus aucune incidence sur les performances.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_close_on de l'affichage catalogue sys.databases ou la propriété IsAutoClose de la fonction DATABASEPROPERTYEX.

  Remarque :
  Lorsque AUTO_CLOSE a la valeur ON, certaines colonnes de l'affichage catalogue sys.databases et la fonction DATABASEPROPERTYEX retournent NULL parce que la base de données est inaccessible et qu'aucune donnée ne peut être extraite. Pour résoudre ce problème, exécutez une instruction USE pour ouvrir la base de données.

  Remarque :
  La mise en miroir exige AUTO_CLOSE OFF.

  Si la base de données à la valeur AUTOCLOSE = ON, une opération qui initialise un arrêt de la base de données automatique nettoie le cache du plan pour l'instance de SQL Server. Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. Dans SQL Server 2005 Service Pack 2, pour chaque mémoire cache effacée du cache du plan, le journal des erreurs de SQL Server contient le message d'information suivant : SQL Server a rencontré %d occurrence(s) de vidages de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance ou de reconfiguration de base de données. Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    Les statistiques manquantes, nécessaires à une requête dans le cadre de son optimisation, sont créées automatiquement durant l'optimisation de la requête.

    L'ajout de statistiques améliore les performances des requêtes car l'optimiseur de requête SQL Server peut déterminer plus précisément comment évaluer une requête. Si les statistiques ne sont pas utilisées, le les supprime automatiquement. Si elle a la valeur OFF, les statistiques ne sont pas créées automatiquement et peuvent au lieu de cela être créées manuellement. Pour plus d'informations, consultez Statistiques d'index.

  • OFF
    Les statistiques doivent être créées manuellement.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_update_stats_on de l'affichage catalogue sys.databases ou la propriété IsAutoUpdateStatistics de la fonction DATABASEPROPERTYEX.

  Remarque :
  L'optimiseur de requête traite toutes les tables système internes comme si l'option AUTO_CREATE_STATISTICS avait la valeur ON, quelle que soit l'option actuellement définie. Ces tables incluent les tables de base système, les index XML, les index de texte intégral, les tables de files d'attente de Service Broker et les tables de notifications de requêtes.

• AUTO_SHRINK { ON | OFF }

  • ON
    Les fichiers de base de données peuvent faire l'objet d'un compactage périodique.

    Tant les fichiers de données que les journaux des transactions peuvent être automatiquement compactés. AUTO_SHRINK ne réduit la taille du journal des transactions que si le mode de récupération SIMPLE est défini pour la base de données ou si le journal est sauvegardé. Si elle possède la valeur OFF, les fichiers de base de données ne sont pas compactés automatiquement lors des vérifications périodiques de l'espace inutilisé.

    L'option AUTO_SHRINK provoque le compactage dès qu'un fichier comprend plus de 25 % d'espace inutilisé. Le fichier est réduit à une taille laissant 25 % d'espace inutilisé ou à sa taille initiale au moment de sa création, selon la valeur la plus élevée.

    Vous ne pouvez pas compacter une base de données en lecture seule.

  • OFF
    Les fichiers de base de données ne sont pas compactés automatiquement lors des vérifications périodiques de l'espace inutilisé.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_auto_shrink_on de l'affichage catalogue sys.databases ou la propriété IsAutoShrink de la fonction DATABASEPROPERTYEX.

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Les statistiques obsolètes, nécessaires à une requête dans le cadre de son optimisation, sont automatiquement mises à jour durant l'optimisation de la requête.
  • OFF
    Les statistiques doivent être mises à jour manuellement.

  Remarque :
  L'instruction UPDATE STATISTICS réactive la mise à jour statistique automatique sur la table ou la vue cible sauf si la clause NORECOMPUTE est spécifiée.

  Remarque :
  L'optimiseur de requête traite toutes les tables système internes comme si l'option AUTO_UPDATE_STATISTICS avait la valeur ON, quelle que soit l'option actuellement définie. Ces tables incluent les tables de base système, les index XML, les index de texte intégral, les tables de files d'attente de Service Broker et les tables de notifications de requêtes.

  Pour plus d'informations, consultez Statistiques d'index.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Les requêtes qui initialisent une mise à jour automatique des statistiques obsolètes n'attendent pas la fin de la mise à jour des statistiques avant d'effectuer la compilation. Les requêtes suivantes utiliseront les statistiques mises à jour une fois celles-ci disponibles.
  • OFF
    Les requêtes qui initialisent une mise à jour automatique des statistiques obsolètes attendent de pouvoir utiliser les statistiques mises à jour dans le plan d'optimisation de requête.

  Affecter la valeur ON à cette option n'a aucun effet à moins que AUTO_UPDATE_STATISTICS ait également la valeur ON.

  Pour plus d'informations, consultez Statistiques d'index.

<sql_option>::=

Contrôle les options de conformité ANSI au niveau de la base de données.

• ANSI_NULL_DEFAULT { ON | OFF }
  Détermine la valeur par défaut, NULL ou NOT NULL, d'une colonne, d'un type de données d'alias ou d'un type CLR défini par l'utilisateur dont le paramètre d'acceptation des valeurs NULL n'est pas défini de façon explicite dans les instructions CREATE TABLE ou ALTER TABLE. Les colonnes définies avec des contraintes respectent les règles de contrainte, quelle que soit la valeur de ce paramètre.

  • ON
    La valeur par défaut est NULL.
  • OFF
    La valeur par défaut est NOT NULL.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULL_DEFAULT au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULL_DEFAULT pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_NULL_DFLT_ON (Transact-SQL).

  Pour garantir la compatibilité ANSI, l'activation (ON) de l'option de base de données ANSI_NULL_DEFAULT entraîne la définition de NULL comme valeur par défaut de la base de données.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_ansi_null_default_on de l'affichage catalogue sys.databases ou la propriété IsAnsiNullDefault de la fonction DATABASEPROPERTYEX.

• ANSI_NULLS { ON | OFF }

  • ON
    Toutes les comparaisons avec une valeur NULL produisent le résultat UNKNOWN (inconnu).
  • OFF
    Les comparaisons de valeurs non UNICODE à une valeur NULL donnent la valeur TRUE si les deux valeurs sont NULL.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut défini pour ANSI_NULLS au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_NULLS pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_NULLS (Transact-SQL).

  SET ANSI_NULLS doit également avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_ansi_nulls_on de l'affichage catalogue sys.databases ou la propriété IsAnsiNullsEnabled de la fonction DATABASEPROPERTYEX.

• ANSI_PADDING { ON | OFF }

  • ON
    Les chaînes sont complétées pour avoir la même longueur avant leur conversion ou insertion dans un type de données varchar ou nvarchar.

    Les espaces à droite dans les valeurs de type caractère insérées dans des colonnes varchar ou nvarchar et les zéros à droite dans les valeurs binaires insérées dans des colonnes varbinary ne sont pas supprimés. Les valeurs ne sont pas complétées à concurrence de la longueur de la colonne.

  • OFF
    Les espaces à droite pour varchar ou nvarchar et les zéros pour varbinary sont supprimés.

  Lorsque cette option a la valeur OFF, elle affecte uniquement la définition de nouvelles colonnes.

  char(n Les colonnes ) et binary(n) qui acceptent les valeurs NULL sont complétées à concurrence de la longueur de la colonne lorsque ANSI_PADDING a la valeur ON mais les espaces et les zéros à droite sont supprimés lorsque ANSI_PADDING a la valeur OFF. Les colonnes char(n) et binary(n) qui n'acceptent pas les valeurs NULL sont toujours complétées à concurrence de la longueur de la colonne.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_PADDING au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_PADDING pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_PADDING (Transact-SQL).

  Important :
  Il est recommandé de toujours affecter la valeur ON à l'option ANSI_PADDING. Par ailleurs, ANSI_PADDING doit être avoir la valeur ON lorsque vous créez ou manipulez des index dans des colonnes calculées ou des vues indexées.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_ansi_padding_on de l'affichage catalogue sys.databases ou la propriété IsAnsiPaddingEnabled de la fonction DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    Des erreurs ou avertissements sont générés en présence de certaines conditions, par exemple une division par zéro se produisent ou lorsque des valeurs NULL apparaissent dans des fonctions d'agrégat.
  • OFF
    Aucun avertissement n'est généré et des valeurs NULL sont retournées lorsque des conditions telles qu'une division par zéro sont vérifiées.

  SET ANSI_WARNINGS doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de ANSI_NULLS défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à ANSI_WARNINGS pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET ANSI_WARNINGS (Transact-SQL).

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_ansi_warnings_on de l'affichage catalogue sys.databases ou la propriété IsAnsiWarningsEnabled de la fonction DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Arrête une requête lorsqu'un dépassement de capacité ou une division par zéro se produit durant son exécution.
  • OFF
    Un message d'avertissement s'affiche si l'une de ces erreurs se produit, mais le traitement de la requête, du lot d'instructions ou de la transaction se poursuit, comme s'il n'y avait pas d'erreur.

  SET ARITHABORT doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_arithabort_on de l'affichage catalogue sys.databases ou la propriété IsArithmeticAbortEnabled de la fonction DATABASEPROPERTYEX.

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    Le résultat d'une concaténation est NULL lorsque l'un des deux opérandes est NULL. Par exemple, la concaténation de la chaîne de caractères « Ceci est  » et NULL donne la valeur NULL et non la valeur « Ceci est  ».
  • OFF
    La valeur NULL est considérée comme une chaîne de caractères vide.

  CONCAT_NULL_YIELDS_NULL doit avoir la valeur ON lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées.

  Les paramètres définis à l'aide de l'instruction SET au niveau de la connexion se substituent au paramètre par défaut de CONCAT_NULL_YIELDS_NULL défini au niveau de la base de données. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à CONCAT_NULL_YIELDS_NULL pour la session lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_concat_null_yields_null_on de l'affichage catalogue sys.databases ou la propriété IsNullConcat de la fonction DATABASEPROPERTYEX.

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    Les guillemets doubles peuvent être utilisés pour entourer des identificateurs délimités.

    Toutes les chaînes délimitées par des guillemets doubles sont considérées comme des identificateurs d'objet. Les identificateurs entre guillemets n'ont pas à respecter les règles Transact-SQL applicables aux identificateurs. Ils peuvent être des mots clés et contenir des caractères généralement interdits dans les identificateurs Transact-SQL. Si un guillemet simple (') fait partie de la chaîne littérale, il pourra être représenté par un guillemet double ('').

  • OFF
    Les identificateurs ne peuvent figurer entre guillemets et doivent respecter toutes les règles Transact-SQL en matière d'identificateurs. Les chaînes littérales peuvent être délimitées par des guillemets simples ou doubles.

  SQL Server permet également de délimiter les identificateurs par des crochets ([ ]). Les identificateurs entre crochets peuvent toujours être utilisés, quel que soit le paramètre de QUOTED_IDENTIFIER. Pour plus d'informations, consultez Identificateurs délimités (Moteur de base de données).

  Lors de la création d'une table, l'option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table, même si elle a la valeur OFF au moment de sa création.

  Les paramètres définis au niveau de la connexion à l'aide de l'instruction SET se substituent au paramètre de base de données par défaut de QUOTED_IDENTIFIER. Par défaut, les clients ODBC et OLE DB génèrent une instruction SET de niveau connexion qui affecte la valeur ON à QUOTED_IDENTIFIER, lors de la connexion à une instance de SQL Server. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_quoted_identifier_on de l'affichage catalogue sys.databases ou la propriété IsQuotedIdentifiersEnabled de la fonction DATABASEPROPERTYEX.

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    Une erreur est générée lors d'une perte de précision dans une expression.
  • OFF
    Les pertes de précision ne génèrent pas de messages d'erreur et le résultat est arrondi en fonction de la précision de la colonne ou de la variable contenant le résultat.

  NUMERIC_ROUNDABORT doit être défini avec la valeur OFF lorsque vous créez ou modifiez des index dans des colonnes calculées ou des vues indexées

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_numeric_roundabort_on de l'affichage catalogue sys.databases ou la propriété IsNumericRoundAbortEnabled de la fonction DATABASEPROPERTYEX.

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    L'activation récursive des déclencheurs AFTER est autorisée.
  • OFF
    Seule l'activation récursive directe des déclencheurs AFTER n'est pas autorisée. Pour désactiver également la récursivité indirecte des déclencheurs AFTER, affectez la valeur 0 à l'option serveur nested triggers à l'aide de sp_configure.

  Remarque :
  Seule la récursivité directe est désactivée lorsque RECURSIVE_TRIGGERS possède la valeur OFF. Pour désactiver la récursivité indirecte, vous devez aussi affecter la valeur 0 à l'option serveur nested triggers.

  Vous pouvez déterminer l'état de cette option en consultant la colonne is_recursive_triggers_on de l'affichage catalogue sys.databases ou la propriété IsRecursiveTriggersEnabled de la fonction DATABASEPROPERTYEX.

<recovery_option> ::=

Contrôle les options de récupération de base de données et la vérification des erreurs d'E/S disque.

• FULL
  Fournit la récupération complète après la défaillance d'un support à l'aide des sauvegardes des journaux des transactions. Si un fichier de données est endommagé, la récupération des supports peut restaurer toutes les transactions validées. Pour plus d'informations, consultez Sauvegarde en mode de récupération complet.

• BULK_LOGGED
  Fournit la récupération après la défaillance d'un support en associant des performances optimales et une utilisation minimale de l'espace réservé aux fichiers journaux pour certaines opérations en bloc ou à grande échelle. Pour plus d'informations sur les opérations journalisées en bloc, consultez Opérations journalisées minimales. Avec le mode de récupération BULK_LOGGED, ces opérations font l'objet d'une journalisation minimale. Pour plus d'informations, consultez Sauvegarde avec le mode de récupération utilisant les journaux de transactions.

• SIMPLE
  Une stratégie de sauvegarde simple utilisant un espace de journalisation minimal est fournie. L'espace réservé aux fichiers journaux peut être automatiquement réutilisé lorsqu'il n'est plus utilisé par la récupération des défaillances serveur. Pour plus d'informations, consultez Sauvegarde selon le mode de récupération simple.

  Important :
  Le mode de récupération simple est plus facile à gérer que les deux autres modes, mais le risque de perte de données est plus élevé lorsqu'un fichier de données est endommagé. Toutes les modifications apportées depuis la dernière sauvegarde de la base de données ou de la sauvegarde différentielle de la base de données sont perdues et doivent être réintroduites manuellement.

Le mode de récupération par défaut dépend du mode de récupération de la base de données model. Pour plus d'informations sur la sélection du mode de récupération le plus approprié, consultez Choix du mode de récupération d'une base de données.

Vous pouvez déterminer l'état de cette option en consultant les colonnes recovery_model et recovery_model_desc de l'affichage catalogue sys.databases ou la propriété Recovery de la fonction DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Les pages incomplètes peuvent être détectées par le moteur de base de données.
  • OFF
    Les pages incomplètes ne peuvent pas être détectées par le moteur de base de données.

  Important :
  La structure syntaxique TORN_PAGE_DETECTION ON | OFF sera supprimée de la version ultérieure de Microsoft SQL Server. Évitez d'utiliser cette structure syntaxique dans le développement de nouvelles applications et prévoyez de modifier celles qui l'utilisent actuellement. Utilisez l'option PAGE_VERIFY à la place.

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  Détecte les pages de base de données endommagées provoquées par des erreurs de chemin d'E/S disque. Les erreurs de chemin d'E/S disque peuvent endommager la base de données et résultent généralement d'une défaillance matérielle des disques ou de pannes d'alimentation survenant au moment de l'écriture de la page sur le disque.

  • CHECKSUM
    Calcule une somme de contrôle du contenu d'une page entière et stocke la valeur dans l'en-tête de page lorsque celle-ci est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, la somme de contrôle est recalculée et le résultat comparé à la valeur préalablement stockée dans l'en-tête de la page. Si les valeurs diffèrent, le message d'erreur 824 (indiquant l'échec d'une somme de contrôle) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Un échec de somme de contrôle indique un problème de chemin d'E/S. Pour en déterminer la cause, vous devez examiner le matériel, les pilotes de microprogrammes, le BIOS, les pilotes de filtre (par exemple un logiciel antivirus) et d'autres composants de chemin d'E/S.
  • TORN_PAGE_DETECTION
    Enregistre un bit spécifique pour chaque secteur de 512 octets dans la page de base de données et le stocke dans l'en-tête de page de base de données au moment où la page est écrite sur le disque. Lorsque la page est ensuite lue à partir du disque, les bits endommagés stockés dans l'en-tête de la page sont comparés aux informations réelles du secteur concerné. Lorsque les valeurs ne concordent pas, cela signifie que seule une partie de la page a été écrite sur le disque. Dans un tel cas, le message d'erreur 824 (indiquant une erreur de page endommagée) est signalé dans le journal des erreurs SQL Server et le journal des événements Windows. Les pages endommagées sont généralement détectées par la récupération de base de données s'il s'agit réellement d'une écriture de page incomplète. Toutefois, les échecs de chemin d'E/S peuvent donner lieu à tout moment à une page endommagée.
  • NONE
    Les écritures de page de base de données ne génèrent pas de valeur CHECKSUM ou TORN_PAGE_DETECTION. SQL Server ne vérifie pas une somme de contrôle ou une page endommagée au cours d'une lecture même si l'en-tête de page comporte une valeur CHECKSUM ou TORN_PAGE_DETECTION.

  Prenez en considération les points suivants lorsque vous utilisez l'option PAGE_VERIFY :

  • Dans SQL Server 2005, le paramètre par défaut est CHECKSUM. Dans SQL Server 2000, TORN_PAGE_DETECTION est le paramètre par défaut.
  • Lorsqu'une base de données utilisateur ou système est mise à niveau vers SQL Server 2005, la valeur PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) est conservée. Nous vous recommandons d'utiliser CHECKSUM.
  • Même si TORN_PAGE_DETECTION utilise moins de ressources, il ne fournit qu'un sous-ensemble limité de la protection offerte par CHECKSUM.
  • Il est possible de définir PAGE_VERIFY sans mettre la base de données hors connexion, la verrouiller ni empêcher d'une quelconque façon l'accès concurrentiel à cette base de données.
  • CHECKSUM et TORN_PAGE_DETECTION s'excluent mutuellement. Les deux options ne peuvent pas être activées en même temps.

  Lors d'une détection de page endommagée ou d'échec de somme de contrôle, vous pouvez procéder à une récupération par une restauration des données ou éventuellement par une reconstruction de l'index si la défaillance se limite à des pages d'index. En présence d'une erreur de somme de contrôle, pour déterminer le type de page(s) de données affectée(s), exécutez DBCC CHECKDB. Pour plus d'informations sur les options de restauration, consultez Arguments RESTORE (Transact-SQL). Bien que la restauration des données permette de résoudre le problèmes de données endommagées, la cause première, par exemple une défaillance matérielle du disque, doit être identifiée et corrigée le plus rapidement possible pour éviter que ces erreurs se reproduisent.

  SQL Server procède à quatre nouvelles tentatives de lecture qui échoue avec une erreur de somme de contrôle, de page endommagée ou d'E/S disque. Si la lecture réussit au cours d'une de ces tentatives, un message est écrit dans le journal des erreurs et l'exécution de la commande qui a déclenché la lecture continue. Si les tentatives de lecture échouent, la commande échoue elle aussi avec le message d'erreur 824.

  Pour plus d'informations sur les messages d'erreurs 823 et 824, sommes de contrôle, pages endommagées, tentatives de lecture ainsi que d'autres fonctionnalités d'audit d'E/S de SQL Server, consultez ce site Web de Microsoft.

  Vous pouvez déterminer l'état de cette option en consultant la colonne page_verify_option de l'affichage catalogue sys.databases ou la propriété IsTornPageDetectionEnabled de la fonction DATABASEPROPERTYEX.

<database_mirroring_option>::=

Contrôle la mise en miroir d'une base de données. Les valeurs spécifiées avec les options de mise en miroir de base de données s'appliquent aux deux copies de la base de données et à l'ensemble de la session de mise en miroir de base de données. Une seule <database_mirroring_option> est autorisée par instruction ALTER DATABASE : { SET PARTNER <partner_option> | SET WITNESS <witness_option>}.

Important :
Une commande SET PARTNER ou SET WITNESS peut se dérouler normalement lorsqu'elle est entrée, mais échouer ensuite.

Remarque :
Il est recommandé de configurer la mise en miroir de la base de données pendant les heures creuses car l'opération de configuration peut avoir une incidence sur les performances.

Pour plus d'informations sur la mise en miroir de base de données, consultez Mise en miroir de bases de données.

• PARTNER <partner_option>
  Contrôle les propriétés de base de données qui définissent les partenaires de basculement d'une session de mise en miroir de base de données ainsi que leur comportement. Certaines options de SET PARTNER peuvent être définies sur l'un et l'autre partenaires tandis que d'autres sont réservées au serveur principal ou au serveur miroir. Pour plus d'informations, consultez les options PARTNER individuelles décrites ci-dessous. Une clause SET PARTNER affecte les deux copies de la base de données, indépendamment du partenaire sur lequel elle est spécifiée.

  Pour exécuter une instruction SET PARTNER, l'option STATE des points de terminaison des deux partenaires doit avoir la valeur STARTED. Notez par ailleurs que l'option ROLE du point de terminaison de mise en miroir de chaque instance de serveur partenaire doit avoir la valeur PARTNER ou ALL. Pour plus d'informations sur la définition d'un point de terminaison, consultez Procédure : Créer un point de terminaison de mise en miroir pour l'authentification Windows (Transact-SQL). Pour connaître le rôle et l'état du point de terminaison de mise en miroir d'une instance de serveur, utilisez l'instruction Transact-SQL suivante sur cette instance :

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  <partner_option> ::=

  Remarque :
  Une seule <partner_option> est autorisée par clause SET PARTNER.

  • 'partner_server'
    Spécifie l'adresse réseau de serveur d'une instance de SQL Server sélectionnée comme partenaire de basculement dans une nouvelle session de mise en miroir de base de données. Chaque session exige deux partenaires : l'un d'eux commence en tant que serveur principal et l'autre en tant que serveur miroir. Les deux partenaires doivent de préférence résider sur des ordinateurs différents.

    Cette option est spécifiée une seule fois par session sur chaque partenaire. L'initialisation d'une session de mise en miroir de base de données exige deux instructions ALTER DATABASE database SET PARTNER ='partner_server'. Leur ordre est important. Commencez par vous connecter au serveur miroir et spécifiez l'instance de serveur principal en tant que partner_server (SET PARTNER ='principal_server'). Ensuite, connectez-vous au serveur principal et spécifiez l'instance de serveur miroir en tant que partner_server (SET PARTNER ='mirror_server') ; cette opération démarre une session de mise en miroir de base de données entre ces deux partenaires. Pour plus d'informations, consultez Configuration de la mise en miroir d'une base de données.

    La valeur de partner_server est une adresse réseau de serveur. La syntaxe est la suivante :

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

    où

    • <system_address> est une chaîne, telle qu'un nom de système, un nom de domaine complet ou une adresse IP, qui identifie de manière unique l'ordinateur de destination.
    • <port> est un numéro de port associé au point de terminaison de la mise en miroir de l'instance du serveur partenaire.

    Pour plus d'informations, consultez Spécification d'une adresse réseau de serveur (mise en miroir de base de données).

    L'exemple suivant illustre la clause SET PARTNER ='partner_server' :

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

    Important :

    Si une session est configurée à l'aide de l'instruction ALTER DATABASE au lieu de SQL Server Management Studio, la session est définie par défaut avec l'option de sécurité la plus élevée pour les transactions (SAFETY a la valeur FULL) et s'exécute en mode haute sécurité sans basculement automatique. Pour autoriser le basculement automatique, configurez un témoin et pour le mode hautes performances, désactivez la sécurité des transactions (SAFETY OFF).

  • FAILOVER
    Bascule manuellement le serveur principal vers le serveur miroir. Vous ne pouvez spécifier l'option FAILOVER que sur le serveur principal. Cette option est valide dans le seul cas où le paramètre SAFETY a la valeur FULL (valeur par défaut).

    L'option FAILOVER requiert master comme contexte de base de données.

    Pour plus d'informations, consultez Basculement manuel.

  • FORCE_SERVICE_ALLOW_DATA_LOSS
    Force le service de base de données vers la base de données miroir après la défaillance du serveur principal avec la base de données dans un état non synchronisé ou dans un état synchronisé si le basculement automatique ne se produit pas.

    Il est vivement conseillé de ne forcer le service que si le serveur principal n'est plus en cours d'exécution. Sinon, certains clients peuvent continuer à accéder la base de données principale d'origine au lieu de la nouvelle base de données principale.

    FORCE_SERVICE_ALLOW_DATA_LOSS est disponible uniquement sur le serveur miroir et seulement dans les circonstances suivantes :

    • Le serveur principal est arrêté.
    • WITNESS est défini sur OFF ou le témoin est connecté au serveur miroir.

    Forcez le service uniquement si vous pouvez prendre le risque de perdre des données afin de restaurer immédiatement le service à la base de données. Pour plus d'informations sur les solutions alternatives au service forcé, consultez Mise en miroir asynchrone de bases de données (mode hautes performances).

    Lorsque vous forcez le service, la session est suspendue, préservant temporairement toutes les données dans la base de données principale d'origine. Une fois que le serveur principal d'origine est en service et en mesure de communiquer avec le nouveau serveur principal, l'administrateur de base de données peut réactiver le service. Lors d'une reprise de la session, tous les enregistrements de journal non envoyés et les mises à jour correspondantes sont perdues.

    Pour plus d'informations sur les risques liés au service forcé, consultez Service forcé (avec possibilité de perte de données).

  • OFF
    Supprime une session de mise en miroir de base de données et supprime la mise en miroir de la base de données. Vous pouvez définir la valeur OFF sur l'un et l'autre partenaires. Pour plus d'informations sur l'impact de la suppression de la mise en miroir, consultez Suppression d'une mise en miroir des bases de données.
  • RESUME
    Reprend une session de mise en miroir de base de données suspendue. Vous ne pouvez spécifier l'option RESUME que sur le serveur principal.

  • SAFETY { FULL | OFF }
    Définit le niveau de sécurité des transactions. Vous ne pouvez spécifier l'option SAFETY que sur le serveur principal.

    La valeur par défaut est FULL. Avec la sécurité complète, la session de mise en miroir de base de données s'exécute de façon synchrone (mode haute sécurité). Si l'option SAFETY est désactivée (OFF), la session de mise en miroir de base de données s'exécute de façon asynchrone (mode hautes performances).

    Le comportement du mode haute sécurité dépend en partie du témoin :

    • Lorsque SAFETY a la valeur FULL et qu'un témoin est défini pour la session, la session s'exécute en mode haute sécurité avec basculement automatique. En cas de perte du serveur principal, la session bascule automatiquement si la base de données est synchronisée et que l'instance du serveur miroir et le témoin sont toujours connectés l'un à l'autre (en d'autres termes, ils ont un quorum). Pour plus d'informations, consultez Quorum : effets d'un témoin sur la disponibilité de la base de données.
      Si un témoin est défini pour la session mais qu'il est déconnecté à ce moment-là, la perte du serveur miroir provoque l'arrêt du serveur principal.
    • Lorsque SAFETY prend la valeur FULL et que le témoin est défini sur OFF, la session s'exécute en mode haute sécurité sans basculement automatique. L'arrêt éventuel de l'instance de serveur miroir n'a aucune incidence sur l'instance de serveur principal. Par contre, si l'instance de serveur principal s'arrête, le service peut être forcé (avec perte de données, le cas échéant) sur l'instance de serveur miroir.

    Si SAFETY a la valeur OFF, la session s'exécute en mode hautes performances et les basculements manuel et automatique ne sont pas pris en charge. Toutefois, les problèmes survenant sur le miroir n'ont pas d'incidence sur l'instance de serveur principale qui, si elle s'arrête, peut être, si nécessaire, relayée par l'instance de serveur miroir dont vous forcez le service (avec une perte possible des données). Pour cela, WITNESS doit être défini sur OFF, ou le témoin être connecté à ce moment-là au miroir. Pour plus d'informations sur le service forcé, consultez « FORCE_SERVICE_ALLOW_DATA_LOSS » plus haut dans cette section.

    Important :
    Le mode hautes performances n'a pas été prévu pour utiliser un témoin. Toutefois, il est vivement recommandé de vérifier que WITNESS est défini à OFF chaque fois que l'option SAFETY est désactivée (OFF).

    Pour plus d'informations, consultez Paramètres Transact-SQL et modes d'opération de mise en miroir de bases de données.

  • SUSPEND
    Interrompt une session de mise en miroir de base de données.

    Vous pouvez définir la valeur SUSPEND sur l'un et l'autre partenaires.

  • TIMEOUT integer
    Spécifie le délai d'attente en secondes. Le délai d'attente est la durée maximale pendant laquelle une instance de serveur attend de recevoir un message PING d'une autre instance de la session de mise en miroir avant de considérer que cette dernière est déconnectée.

    Vous ne pouvez spécifier l'option TIMEOUT que sur le serveur principal. Si vous ne précisez pas de valeur, le délai défini par défaut est 10 secondes. Si vous spécifiez 5 ou une valeur supérieure, le délai d'attente correspond au nombre de secondes indiqué. Si vous spécifiez une valeur comprise entre 0 et 4 secondes, le délai d'attente est automatiquement de 5 secondes.

    Important :
    Le temps d'attente recommandé est de 10 secondes minimum. En définissant une valeur inférieure à 10 secondes, vous créez la possibilité qu'un système surchargé soit à court de PING et qu'il déclare à tort une défaillance.

    Pour plus d'informations, consultez Défaillances possibles pendant la mise en miroir d'une base de données.

• WITNESS <witness_option>
  Contrôle les propriétés de base de données qui définissent un témoin de mise en miroir de base de données. Une clause SET WITNESS affecte les deux copies de la base de données mais vous ne pouvez la spécifier que sur le serveur principal. Si un témoin est défini sur une session, le quorum est obligatoire pour servir la base de données sans tenir compte de l'option SAFETY. Pour plus d'informations, consultez Quorum : effets d'un témoin sur la disponibilité de la base de données.

  Les partenaires de basculement et témoins doivent de préférence résider sur des ordinateurs différents. Pour plus d'informations sur le témoin, consultez Témoin de mise en miroir de base de données. Pour plus d'informations sur le basculement automatique, consultez Basculement automatique.

  Pour exécuter une instruction SET WITNESS, l'option STATE des points de terminaison des instances de serveur principal et de serveur témoin doit avoir la valeur STARTED. Notez par ailleurs que l'option ROLE du point de terminaison de mise en miroir d'une instance de serveur témoin doit avoir la valeur WITNESS ou ALL. Pour plus d'informations sur la définition d'un point de terminaison, consultez Point de terminaison de mise en miroir de bases de données.

  Pour connaître le rôle et l'état du point de terminaison de mise en miroir d'une instance de serveur, utilisez l'instruction Transact-SQL suivante sur cette instance :

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  Remarque :
  Les propriétés de base de données ne peuvent pas être définies sur le témoin.

  <witness_option> ::=

  Remarque :
  Une seule <witness_option> est autorisée par clause SET WITNESS.

  • 'witness_server'
    Spécifie une instance du moteur de base de données qui doit servir de serveur témoin pour une session de mise en miroir de base de données. Vous ne pouvez spécifier d'instructions SET WITNESS que sur le serveur principal.

    Dans une instruction SET WITNESS ='witness_server', la syntaxe de witness_server est identique à celle de partner_server.

  • OFF
    Supprime le témoin d'une session de mise en miroir de bases de données. Si le témoin a la valeur OFF, le basculement automatique est désactivé. Lorsque la base de données est configurée avec l'option FULL SAFETY et le témoin avec la valeur OFF, une défaillance du serveur miroir conduit le serveur principal à rendre la base de données inaccessible.

<service_broker_option>::=

Contrôle les options de Service Broker

• ENABLE_BROKER
  Spécifie que Service Broker est activé pour la base de données spécifiée. L'indicateur is_broker_enabled a la valeur True dans l'affichage catalogue sys.databases et la remise des messages est démarrée.

  Remarque :
  L'activation de SQL Server Service Broker dans une base de données nécessite un verrou de base de données. Pour activer Service Broker dans la base de données msdb, arrêtez tout d'abord l'Agent SQL Server, afin que Service Broker puisse obtenir le verrou nécessaire.

• DISABLE_BROKER
  Spécifie que Service Broker est désactivé pour la base de données spécifiée. L'indicateur is_broker_enabled a la valeur False dans l'affichage catalogue sys.databases et la remise des messages est arrêtée.

• NEW_BROKER
  Spécifie que la base de données doit recevoir un nouvel identificateur Service Broker. Dans la mesure où la base de données est considérée comme un nouveau Service Broker, toutes les conversations existantes dans la base de données sont immédiatement supprimées sans générer de message de fin de dialogue.

• ERROR_BROKER_CONVERSATIONS
  Indique que les conversations dans la base de données doivent recevoir un message d'erreur lorsque la base de données est attachée. De cette façon, vos applications peuvent effectuer un nettoyage normal des conversations existantes.

<date_correlation_optimization_option> ::=

Contrôle l'option date_correlation_optimization.

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    SQL Server conserve les statistiques de corrélation entre deux tables quelconques de la base de données liées par une contrainte FOREIGN KEY et possédant des colonnes datetime. Pour plus d'informations, consultez Optimisation des requêtes qui ont accès à des colonnes datetime corrélées.
  • OFF
    Les statistiques de corrélation ne sont pas conservées.

  Pour définir DATE_CORRELATION_OPTIMIZATION à ON, il ne doit exister aucune connexion active à la base de données, à l'exception de celle qui exécute l'instruction ALTER DATABASE. Ensuite, différentes connexions peuvent être prises en charge.

  Vous pouvez déterminer la valeur actuelle de cette option en consultant la colonne is_date_correlation_on de l'affichage catalogue sys.databases.

<parameterization_option> ::=

Contrôle de l'option de paramétrage.

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    Les requêtes sont paramétrées en fonction du comportement par défaut de la base de données. Pour plus d'informations, consultez Paramétrage simple.
  • FORCED
    SQL Server paramètre toutes les requêtes de la base de données. Pour plus d'informations, consultez Paramétrage forcé.

  Vous pouvez déterminer la valeur actuelle de cette option en consultant la colonne is_parameterization_forced de l'affichage catalogue sys.databases.

<snapshot_option>::=

Détermine le niveau d'isolement des transactions.

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    Les transactions peuvent spécifier le niveau d'isolement des transactions SNAPSHOT. Quand une transaction s'exécute au niveau d'isolement SNAPSHOT, toutes les instructions voient une capture instantanée des données si celle-ci existe au début de la transaction. Si une transaction exécutée au niveau d'isolement SNAPSHOT accède à des données dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit être affectée de la valeur ON dans toutes les bases de données ou chaque instruction de la transaction doit utiliser des indicateurs de verrouillage sur toute référence d'une clause FROM à une table de la base de données dont l'option ALLOW_SNAPSHOT_ISOLATION a la valeur OFF.
  • OFF
    Les transactions ne peuvent pas spécifier le niveau d'isolement des transactions SNAPSHOT.

  Lorsque vous modifiez l'état de ALLOW_SNAPSHOT_ISOLATION (de ON à OFF ou inversement), ALTER DATABASE ne rend pas le contrôle à l'appelant tant que toutes les transactions existantes dans la base de données n'ont pas été validées. Si la base de données présente déjà l'état spécifié dans l'instruction ALTER DATABASE, le contrôle est immédiatement rendu à l'appelant. Si le retour de l'instruction ALTER DATABASE dure longtemps, utilisez sys.dm_tran_active_snapshot_database_transactions pour déterminer s'il existe des transactions longues. Si l'instruction ALTER DATABASE est annulée, la base de données conserve l'état qu'elle présentait au démarrage de ALTER DATABASE. L'affichage catalogue sys.databases indique l'état des transactions d'isolement de capture instantanée dans la base de données. Si snapshot_isolation_state_desc a pour valeur IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF marque une pause de six secondes puis renouvelle l'opération.

  Vous ne pouvez pas modifier l'état de ALLOW_SNAPSHOT_ISOLATION si la base de données est hors connexion (OFFLINE).

  Si vous configurez ALLOW_SNAPSHOT_ISOLATION dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé si la base de données devient par la suite accessible en lecture et en écriture (READ_WRITE).

  Vous pouvez modifier les paramètres de ALLOW_SNAPSHOT_ISOLATION pour les bases de données master, model, msdb et tempdb. Si vous modifiez le paramètre pour la base de données tempdb, il est conservé à chaque arrêt et redémarrage de l'instance du moteur de base de données. Si vous modifiez le paramètre pour la base de données model, il devient le paramètre par défaut pour toutes les nouvelles bases de données créées à l'exception de tempdb.

  Cette option a la valeur ON par défaut dans les bases de données master et msdb.

  Vous pouvez déterminer la valeur actuelle de cette option en consultant la colonne snapshot_isolation_state de l'affichage catalogue sys.databases.

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    Les transactions spécifiant le niveau d'isolement de lecture validée utilisent le contrôle de version de ligne au lieu du verrouillage. Lorsqu'une transaction est exécutée au niveau d'isolement de lecture validée, toutes les instructions voient une capture instantanée des données telles qu'elles se présentent au début de l'instruction.
  • OFF
    Les transactions spécifiant le niveau d'isolement READ_COMMITTED utilisent le verrouillage.

  Pour activer (ON) ou désactiver (OFF) READ_COMMITTED_SNAPSHOT, il ne doit exister aucune connexion active à la base de données à l'exception de la connexion exécutant la commande ALTER DATABASE. Toutefois, il n'est pas nécessaire que la base de données soit en mode mono-utilisateur. Vous ne pouvez pas modifier l'état de cette option si la base de données est hors connexion (OFFLINE).

  Si vous configurez READ_COMMITTED_SNAPSHOT dans une base de données en lecture seule (READ_ONLY), le paramètre est conservé lorsque la base de données devient par la suite accessible en lecture et en écriture (READ_WRITE).

  Il n'est pas possible d'affecter la valeur ON à READ_COMMITTED_SNAPSHOT pour les bases de données système master, tempdb ou msdb. Si vous modifiez le paramètre pour la base de données model, il devient le paramètre par défaut pour toutes les nouvelles bases de données créées à l'exception de tempdb.

  Vous pouvez déterminer la valeur actuelle de cette option en consultant la colonne is_read_committed_snapshot_on de l'affichage catalogue sys.databases.

WITH <termination>::=

Spécifie le(s) cas où une transaction incomplète doit être annulée lors d'un changement d'état de la base de données. Lorsque la clause de fin est omise, l'instruction ALTER DATABASE attend indéfiniment s'il existe un verrou quelconque sur la base de données. Une seule clause de fin peut être spécifiée, à la suite des clauses SET.

Remarque :
Toutes les options de base de données n'utilisent pas la clause WITH <termination>. Pour plus d'informations, consultez le tableau du paragraphe « Configuration des options » de la section Notes.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  Indique si l'annulation intervient après le nombre de secondes spécifié ou immédiatement.

• NO_WAIT
  Indique que la modification souhaitée de l'option ou de l'état de la base de données échoue si sa réalisation immédiate suppose la validation ou l'annulation des transactions de leur propre fait.

Notes

Pour supprimer une base de données, utilisez DROP DATABASE.

Pour renommer une base de données, utilisez l'option MODIFY NAME = new_database_name avec ALTER DATABASE.

Pour diminuer la taille d'une base de données, utilisez DBCC SHRINKDATABASE.

Vous ne pouvez pas ajouter ou supprimer de fichier tant qu'une instruction BACKUP est en cours d'exécution.

Un maximum de 32 767 fichiers et 32 767 groupes de fichiers peut être spécifié pour chaque base de données.

L'instruction ALTER DATABASE doit s'exécuter en mode de validation automatique (mode de gestion des transactions par défaut) et n'est pas autorisée dans une transaction explicite ou implicite. Pour plus d'informations, consultez Transactions en mode autocommit.

Dans SQL Server 2005, l'état d'un fichier de base de données (par exemple en ligne ou hors connexion), est préservé indépendamment de l'état de la base de données. Pour plus d'informations, consultez États des fichiers. L'état des fichiers dans un groupe de fichiers détermine la disponibilité de tout le groupe de fichiers. Pour qu'un groupe de fichiers soit disponible, tous les fichiers du groupe doivent être en ligne. Si un groupe de fichiers est hors connexion, toute tentative d'accès au groupe par une instruction SQL échoue avec une erreur. Lorsque vous créez des plans de requêtes pour les instructions SELECT, l'optimiseur de requête évite les index non-cluster et les vues indexées qui résident dans les groupes de fichiers hors connexion. Cela permet aux instructions de s'exécuter correctement. Toutefois, si le groupe de fichiers hors connexion contient le segment de mémoire ou l'index cluster de la table cible, les instructions SELECT échouent. Par ailleurs, toute instruction INSERT, UPDATE ou DELETE qui modifie une table avec un index dans un groupe de fichiers hors connexion échoue.

Lorsqu'une base de données est dans l'état RESTORING, la plupart des instructions ALTER DATABASE échouent. La seule exception concerne la configuration des options de mise en miroir de base de données. Une base de données peut être dans l'état RESTORING durant une opération de restauration active, ou lorsqu'une opération de restauration d'un fichier de base de données ou d'un fichier journal échoue car un fichier de sauvegarde est corrompu. Pour plus d'informations, consultez Réponse aux erreurs de restauration SQL Server provoquées par des sauvegardes endommagées.

Configuration des options

Pour extraire les paramètres actuels des options de base de données, utilisez l'affichage catalogue sys.databases ou DATABASEPROPERTYEX. Pour obtenir une liste des valeurs par défaut assignées à la base de données lors de sa création, consultez Définition des options de base de données.

Dès qu'une option de base de données est définie, la modification prend immédiatement effet.

Pour modifier les valeurs par défaut de l'une des options de base de données afin qu'elles s'appliquent à toutes les nouvelles bases de données créées, modifiez l'option de base de données appropriée dans la base de données model.

Toutes les options de base de données n'utilisent pas la clause WITH <termination> et ne peuvent pas être combinées avec d'autres options. Le tableau suivant répertorie ces options ainsi que l'état de l'option et d'arrêt.

Catégorie d'options	Peut être spécifiée avec d'autres options	Peut utiliser la clause WITH <termination>
<db_state_option>	Oui	Oui
<db_user_access_option>	Oui	Oui
db_update_option>	Oui	Oui
<external_access_option>	Oui	Non
<cursor_option>	Oui	Non
<auto_option>	Oui	Non
<sql_option>	Oui	Non
<recovery_option>	Oui	Non
<database_mirroring_option>	Non	Non
ALLOW_SNAPSHOT_ISOLATION	Non	Non
READ_COMMITTED_SNAPSHOT	Non	Oui
<service_broker_option>	Oui	Non
DATE_CORRELATION_OPTIMIZATION	Oui	Oui
<parameterization_option>	Oui	Oui

Le cache de plan pour l'instance de SQL Server est effacé en définissant une des options suivantes :

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

Cette opération entraîne la recompilation de tous les plans d'exécution ultérieurs et peut entraîner une baisse temporaire et brutale des performances des requêtes. Dans SQL Server 2005 Service Pack 2, pour chaque mémoire cache effacée du cache du plan, le journal des erreurs de SQL Server contient le message d'information suivant : SQL Server a rencontré %d occurrence(s) de vidages de mémoire cache pour la mémoire cache '%s' (partie du cache du plan) en raison d'opérations de maintenance ou de reconfiguration de base de données. Ce message est enregistré toutes les cinq minutes si le cache est vidé au cours de cet intervalle.

Déplacement de fichiers

Dans SQL Server 2005, vous pouvez déplacer des données système ou définies par l'utilisateur ainsi que des fichiers journaux en spécifiant le nouvel emplacement dans FILENAME. Cela peut être utile dans les cas suivants :

• Récupération après défaillance. Par exemple, la base de données est en mode suspect ou arrêtée à cause d'une défaillance matérielle.
• Déplacement prévu.
• Déplacement en vue d'une maintenance de disque planifiée.

Pour plus d'informations, consultez Déplacement des fichiers de bases de données.

Initialisation des fichiers

Par défaut, les fichiers de données et journaux sont initialisés en remplissant les fichiers avec des zéros lorsque vous effectuez l'une des opérations suivantes :

• Créer une base de données.
• Ajouter des fichiers à une base de données existante.
• Augmenter la taille d'un fichier existant.
• Restaurer une base de données ou un groupe de fichiers.

Dans SQL Server 2005, les fichiers de données peuvent être initialisés instantanément. Dès lors, l'exécution de ces opérations de fichiers est très rapide. Pour plus d'informations, consultez Initialisation des fichiers de base de données.

Modification du classement de la base de données

Avant d'appliquer un classement différent à une base de données, vérifiez que les conditions suivantes sont réunies :

1. Vous êtes le seul utilisateur actuel de la base de données.
2. Aucun objet lié à un schéma ne dépend du classement de la base de données.
   Si les objets suivants, lesquels dépendent du classement de base de données, existent dans la base de données, l'instruction ALTER DATABASE database_name COLLATE échoue. SQL Server retourne un message d'erreur pour chaque objet bloquant l'action ALTER :
   • Fonctions et vues définies par l'utilisateur, créées à l'aide de SCHEMABINDING.
   • Colonnes calculées.
   • Contraintes CHECK.
   • Fonctions comportant des valeurs de tables qui retournent des tables avec des colonnes de caractères dont les classements sont hérités du classement par défaut de la base de données.
3. La modification du classement de la base de données ne crée pas de noms système en double parmi les noms système des objets de la base de données.
   Les espaces de noms suivants peuvent provoquer l'échec d'une modification du classement de la base de données si les doublons de noms résultent du classement modifié :
   • noms d'objets (procédure, table, déclencheur ou vue) ;
   • noms de schéma ;
   • entités de sécurité (groupe, rôle ou utilisateur) ;
   • noms de type scalaire (types système et définis par l'utilisateur) ;
   • noms de catalogues de texte intégral ;
   • noms de colonnes ou de paramètres dans un objet ;
   • noms d'index dans une table.
     Les doublons de noms résultant du nouveau classement entraînent l'échec de l'action de modification, et SQL Server retourne un message d'erreur spécifiant l'espace de noms dans lequel le doublon a été trouvé.

Affichage des informations de bases de données

Vous pouvez utiliser les affichages catalogue, les fonctions système et les procédures stockées du système pour retourner des informations sur les bases de données, les fichiers et les groupes de fichiers. Pour plus d'informations, consultez Affichage des métadonnées de la base de données.

Autorisations

Exige l'autorisation ALTER sur la base de données.

Exemples

A. Ajout d'un groupe de fichiers à une base de données

L'exemple suivant ajoute un fichier de données de 5 Mo à la base de données 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. Ajout d'un groupe de deux fichiers à une base de données

L'exemple suivant crée le groupe de fichiers Test1FG1 dans la base de données AdventureWorks et ajoute deux fichiers de 5 Mo au groupe de fichiers.

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. Ajout de deux fichiers journaux à une base de données

L'exemple suivant ajoute deux fichiers journaux de 5 Mo à la base de données 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. Suppression d'un fichier d'une base de données

L'exemple suivant supprime l'un des fichiers ajoutés dans l'exemple B.

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

E. Modification d'un fichier

L'exemple suivant augmente la taille de l'un des fichiers ajoutés dans l'exemple B.

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

F. Déplacement d'un fichier vers un nouvel emplacement

L'exemple suivant déplace le fichier Test1dat2 créé dans l'exemple A vers un nouveau répertoire.

Remarque :
Vous devez déplacer physiquement le fichier vers le nouveau répertoire avant d'exécuter cet exemple. Après quoi, arrêtez et démarrez l'instance de SQL Server ou mettez la base de données AdventureWorks hors connexion (OFFLINE) puis remettez-la en ligne (ONLINE) pour implémenter la modification.

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

G. Déplacement de tempdb vers un nouvel emplacement

L'exemple suivant déplace tempdb de son emplacement actuel vers un autre emplacement du disque. Étant donné que tempdb est recréée à chaque démarrage du service MSSQLSERVER, il n'est pas nécessaire de déplacer physiquement les fichiers de données et les fichiers journaux. Les fichiers sont créés lors du redémarrage du service à l'étape 3. Jusqu'au redémarrage du service, tempdb continue à fonctionner à son emplacement existant.

1. Déterminez les noms de fichiers logiques de la base de données tempdb et leur emplacement actuel sur le disque.

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

2. Modifiez l'emplacement de chaque fichier à l'aide de 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. Arrêtez et redémarrez l'instance de SQL Server.

4. Vérifiez que la modification des fichiers a bien eu lieu.

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

5. Supprimez les fichiers empdb.mdf et templog.ldf de leur emplacement d'origine.

H. Configuration d'un groupe de fichiers comme groupe par défaut

L'exemple suivant configure le groupe de fichiers Test1FG1 créé dans l'exemple B comme groupe de fichiers par défaut. Ensuite, le groupe de fichiers par défaut est reconfiguré en groupe de fichiers PRIMARY. Notez que PRIMARY doit être délimité par des crochets ou des guillemets.

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

I. Définition des options d'une base de données

L'exemple suivant définit les options de mode de récupération et de vérification de pages de données pour la base de données exemple AdventureWorks .

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

J. Configuration de la base de données avec le paramètre READ_ONLY

Pour modifier l'état d'une base de données ou d'un groupe de fichiers en READ_ONLY ou READ_WRITE, vous avez besoin d'un accès exclusif à la base de données. L'exemple suivant bascule la base de données en mode SINGLE_USER pour obtenir l'accès exclusif. L'exemple affecte ensuite à la base de données AdventureWorks l'état READ_ONLY et rend à tous les utilisateurs l'accès à la base de données.

Remarque :
Cet exemple utilise l'option de fin WITH ROLLBACK IMMEDIATE dans la première instruction ALTER DATABASE. Toutes les transactions incomplètes seront annulées et les autres connexions à la base de données exemple AdventureWorks immédiatement déconnectées.

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. Activation du niveau d'isolement de capture instantanée sur une base de données

L'exemple suivant active l'option d'infrastructure d'isolation de capture instantanée de la base de données 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

Le jeu de résultats montre que l'infrastructure d'isolation de capture instantanée est activée.

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

L. Création d'une session de mise en miroir de base de données avec un témoin

Pour configurer la mise en miroir de base de données avec un témoin, vous devez configurer la sécurité, préparer la base de données miroir et également utiliser ALTER DATABASE pour définir les partenaires. Pour obtenir un exemple du processus de configuration complet, consultez Configuration de la mise en miroir d'une base de données.

M. Basculement manuel d'une session de mise en miroir de base de données

Le basculement manuel peut être initialisé par l'un ou l'autre des partenaires de mise en miroir de base de données. Avant de basculer, vérifiez que le serveur considéré comme serveur principal est effectivement le serveur principal. Par exemple, pour la base de données AdventureWorks, sur l'instance de serveur représentant, selon vous, le serveur principal, exécutez la requête suivante :

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

Si l'instance de serveur est effectivement le serveur principal, la valeur de mirroring_role_desc est Principal. Si cette instance de serveur est le serveur miroir, l'instruction SELECT retourne Mirror.

L'exemple suivant suppose que le serveur est l'actuel serveur principal.

1. Basculez manuellement vers le partenaire de mise en miroir de base de données :

   ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
   GO
   

2. Pour vérifier les résultats du basculement sur le nouveau miroir, exécutez la requête suivante :

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

   La valeur actuelle de mirroring_role_desc est désormais Mirror.

Voir aussi

Référence

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)

Autres ressources

Activation des niveaux d'isolement selon le versioning de ligne
Bases de données système

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
12 décembre 2006	Nouveau contenu : Ajout d'informations sur les options qui effacent le cache de plan à la section « Configuration des options » dans Notes et la définition de AUTO_CLOSE. Ajout des informations sur l'utilisation de l'option AUTO_UPDATE_STATISTICS_ASYNC en mode mono-utilisateur dans la définition de SINGLE_USER.
14 avril 2006	Contenu modifié : Mise à jour de la description de l'option FAILOVER pour indiquer qu'elle requiert master comme contexte de base de données. Ajout d'une remarque Important à l'introduction de la section « <database_mirroring_option> ». Sous l'argument ALLOW_SNAPSHOT_ISOLATION, mise à jour des informations sur la détermination de l'état des transactions d'isolement de capture instantanée dans la base de données et sur l'impact de l'état sur le comportement de SQL Server lorsque cette option est modifiée. Correction de la définition de DATE_CORRELATION_OPTIMIZATION.
5 décembre 2005	Nouveau contenu : Une nouvelle indication figure dans la définition de ENABLE_BROKER. Une recommandation a été ajoutée pour la modification de l'option PAGE_VERIFY dans les bases de données mises à niveau. Contenu modifié : Suppression de l'option SUPPLEMENTAL_LOGGING. L'exemple G a été corrigé. Les informations concernant l'option TRUSTWORTHY dans les bases de données système ont été corrigées. La définition de READ_COMMITTED_SNAPSHOT a été mise à jour ; elle indique que le mode mono-utilisateur n'est pas nécessaire. La définition de <db_state_option> a été mise à jour ; elle indique qu'il est impossible de définir les options OFFLINE, ONLINE et EMERGENCY lorsque la base de données est dans l'état RESTORING.