Troubleshooting SQL Server Objects

• Troubleshoot failed object creation.

Most problems associated with creating and/or accessing objects can be resolved through setting appropriate object access permissions. However, other elements that can hamper the creation or use of objects include (but are not limited to) the following:

• Backup and restore operations

• Other users' operations locking parts of the system

• Metadata corruption

• Hardware or resource problems

• Network connectivity

• Configuration settings

• Operating system

• Metadata corruption

A good starting point from which to resolve most problems is the wealth of feedback SQL Server gives in the form of OS Application Event Log, SQL Server Logs, and the Current Activity Window, as well as the permission properties of the users, roles, and objects.

To create a database, you need to be a member of System Administrators or Database Creators server roles or have the Create Database permission. To create objects within a database, you must be a member of db_owner or db_ddladmin database roles or have the specific permission to create the object as given by statement-level permissions. Statement-level permissions can be found on the Permissions tab of the database Properties dialog box.

As databases and their objects are created, the system uses the default filegroup for the physical storage of the element. It is a good practice to create a storage group for user objects and make that the default filegroup. This way, as the user creates objects, those objects don't compete for storage with other data.

If a user lacks the permission to create or alter an object, an alternative is available that grants the user creation permission without giving the user too much control over the environment. An Application role that has permission to work with objects in this manner can be assigned to a stored procedure that creates the objects for the user. When the user executes the procedure, objects can be created or altered in a controlled manner.

Setting Server Configuration Options

Standard configuration settings are available through the Server Properties dialog box in the Enterprise Manager or can be accessed using the sp_configure stored procedure. Some of the more advanced options require that you enable Show Advanced Options.

 sp_configure 'show advanced options', 1 reconfigure 

Affinity Mask (Advanced)

Use this option in systems that have four or more processors. It increases performance when the system is under a heavy workload. You can specify which processors Microsoft SQL Server is to use. You can exclude SQL Server activity from processors that have been given specific workload assignments by the Windows NT 4.0 or Windows 2000 operating system.

Allow Updates

This option is used to allow the direct alteration of system tables. When Allow Updates is set to 1 , any user with appropriate permissions can either update system tables directly with ad-hoc updates or create stored procedures that update system tables.

AWE Enabled (Advanced)

Address Windowing Extension (AWE) is an advanced option used to support up to a maximum of 64 gigabytes (GB) of physical memory.

C2 Audit Mode

Use C2 audit mode to review both successful and unsuccessful attempts to access statements and objects. Allowing for the documentation of system activity and observance of security policy violations, C2 auditing tracks C2 audit events and records them to a file in the \mssql\data directory or the \ mssql $instancename\data directory for named instances of SQL Server. If the file reaches a size limit of 200 megabytes (MB), C2 auditing starts a new file.

Cost Threshold for Parallelism (Advanced)

Use this option to specify the threshold where SQL Server creates and executes parallel query plans. Parallel query plans are executed only when the estimated cost to execute a serial plan for the same query is higher than the value set. The cost refers to an estimated elapsed time in seconds that is required to execute a standard plan. Only set cost threshold for parallelism on symmetric multiprocessors.

Cursor Threshold (Advanced)

Use this option to indicate the number of rows in the cursor set at which cursor keysets are generated asynchronously. If you set Cursor Threshold to -1 , all keysets are generated synchronously, which benefits small cursor sets. If you set Cursor Threshold to , 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 in Cursor Threshold. Do not set Cursor Threshold too low because small result sets are better built synchronously.

Default Language

Use this option to specify the default language for all newly created logins.

Fill Factor (Advanced)

Use this option to specify how full the server should make each page when it creates a new index using existing data. The Fill Factor percentage affects performance because SQL Server must take time to split pages when they fill up. The default for Fill Factor of 0 (zero) does not mean that pages are 0% full. It is treated similarly to a fill factor value of 100 in that indexes are created with full data pages and nonclustered indexes with full leaf pages. The default setting is different from 100 in that SQL Server leaves some space within the upper level of the index tree.

Index Create Memory (Advanced)

Use this option to control the amount of memory used by index creation sorts. The Index Create Memory option is self-configuring and should operate without requiring adjustment. If difficulties are experienced creating indexes, consider increasing the value. Query sorts are controlled through the Min Memory Per Query option. The default value for this option is (self-configuring).

Default Full-Text Language (Advanced)

