Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

Administering Database Properties

The Database Properties dialog box is the place where you manage the configuration options and values of a user or system database. You can execute additional tasks from within these pages, such as database mirroring and transaction log shipping. The configuration pages in the Database Properties dialog box include

  • General
  • Files
  • Filegroups
  • Options
  • Permissions
  • Extended Properties
  • Mirroring
  • Transaction Log Shipping

The upcoming sections describe each page and setting in its entirety. To invoke the Database Properties dialog box, perform the following steps:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
  2. In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the Databases folder.
  3. Select a desired database such as AdventureWorks, right-click, and select Properties. The Database Properties dialog box, including all the pages, is displayed in the left pane.

Administering the Database Properties General Page

General, the first page in the Database Properties dialog box, displays information exclusive to backups, database settings, and collation settings. Specific information displayed includes

  • Last Database Backup
  • Last Database Log Backup
  • Database Name
  • State of the Database Status
  • Database Owner
  • Date Database Was Created
  • Size of the Database
  • Space Available
  • Number of Users Currently Connected to the Database
  • Collation Settings

You should use this page for obtaining factual information about a database, as displayed in Figure 1.11.

Figure 1.11

Figure 1.11 Viewing the General page in the Database Properties dialog box.

Administering the Database Properties Files Page

The second Database Properties page is called Files. Here, you can change the owner of the database, enable full-text indexing, and manage the database files, as shown in Figure 1.12.

Figure 1.12

Figure 1.12 Configuring the database files settings from within the Files page.

Managing Database Files

The Files page is used to configure settings pertaining to database files and transation logs. You will spend time working in the Files page when initially rolling out a database and conducting capacity planning. Following are the settings you'll see:

  • Data and Log File Types—A SQL Server 2005 OLTP database is composed of two types of files: data and log. Each database has at least one data file and one log file. When you're scaling a database, it is possible to create more than one data and one log file. If multiple data files exist, the first data file in the database has the extension *.mdf and subsequent data files maintain the extension *.ndf. In addition, all log files use the extension *.ldf.
  • Filegroups—When you're working with multiple data files, it is possible to create filegroups. A filegroup allows you to logically and physically group database objects and files together. The default filegroup, known as the Primary Filegroup, maintains all the system tables and data files not assigned to other filegroups. Subsequent filegroups need to be created and named explicitly.
  • Initial Size in MB—This setting indicates the preliminary size of a database or transaction log file. You can increase the size of a file by modifying this value to a higher number in megabytes.
  • Autogrowth Feature—This feature enables you to manage the file growth of both the data and transaction log files. When you click the ellipses button, a Change Autogrowth dialog box appears. The configurable settings include whether to enable autogrowth, and if autogrowth is selected, whether autogrowth should occur based on a percentage or in a specified number of megabytes. The final setting is whether to choose a maximum file size for each file. The two options available are Restricted File Growth (MB) or Unrestricted File Growth.
  • Database Files and RAID Sets—Database files should reside only on RAID sets to provide fault tolerance and availability, while at the same time increasing performance. If cost is not an issue, data files and transaction logs should be placed on RAID 1+0 volumes. RAID 1+0 provides the best availability and performance because it combines mirroring with stripping. However, if this is not a possibility due to budget, data files should be placed on RAID 5 and transaction logs on RAID 1.

Increasing Initial Size of a Database File

Perform the following steps to increase the data file for the AdventureWorks database using SSMS:

  1. In Object Explorer, right-click the AdventureWorks database and select Properties.
  2. Select the File Page in the Database Properties dialog box.
  3. Enter the new numerical value for the desired file size in the Initial Size (MB) column for a data or log file and click OK.

Creating Additional Filegroups for a Database

Perform the following steps to create a new filegroup and files using the AdventureWorks database with both SSMS and TSQL:

  1. In Object Explorer, right-click the AdventureWorks database and select Properties.
  2. Select the Filegroups page in the Database Properties dialog box.
  3. Click the Add button to create a new filegroup.
  4. When a new row appears, enter the name of new the filegroup and enable the option Default.

Alternatively, you can use the following TSQL script to create the new filegroup for the AdventureWorks database:

USE [master]
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [SecondFileGroup]

Creating New Data Files for a Database and Placing Them in Different Filegroups

