Merge pull request #16756 from pmasl/patch-867

PRMerger7 · web-flow · commit 58bdbe77f292 · 2020-08-23T19:05:30.000-07:00
Update alter-database-transact-sql-set-options.md
diff --git a/docs/t-sql/statements/alter-database-transact-sql-set-options.md b/docs/t-sql/statements/alter-database-transact-sql-set-options.md
@@ -188,11 +188,13 @@ SET
   | TRANSFORM_NOISE_WORDS = { OFF | ON }
   | TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }
 }
+
 <FILESTREAM_option> ::=
 {
     NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL
   | DIRECTORY_NAME = <directory_name>
 }
+
 <HADR_options> ::=
     ALTER DATABASE SET HADR
 
@@ -206,7 +208,7 @@ SET
 {
     QUERY_STORE
     {
- = OFF
+          = OFF [ FORCED ] 
         | = ON [ ( <query_store_option_list> [,...n] ) ]
         | ( < query_store_option_list> [,...n] )
         | CLEAR [ ALL ]
@@ -229,7 +231,7 @@ SET
 
 <query_capture_policy_option_list> :: =
 {
-    STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
+      STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
     | EXECUTION_COUNT = number
     | TOTAL_COMPILE_CPU_TIME_MS = number
     | TOTAL_EXECUTION_CPU_TIME_MS = number
@@ -247,11 +249,12 @@ SET
     REMOTE_DATA_ARCHIVE =
     {
         ON ( SERVER = <server_name> ,
-{CREDENTIAL = <db_scoped_credential_name>
-   | FEDERATED_SERVICE_ACCOUNT = ON | OFF
-}
-      )
-      | OFF
+             { 
+                  CREDENTIAL = <db_scoped_credential_name>
+                  | FEDERATED_SERVICE_ACCOUNT = ON | OFF
+             }
+        )
+        | OFF
     }
 }
 
@@ -267,8 +270,8 @@ SET
 <snapshot_option> ::=
 {
     ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
-  | READ_COMMITTED_SNAPSHOT {ON | OFF }
-  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = {ON | OFF }
+  | READ_COMMITTED_SNAPSHOT { ON | OFF }
+  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = { ON | OFF }
 }
 <sql_option> ::=
 {
@@ -293,7 +296,9 @@ SET
   | ROLLBACK IMMEDIATE
   | NO_WAIT
 }
-<temporal_history_retention>::=TEMPORAL_HISTORY_RETENTION { ON | OFF }
+
+<temporal_history_retention> ::=
+    TEMPORAL_HISTORY_RETENTION { ON | OFF }
 ```
 
 ## Arguments
@@ -331,9 +336,9 @@ The AUTO_CLOSE option is useful for desktop databases because it allows for data
 >
 > When AUTO_CLOSE is set to ON, some columns in the [sys.databases](../../relational-databases/system-catalog-views/sys-databases-transact-sql.md) catalog view and the [DATABASEPROPERTYEX](../../t-sql/functions/databasepropertyex-transact-sql.md) function will return NULL because the database is unavailable to retrieve the data. To resolve this issue, run a USE statement to open the database.
 >
-> Database mirroring requires AUTO_CLOSE OFF.
+> Database mirroring requires AUTO_CLOSE set to OFF.
 
-When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. Starting with [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] Service Pack 2, for each cleared cache store in the plan cache, the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log contains the following informational message: `SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations`. This message is logged every five minutes as long as the cache is flushed within that time interval.
+When the database is set to `AUTOCLOSE = ON`, an operation that initiates an automatic database shutdown clears the plan cache for the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. Starting with [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] Service Pack 2, for each cleared cache store in the plan cache, the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log contains the following informational message: `SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations`. This message is logged every five minutes as long as the cache is flushed within that time interval.
 
 <a name="auto_create_statistics"></a> AUTO_CREATE_STATISTICS { **ON** | OFF }     
 ON     

Original file line number	Diff line number	Diff line change
`@@ -188,11 +188,13 @@ SET`
`188`	`188`	`\| TRANSFORM_NOISE_WORDS = { OFF \| ON }`
`189`	`189`	`\| TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }`
`190`	`190`	`}`
	`191`	`+`
`191`	`192`	`<FILESTREAM_option> ::=`
`192`	`193`	`{`
`193`	`194`	`NON_TRANSACTED_ACCESS = { OFF \| READ_ONLY \| FULL`
`194`	`195`	`\| DIRECTORY_NAME = <directory_name>`
`195`	`196`	`}`
	`197`	`+`
`196`	`198`	`<HADR_options> ::=`
`197`	`199`	`ALTER DATABASE SET HADR`
`198`	`200`
`@@ -206,7 +208,7 @@ SET`
`206`	`208`	`{`
`207`	`209`	`QUERY_STORE`
`208`	`210`	`{`
`209`		`- = OFF`
	`211`	`+ = OFF [ FORCED ]`
`210`	`212`	`\| = ON [ ( <query_store_option_list> [,...n] ) ]`
`211`	`213`	`\| ( < query_store_option_list> [,...n] )`
`212`	`214`	`\| CLEAR [ ALL ]`
`@@ -229,7 +231,7 @@ SET`
`229`	`231`
`230`	`232`	`<query_capture_policy_option_list> :: =`
`231`	`233`	`{`
`232`		`- STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS \| HOURS }`
	`234`	`+ STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS \| HOURS }`
`233`	`235`	`\| EXECUTION_COUNT = number`
`234`	`236`	`\| TOTAL_COMPILE_CPU_TIME_MS = number`
`235`	`237`	`\| TOTAL_EXECUTION_CPU_TIME_MS = number`
`@@ -247,11 +249,12 @@ SET`
`247`	`249`	`REMOTE_DATA_ARCHIVE =`
`248`	`250`	`{`
`249`	`251`	`ON ( SERVER = <server_name> ,`
`250`		`-{CREDENTIAL = <db_scoped_credential_name>`
`251`		`- \| FEDERATED_SERVICE_ACCOUNT = ON \| OFF`
`252`		`-}`
`253`		`- )`
`254`		`- \| OFF`
	`252`	`+ {`
	`253`	`+ CREDENTIAL = <db_scoped_credential_name>`
	`254`	`+ \| FEDERATED_SERVICE_ACCOUNT = ON \| OFF`
	`255`	`+ }`
	`256`	`+ )`
	`257`	`+ \| OFF`
`255`	`258`	`}`
`256`	`259`	`}`
`257`	`260`
`@@ -267,8 +270,8 @@ SET`
`267`	`270`	`<snapshot_option> ::=`
`268`	`271`	`{`
`269`	`272`	`ALLOW_SNAPSHOT_ISOLATION { ON \| OFF }`
`270`		`- \| READ_COMMITTED_SNAPSHOT {ON \| OFF }`
`271`		`- \| MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = {ON \| OFF }`
	`273`	`+ \| READ_COMMITTED_SNAPSHOT { ON \| OFF }`
	`274`	`+ \| MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = { ON \| OFF }`
`272`	`275`	`}`
`273`	`276`	`<sql_option> ::=`
`274`	`277`	`{`
`@@ -293,7 +296,9 @@ SET`
`293`	`296`	`\| ROLLBACK IMMEDIATE`
`294`	`297`	`\| NO_WAIT`
`295`	`298`	`}`
`296`		`-<temporal_history_retention>::=TEMPORAL_HISTORY_RETENTION { ON \| OFF }`
	`299`	`+`
	`300`	`+<temporal_history_retention> ::=`
	`301`	`+ TEMPORAL_HISTORY_RETENTION { ON \| OFF }`
`297`	`302`	```
`298`	`303`
`299`	`304`	`## Arguments`
`@@ -331,9 +336,9 @@ The AUTO_CLOSE option is useful for desktop databases because it allows for data`
`331`	`336`	`>`
`332`	`337`	`> When AUTO_CLOSE is set to ON, some columns in the [sys.databases](../../relational-databases/system-catalog-views/sys-databases-transact-sql.md) catalog view and the [DATABASEPROPERTYEX](../../t-sql/functions/databasepropertyex-transact-sql.md) function will return NULL because the database is unavailable to retrieve the data. To resolve this issue, run a USE statement to open the database.`
`333`	`338`	`>`
`334`		`-> Database mirroring requires AUTO_CLOSE OFF.`
	`339`	`+> Database mirroring requires AUTO_CLOSE set to OFF.`
`335`	`340`
`336`		-When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. Starting with [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] Service Pack 2, for each cleared cache store in the plan cache, the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log contains the following informational message: `SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations`. This message is logged every five minutes as long as the cache is flushed within that time interval.
	`341`	+When the database is set to `AUTOCLOSE = ON`, an operation that initiates an automatic database shutdown clears the plan cache for the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. Starting with [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] Service Pack 2, for each cleared cache store in the plan cache, the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log contains the following informational message: `SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations`. This message is logged every five minutes as long as the cache is flushed within that time interval.
`337`	`342`
`338`	`343`	`<a name="auto_create_statistics"></a> AUTO_CREATE_STATISTICS { ON \| OFF }`
`339`	`344`	`ON`