Use the default full-text language option to specify a default language value for full-text indexed columns . The default value of this option is the language of the server.

Lightweight Pooling (Advanced)

This option provides a means of reducing the overhead associated with the excessive context switching sometimes seen in multiprocessor environments. When excessive context switching is present, lightweight pooling might provide better throughput.

Locks (Advanced)

The Locks option sets the maximum number of available locks, limiting the amount of memory the server uses. The default setting is , which enables SQL Server to allocate and deallocate locks dynamically based on changing system requirements.

Max Degree of Parallelism (Advanced)

This option limits the number of processors to use in parallel plan execution. The default value is (actual number of CPUs) and the maximum is 32 .

Max Server Memory/Min Server Memory

These two settings establish upper and lower limits to the amount of memory the database engine uses. The database engine starts with only the memory required to initialize. As the workload increases, it acquires additional memory. The database engine frees any of the acquired memory until it reaches the amount specified in Min Server Memory.

Max Text Repl Size

Specifies the maximum size (in bytes) of text and image data that can be added to a replicated column in a single INSERT , UPDATE , WRITETEXT , or UPDATETEXT statement.

Max Worker Threads (Advanced)

Configures the number of worker threads available to the server and its processes. SQL Server uses the threads so that one or more threads simultaneously support each network that SQL Server supports; another thread handles database checkpoints; and a pool of threads handles user connections.

Media Retention (Advanced)

Use the Media Retention option to provide a default for the length of time each backup should be retained. Overridden by the RETAINDAYS clause of the BACKUP statement, Media Retention helps protect backups from being overwritten until the specified number of days has elapsed.

Min Memory Per Query (Advanced)

Use this option to specify the minimum amount of memory that will be allocated for the execution of a query.

Nested Triggers

The Nested Triggers option enables actions that initiate another trigger to be performed. When the Nested Triggers option is set to , triggers cannot cascade. When the Nested Triggers option is set to the default setting of 1 , triggers can cascade to as many as 32 levels.

Network Packet Size (Advanced)

Use this option to set the packet size used across the entire network. The default packet size is 4096 bytes. If an application does bulk copy operations, or sends or receives large amounts of text or image data, a packet size larger than the default can improve efficiency because it results in fewer network reads and writes . If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers.

Open Objects (Advanced)

Use this option to set the maximum number of database objects that can be open at one time. Database objects are those objects defined in the sysobjects table: tables, views, rules, stored procedures, defaults, and triggers.

Priority Boost (Advanced)

This option specifies the processor scheduling priority. If you set this option to 1 , SQL Server runs at a priority base of 13 in the Windows NT 4.0 or Windows 2000 Scheduler. The default is , which is a priority base of 7.

Query Governor Cost Limit (Advanced)

Specifies an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to execute a query.

Query Wait (Advanced)

Memory- intensive queries, such as those involving sorting and hashing, are queued when not enough memory is available to run the query. The query times out after a set amount of time that SQL Server calculates (25 times the estimated cost of the query) or the time amount specified by the non-negative value of the query wait.

Recovery Interval (Advanced)

Use this option to set the maximum number of minutes per database that the server needs to recover the database activity. The recovery process is initiated each time SQL Server starts or as the basis for completing a restore operation. The recovery process rolls back transactions that did not commit and rolls forward transactions that did commit. This configuration option sets an upper limit on the time it should take to recover each database. The default is , indicating automatic configuration by SQL Server. In practice, this means a recovery time of less than one minute and a checkpoint marker is placed into the transaction log approximately every one minute for active databases.

Remote Access

Use the Remote Access option to control logins from remote servers running SQL Server. Set Remote Access to 1 (default) to enable logins from remote servers. Set the option to to secure a local server and prevent access from a remote server.

Remote Login Timeout

Use this option to specify the number of seconds to wait before returning from a failed remote login attempt.

Remote Proc Trans

This option protects the activities of a server-to-server process through the use of the Distributed Transaction Coordinator. Set Remote Proc Trans to 1 to provide an MS DTC-coordinated distributed transaction that protects the ACID properties of transactions. Sessions begun after setting this option to 1 , inherit the configuration setting as their default.

Remote Query Timeout

This option is used to indicate the number of seconds that must elapse when processing a remote operation before the operation times out. The default of 600 sets a ten-minute wait.

Scan for Startup Process (Advanced)