Now that you've created a new filegroup, you can create two additional data files for the AdventureWorks database and place them on the newly created filegroup:

  1. In Object Explorer, right-click the AdventureWorks database and select Properties.
  2. Select the Files page in the Database Properties dialog box.
  3. Click the Add button to create new data files.
  4. In the Database Files section, enter the following information in the appropriate columns:



    Logical Name


    File Type





    10 MB



    File Name


  5. Click OK.

You can now conduct the same steps by executing the following TSQL syntax to create a new data file:

USE [master]
ALTER DATABASE [AdventureWorks]
ADD FILE (NAME = N'AdventureWorks_Data2',
FILENAME = N'C:\AdventureWorks_Data2.ndf',
SIZE = 10240KB , FILEGROWTH = 1024KB )
TO FILEGROUP [SecondFileGroup]

Configuring Autogrowth on a Database File

Next, to configure autogrowth on the database file, follow these steps:

  1. From within the File page on the Database Properties dialog box, click the ellipses button located in the Autogrowth column on a desired database file to configure it.
  2. On the Change Autogrowth dialog box, configure the File Growth and Maximum File Size settings and click OK.
  3. Click OK on the Database Properties dialog box to complete the task.

You can use the following TSQL syntax to modify the Autogrowth settings for a database file based on a growth rate at 50% and a maximum file size of 1000MB:

USE [master]
ALTER DATABASE [AdventureWorks]
MODIFY FILE ( NAME = N'AdventureWorks_Data',
MAXSIZE = 1024000KB , FILEGROWTH = 50%)

Administering the Database Properties Filegroups Page

As stated previously, filegroups are a great way to organize data objects, address performance issues, and minimize backup times. The Filegroup page is best used for viewing existing filegroups, creating new ones, marking filegroups as read-only, and configuring which filegroup will be the default.

To improve performance, you can create subsequent filegroups and place data and indexes onto them. In addition, if there isn't enough physical storage available on a volume, you can create a new filegroup and physically place all files on a different volume or LUN if Storage Area Network (SAN) is being used.

Finally, if a database has static data, it is possible to move this data to a specified filegroup and mark this filegroup as read-only. This minimizes backup times; because the data does not change, SQL Server marks this file group and skips it.

Administering the Database Properties Options Page

The Options page, shown in Figure 1.13, includes configuration settings on Collation, Recovery Model, and other options such as Automatic, Cursor, and Miscellaneous. The following sections explain these settings.

Figure 1.13

Figure 1.13 Viewing and configuring the Database Properties Options page settings.


The Collation setting located on the Database Properties Options page specifies the policies for how strings of character data are sorted and compared, for a specific database, based on the industry standards of particular languages and locales. Unlike SQL Server collation, the database collation setting can be changed by selecting the appropriate setting from the Collation drop-down box.

Recovery Model

The second setting within the Options page is Recovery Model. This is an important setting because it dictates how much data can be retained, which ultimately affects the outcome of a restore.

Understanding and Effectively Using Recovery Models

Each recovery model handles recovery differently. Specifically, each model differs in how it manages logging, which results in whether an organization's database can be recovered to the point of failure. The three recovery models associated with a database in the Database Engine are

  • Full—This recovery model captures and logs all transactions, making it possible to restore a database to a determined point-in-time or up-to-the-minute. Based on this model, you must conduct maintenance on the transaction log to prevent logs from growing too large and disks becoming full. When you perform backups, space is made available once again and can be used until the next planned backup. Organizations may notice that maintaining a transaction log slightly degrades SQL Server performance because all transactions to the database are logged. Organizations that insist on preserving critical data often overlook this issue because they realize that this model offers them the highest level of recovery capabilities.
  • Simple—This model provides organizations with the least number of options for recovering data. The Simple recovery model truncates the transaction log after each backup. This means a database can be recovered only up until the last successful full or differential database backup. This recovery model also provides the least amount of administration because transaction log backups are not permitted. In addition, data entered into the database after a successful full or differential database backup is unrecoverable. Organizations that store data they do not deem as mission critical may choose to use this model.
  • Bulk-Logged—This recovery model maintains a transaction log and is similar to the Full recovery model. The main difference is that transaction logging is minimal during bulk operations to maximize database performance and reduce the log size when large amounts of data are inserted into the database. Bulk import operations such as BCP, BULK INSERT, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX are minimally logged.

    Since the Bulk-Logged recovery model provides only minimal logging of bulk operations, you cannot restore the database to the point of failure if a disaster occurs during a bulk-logged operation. In most situations, an organization will have to restore the database, including the latest transaction log, and rerun the Bulk-Logged operation.

    This model is typically used if organizations need to run large bulk operations that degrade system performance and do not require point-in-time recovery.

