Configuring the Database

You can change certain database file properties, as well as add or drop files using either of the following methods:

• use the Properties dialog box in Management Studio
• use the ALTER DATABASE command (for details, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads)

Adding Files

Data files can be added to an existing file group in a database, or to a new file group. There are no special restrictions or requirements.

Dropping Files

To drop a file, you must first empty it using the DBCC SHRINKFILE command’s EMPTYFILE option, which transmits the file data to all the other files in the file group. Once you empty the file, you can use the ALTER DATABASE <database name> DROP FILE command to drop it.

Changing File Properties

You can change the size-related properties for all databases, as well as the filename property for the tempdb database (this takes effect after you restart Microsoft SQL Server). The SIZE, MAXSIZE, and FILEGROWTH properties can be changed using the ALTER DATABASE tempdb MODIFY FILE command. Note that the SIZE property can only be enlarged.

To shrink the file, use the DBCC SHRINKFILE command. For details and recommendations concerning file properties, see Creating the Database.

Each database contains a set of configurable options that determine its behavior. You can view or change the database options using any one of the following utilities:

• the Options tab in the Management Studio’s Properties dialog box
• the EXEC sp_dboptions stored procedure
• the ALTER DATABASE <database name> SET command

The following table lists, in alphabetical order, the default configuration options, as well as the configuration settings required for Universal CMDB certification:

Configuration Option	Description	Default	Universal CMDB Certification in Microsoft SQL Server
ANSI NULL default (see note below)	Specifies whether the database columns are defined as NULL or NOT NULL, by default	Not set	Not set
ANSI PADDING	Controls the way the column stores values shorter than the defined size of the column and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.	OFF	ON
Auto close	Specifies whether the database shuts down after its resources are freed and all users exit	Not set	Not set Note If set, it may take a long time for the database to allocate resources every time a user connects, after the database is closed.
Auto create statistics	Specifies whether missing statistics required by a query for optimization are built automatically during optimization	Set	Set
Auto shrink	Specifies whether the database is automatically shrunk every hour, leaving 25% of free space	Not set	Not set Note If set, constant growth/ shrinkage may cause file system fragmentation.
Auto update statistics	Specifies whether out-of-date statistics required by a query for optimization are built automatically during optimization	Set	Set
Compatibility level	The version of Microsoft SQL Server that the database appears to be (for the application)	The same version as the release installed	The same version as the release installed
Read only	Database is read only	Not set (READ_WRITE)	READ_WRITE
Recovery	The database recovery model determines the recovery capabilities by controlling the amount of bulk operation logging (such as Select into, Bulk, Insert, Create index, LOB manipulation). The higher the recovery model, the higher the recovery capabilities. However, the amount of logging also increases, which may affect performance.	Full	Full (unless you are certain that the lower recovery capabilities are sufficient for your system)
Recursive triggers	Specifies whether recursive triggers are supported	Not set	Not set
Restrict access	Only single users or members of the db_owner, dbcreator, or sysadmin groups can access the database.	Not set (MULTI_USER)	MULTI_USER
Torn page detection	Specifies whether incomplete pages can be detected	Set	Set
Truncate log on checkpoint	Automatically marks inactive portions of log for reuse on checkpoint	Not set	N/A
Use quoted identifiers	Specifies whether the Microsoft SQL Server enforces ANSI rules regarding quotation marks. Select this option to specify that double quotation marks be used only for identifiers, such as column and table names. Note that character strings must be enclosed in single quotation marks.	Not set	Not set

The following table summarizes the characteristics of each recovery model:

Model/ Support	Allows Log Backup	Allows Point-in-Time/Log Mark Restoration	Allows Backup Log when Data Crashes (Saves changes until the crash point)	Amount of Bulk Operation Logging (can affect the performance of bulk operations)
Simple	No	No	No	Minimal
Bulk Logged	Yes	No	No	Minimal
Full	Yes	Yes	Yes	Full

To check your database’s properties, run the command:

EXEC sp_helpdb <database name>

For information on SQL databases, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads.

For environments that execute heavy data-in operations, in order to speed up the INSERT statements, it is recommended to transform the database from composite index to non composite index.

You can achieve this by invoking the modifyCompositeIndexes JMX method with composite indexes setting to false. For detailed instructions, see How to Modify Composite Indexes.