Use this option to scan for automatic execution of stored procedures at startup time. If it is set to 1 , SQL Server scans for and executes all automatically executed stored procedures defined on the server. The default value is (do not scan).

Set Working Set Size (Advanced)

Reserves physical memory space for SQL Server that is equal to the server memory setting. SQL Server, based on workload and available resources, configures the server memory setting automatically. It varies dynamically between the Min Server Memory and Max Server Memory settings.

Two Digit Year Cutoff

Use the Two Digit Year Cutoff option to specify an integer from 1753 to 9999 that represents the last year for interpreting two-digit years as four-digit years .

User Connections (Advanced)

Use this option to specify the maximum number of simultaneous user connections. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application(s) and hardware. SQL Server enables a maximum of 32,767 user connections.

User Options

The User Options option is used to specify global defaults for all users. A list of default query processing options is established for the duration of a user's work session. A user can override these defaults by using the SET statement. You can configure user options dynamically for new logins. After you change the setting of user options, new logins use the new setting; current logins are not affected.

Reconfigure

This option updates the server configuration. It is used after the application of sp_configure to change server settings and make the new settings take effect. Because some configuration options require that a server stop and restart before the currently running value can be updated, Reconfigure does not always update the currently running value. Use the With Override option of this command to force a value that might or might not meet ranges of allowed values or recommended settings.

Changing configuration options can easily be performed using T-SQL operations:

  TSQL   USE master  EXEC sp_configure 'show advanced option', '1' RECONFIGURE GO EXEC sp_configure 'recovery interval', '3' RECONFIGURE WITH OVERRIDE 

Step by Step 3.8 takes you through the setting of some common server options.

STEP BY STEP

3.8 Setting Server Configuration Options

1. Expand a server group, then right-click the server, and then select Properties.

2. Select the Database Settings tab.

3. Set the Recovery Interval to 3.

4. Select OK to save the setting.

Configuration Exam Considerations

The SQL Server configuration options are used to fine-tune the database environment. Many options provide a mechanism for an administrator or developer to obtain optimum performance and achieve a more secure and stable server. A total approach to an optimum environment also involves the proper use of database configuration options. Server property adjustments affect all the databases stored on the server where database configuration options are used to control a database and not affect other databases.

Setting Database Configuration Options

Standard database configuration settings are available through the Database Properties in the Enterprise Manager or can be accessed using the sp_dboption stored procedure. Some of the more advanced settings cannot be set singly ; they must be set in combination with other settings.

There are five categories of database options:

• Auto options . AUTO_CLOSE, AUTO_CREATE_ STATISTICS, AUTO_UPDATE_STATISTICS, and AUTO_SHRINK

• Cursor options . CURSOR_CLOSE_ON_COMMIT and CURSOR_DEFAULT LOCAL or GLOBAL

• Recovery options . RECOVERY FULL or BULK_LOGGED or SIMPLE and TORN_PAGE_DETECTION

• SQL options . ANSI_NULL_DEFAULT, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, NUMERIC_ROUNDABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_ IDENTIFIER, and RECURSIVE_TRIGGERS

• State options . OFFLINE or ONLINE, READ_ONLY or READ_WRITE, SINGLE_USER or RESTRICTED_USER or MULTI_USER and WITH ROLLBACK AFTER or WITH ROLLBACK IMMEDIATE or NO_WAIT.

These options are described in the following sections, and are covered in alphabetical order.

ANSI_NULL_DEFAULT

This option enables the user to control the default nullability. When NULL or NOT NULL is not specified, a user-defined data type or a column definition uses the default setting for nullability. When this option is set to ON , all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.

ANSI_NULLS

When set to ON , all comparisons to a null value evaluate to NULL (unknown). When set to OFF , comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL . By default, the ANSI_NULLS database option is OFF .

ANSI_PADDING

When set to ON , trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed . Values are not padded to the length of the column. When set to OFF , the trailing blanks and zeros are trimmed. This setting affects only the definition of new columns. It is recommended that ANSI_PADDING always be set to ON . SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.

ANSI_WARNINGS

When set to ON , errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF , no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF .

ARITHABORT

When set to ON , an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When set to OFF , a warning message is displayed if one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

AUTO_CLOSE

When set to ON , server resources are freed up as soon as the database is closed and shut down cleanly when the last user of the database exits. By default, this option is set to ON for all databases in the Desktop Engine, and OFF for all other editions. The database reopens automatically when a user tries to use the database again. When set to OFF , the database remains open even if no users are currently using it.