Next, you need to determine which model best suits your organization's needs. The following section is designed to help you choose the appropriate model.

Selecting the Appropriate Recovery Model

It is important to select the appropriate recovery model because doing so affects an organization's ability to recover, manage, and maintain data.

For enterprise production systems, the Full recovery model is the best model for preventing critical data loss and restoring data to a specific point in time. As long as the transaction log is available, it is possible to even get up-to-the-minute recovery and point-in-time restore if the end of the transaction log is backed up and restored. The trade-off for the Full recovery model is its impact on other operations.

Organizations leverage the Simple recovery model if the data backed up is not critical, data is static or does not change often, or if loss is not a concern for the organization. In this situation, the organization loses all transactions since the last full or last differential backup. This model is typical for test environments or production databases that are not mission critical.

Finally, organizations that typically select the Bulk-Logged recovery model have critical data, but logging large amounts of data degrades system performance, or these bulk operations are conducted after hours and do not interfere with normal transaction processing. In addition, there isn't a need for point-in-time or up-to-the-minute restores.

Switching the Database Recovery Model with SQL Server Management Studio

To set the recovery model on a SQL Server 2005 database using SSMS, perform the following steps:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
  2. In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the database folder.
  3. Select the desired SQL Server database, right-click on the database, and select Properties.
  4. In the Database Properties dialog box, select the Options page.
  5. In Recovery Model, select either Full, Bulk-Logged, or Simple from the drop-down list and click OK.

Switching the Database Recovery Model with Transact-SQL

It is possible not only to change the recovery model of a database with SQL Server Management Studio, but also to make changes to the database recovery model using Transact-SQL commands such as ALTER DATABASE. You can use the following TSQL syntax to change the recovery model for the AdventureWorks Database from Simple to Full:

--Switching the Database Recovery model
Use Master

Compatibility Level

The Compatibility Level setting located on the Database Properties Options page is meant for interoperability and backward compatibility of previous versions of SQL Server. The options available are SQL Server 2005 (90), SQL Server 2000 (80), and SQL Server 7.0 (70).

Other Options (Automatic)

Also available on the Database Properties Options page are these options:

  • Auto Close—When the last user exits the database, the database is shut down cleanly and resources are freed. The values to be entered are either True or False.
  • Auto Create Statistics—This setting specifies whether the database will automatically update statistics to optimize a database. The default setting is True, and this value is recommended.
  • Auto Shrink—Similar to the shrink task, if this setting is set to True, SQL Server removes unused space from the database on a periodic basis. For production databases, this setting is not recommended.
  • Auto Update Statistics—Similar to the Auto Create Statistics settings, this setting automatically updates any out-of-date statistics for the database. The default setting is True, and this value is recommended.
  • Auto Update Statistics Asynchronously—If the statistics are out of date, this setting dictates whether a query should be updated first before being fired.

Other Options (Cursor)

The following options are also available on the Database Properties Options page:

  • Close Cursor on Commit Enabled—This setting dictates whether cursors should be closed after a transaction is committed. If the value is True, cursors are closed when the transaction is committed, and if the value is False, cursors remain open. The default value is False.
  • Default Cursor—The values available include Global and Local. The Global setting indicates that the cursor name is global to the connection based on the Declare statement. During the Declare Cursor statement, the Local setting specifies that the cursor name is Local to the stored procedure, trigger, or batch.

Other Options (Miscellaneous)

