Online Documentation for SQL Administrator for SQL Server

Miscellaneous

The Miscellaneous section of the Server Properties dialog allows you to configure the instance of Microsoft® SQL Server by configuring miscellaneous server settings.

Allow a trigger to fire another one (nested triggers)

If this option is selected then nested triggers will be enabled. Triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.

Allow a server trigger to fire itself (recursive server triggers)

Check this option to enable for an AFTER trigger to call itself recursively.

Optimize plan cache for ad hoc workloads

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. If this option is checked, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Scan for automatically run stored procedures at server startup

Use this option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time. If this option is checked, SQL Server scans for and runs all automatically run stored procedures defined on the server.

Blocked process threshold in seconds to generate report

Use this option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced.

Cursor number of rows threshold to run asynchronously

Use this field to specify the number of rows in the cursor set at which cursor keysets (the set of keys that identify the rows in the cursor) are generated asynchronously. When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.

If set to -1, all keysets are generated synchronously; this benefits small cursor sets. If set to 0, all cursor keysets are generated asynchronously. With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set.

Maximum size of text data to replicate in bytes

Use this option to specify the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536. A value of -1 indicates no limit, other than the limit imposed by the data type. This option applies to transactional replication and Change Data Capture.

Cutoff year for interpreting two-digit years as four-digit years

Use this option to specify an integer less than 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.

Use the Default language for created logins drop-down list to select the default language for all newly created user logins.

Filestream access level

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

Disable FILESTREAM support for this instance

Check this option to disable FILESTREAM feature for handling unstructured data.

Enable FILESTREAM for Transact-SQL access

Enable this option to manage FILESTREAM data with Trancast-SQL statements (insert, update, query, search, and back up).

Enable FILESTREAM for Transact-SQL and Win32 streaming access

Win32 file system interfaces provide I/O streaming access to the data. You can use Win32 to read and write data to a FILESTREAM BLOB.