AUTO_CREATE_STATISTICS

When set to ON , statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the optimizer can better determine how to evaluate queries. If the statistics are not used, SQL Server automatically deletes them. When set to OFF , SQL Server does not automatically create statistics; instead, statistics can be manually created.

AUTO_SHRINK

When set to ON , the database files are set up for periodic shrinking. Any database-associated file, data, or log can be shrunk automatically. When set to OFF , the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases in SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.

AUTO_UPDATE_STATISTICS

When set to ON , existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When set to OFF , existing statistics are not automatically updated; instead, statistics can be manually updated.

CONCAT_NULL_YIELDS_NULL

When set to ON , if one of the operands in a concatenation operation is NULL , the result of the operation is NULL . When set to OFF , concatenating a null value with a character string yields the character string as the result.

CURSOR_CLOSE_ON_COMMIT

When set to ON , any open cursors are closed automatically when a transaction using the cursor is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed, which is usually when a procedure finishes.

CURSOR_DEFAULT LOCAL or GLOBAL

When CURSOR_DEFAULT LOCAL is set, and a cursor is not defined as GLOBAL when it is created, the scope of the cursor is local to the batch, stored procedure, or trigger. The cursor name is valid only within this scope. When CURSOR_DEFAULT GLOBAL is set, and a cursor is not defined as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch the connection executes.

NUMERIC_ROUNDABORT

If set to ON , an error is generated when the loss of precision occurs in an expression. When set to OFF , losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

OFFLINE or ONLINE

When OFFLINE is specified, the database is closed and shut down cleanly and marked offline. The database cannot be modified while it is offline. When ONLINE is specified, the database is open and available for use.

QUOTED_IDENTIFIER

When set to ON , identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the T-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in T-SQL identifiers. When set to OFF (default), identifiers cannot be in quotation marks and must follow all T-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. Identifiers must be enclosed in square brackets ([ ]) if they contain spaces or other characters or key words.

READ_ONLY or READ_WRITE

When READ_ONLY is specified, users can retrieve data from the database but cannot modify the data. Automatic recovery is skipped at system startup and shrinking the database is not possible. No locking takes place in read-only databases, which can result in faster query performance. When READ_WRITE is specified, users can retrieve and modify data.

RECOVERY FULL or BULK_LOGGED or SIMPLE

When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations, such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.

RECURSIVE_TRIGGERS

When set to ON , triggers are enabled to fire recursively. When set to OFF (default), triggers cannot be fired recursively.

SINGLE_USER or RESTRICTED_USER or MULTI_USER

SINGLE_USER enables only one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused . RESTRICTED_USER enables only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. MULTI_USER enables all users with the appropriate permissions to connect to the database.

TORN_PAGE_DETECTION

This recovery option enables SQL Server to detect incomplete I/O operations caused by power failures or other system outages. When set to ON , this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page as the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is therefore detected .

WITH <termination>

The termination clause of the ALTER DATABASE statement specifies how to terminate incomplete transactions. Breaking their connections to the database terminates transactions. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own. ROLLBACK AFTER 'integer' SECONDS waits for the specified number of seconds. ROLLBACK IMMEDIATE breaks unqualified connections immediately. NO_WAIT checks for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. When the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

Similar to setting server configuration options, the process of setting database options is described in Step by Step 3.9.

STEP BY STEP

3.9 Setting Database Options

1. Expand a server group, and then expand the server where the database is to be placed.

2. Right-click Databases, and then click Properties.

3. Select the Options tab.

4. Change the appropriate desired settings and select OK to save.

T-SQL database options can be set programmatically, although this is not normally recommended. The following procedure illustrates how this can be done in those rare instances where it is desired:

TSQL

View settable database options:

 Sp_dboption 

View which options have been set on Northwind database:

 Sp_dboption Northwind 

Turn off an option:

 Sp_dboption Northwind, 'autoclose', False 

Turn on an option:

 Sp_dboption Northwind, 'autoclose', True 

Setting the Database Options

Database options are set to allow for application or procedural requirements and to provide for administrative configuration. You will interact with these settings to set up backups, allow for specific procedures, and provide appropriate access levels, depending on what is needed for a given process. Learn the settings that are required for each process and know the resulting effect on the system under different operating configurations.