The following options are also available on the Database Properties Options page:

  • ANSI Null Default—The value to be entered is either True or False. When set to False, the setting controls the behavior to supersede the default nullability of new columns.
  • ANSI Null Enabled—This setting controls the behavior of the comparison operators when used with null values. The comparison operators consist of Equals (=) and Not Equal To (<>).
  • ANSI Padding Enabled—This setting controls whether padding should be enabled or disabled. Padding dictates how the column stores values shorter than the defined size of the column.
  • ANSI Warnings Enabled—If this option is set to True, a warning message is displayed if null values appear in aggregate functions.
  • Arithmetic Abort Enabled—If this option is set to True, an error is returned, and the transaction is rolled back if an overflow or divide-by-zero error occurs. If the value False is used, an error is displayed; however, the transaction is not rolled back.
  • Concatenate Null Yields Null—This setting specifies how null values are concatenated. True indicates that string + NULL returns NULL. When False, the result is string.
  • Cross-Database Ownership Chaining—Settings include either True or False. True represents that the database allows cross-database ownership chaining, whereas False indicates that this option is disabled.
  • Date Correlation Optimization Enabled—If this option is set to True, SQL Server maintains correlation optimization statistics on the date columns of tables that are joined by a foreign key.
    • Numeric Round-Abort—This setting indicates how the database will handle rounding errors.
    • Parameterization—This setting controls whether queries are parameterized. The two options available are Simple and Forced. When you use Simple, queries are parameterized based on the default behavior of the database, whereas when you use Forced, all queries are parameterized.
  • Quoted Identifiers Enabled—This setting determines whether SQL Server keywords can be used as identifiers when enclosed in quotation marks.
  • Recursive Triggers Enabled—When this setting is enabled by setting the value to True, SQL Server allows recursive triggers to be fired.
  • Trustworthy—This setting allows SQL Server to grant access to the database by the impersonation context. A value of True enables this setting.
  • VarDecimal Storage Format Enabled—When this option is set to True, the database is enabled for the VarDecimal storage format, which is a feature available only with Service Pack 2.

Other Options (Recovery)

Also available on the Database Properties Options page is

  • Page Verify—This option controls how SQL Server will handle incomplete transactions based on disk I/O errors. The available options include Checksum, Torn Page Detection, and None.

Other Options (State)

The following options are available on the Database Properties Options page:

  • Read Only—Setting the database value to True makes the database read-only.

    The default syntax for managing the read-only state of a database is

    ALTER DATABASE database_name
    <db_update_option> ::=
  • State—This field cannot be edited; it informs you of the state of the database. Possible states include Online, Offline, Restoring, Recovering, Recovery Pending, Suspect, and Emergency.

    To change the state of a database with TSQL, use the default syntax:

    ALTER DATABASE database_name
    <db_state_option> ::=
  • Restrict Access—This setting manages which users can connect to the database. Possible values include Multiple, Single, and Restricted. The Multiple setting is the default state, which allows all users and applications to connect to the database. Single user mode is meant for only one user to access the database. This is typically used for emergency administration. The final setting, Restricted, allows only members of the db_owner, dbcreator, or sysadmin accounts to access the database.

    The TSQL code for setting the Restrict Access value is as follows:

    ALTER DATABASE database_name
    <db_user_access_option> ::=

Administering the Database Properties Mirroring Page

Most database administrators believe database mirroring is the paramount new feature included with the release of SQL Server 2005. Database mirroring is also a SQL Server high-availability alternative for increasing availability of a desired database. Database mirroring transmits transaction log records directly from one SQL Server instance to another SQL Server instance. In addition, if the primary SQL Server instance becomes unavailable, the services and clients automatically fail over to the mirrored server. Automatic failover is contingent on the settings and versions used.

The Database Properties Mirroring page is the primary tool for configuring, managing, and monitoring database mirroring for a database. The Mirroring page includes configuration settings for security; mirroring operating mode; and the principal, mirror, and witness server network addresses. For more information on configuring database mirroring, review Chapter 19, "Administering and Managing Database Mirroring" (online).

Administering the Database Properties Permissions Page

The Database Properties Permissions page is used to administer database authorization and role-based access and to control permissions on the database. Chapter 13 covers these topics in their entirety.

Administering the Database Properties Extended Permissions Page

The Database Properties Extended Permissions page is used for managing extended properties on database objects, such as descriptive text, input masks, and formatting rules. The extended properties can be applied to schema, schema view, or column view.

Administering the Database Properties Transaction Log Shipping Page

The final Database Properties page is Transaction Log Shipping. Transaction log shipping is one of four SQL Server 2005 high-availability alternatives similar to database mirroring. In log shipping, transactions are sent from a primary server to the standby secondary server on an incremental basis. However, unlike with database mirroring, automatic failover is not a supported feature.

The configuration settings located on the Transaction Log Shipping page in the Database Properties dialog box are the primary place for you to configure, manage, and monitor transaction log shipping.

For more information on administering transaction log shipping, including step-by-step installation instructions, review Chapter 20, "Administering and Managing Log Shipping" (online).

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020