Modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots cannot be modified. To modify database options associated with replication, use sp_replicationdboption.
To remove a database, use DROP DATABASE.
To rename a database, use the MODIFY NAME = new_database_name option with ALTER DATABASE.
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a BACKUP statement is running.
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.
In SQL Server 2005, the state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States. The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.
When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The exception is setting database mirroring options. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file. For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.
Setting Options
To retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX. For a list of default values assigned to the database when it is first created, see Setting Database Options.
After you set a database option, the modification takes effect immediately.
To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.
Not all database options use the WITH <termination> clause or can be specified in combination with other options. The following table lists these options and their option and termination status.
|
Options category
|
Can be specified with other options
|
Can use the WITH <termination> clause
|
|---|
|
<db_state_option>
|
Yes
|
Yes
|
|
<db_user_access_option>
|
Yes
|
Yes
|
|
db_update_option>
|
Yes
|
Yes
|
|
<external_access_option>
|
Yes
|
No
|
|
<cursor_option>
|
Yes
|
No
|
|
<auto_option>
|
Yes
|
No
|
|
<sql_option>
|
Yes
|
No
|
|
<recovery_option>
|
Yes
|
No
|
|
<database_mirroring_option>
|
No
|
No
|
|
ALLOW_SNAPSHOT_ISOLATION
|
No
|
No
|
|
READ_COMMITTED_SNAPSHOT
|
No
|
Yes
|
|
<service_broker_option>
|
Yes
|
No
|
|
DATE_CORRELATION_OPTIMIZATION
|
Yes
|
Yes
|
|
<parameterization_option>
|
Yes
|
Yes
|
The plan cache for the instance of SQL Server is cleared by setting one of the following options:
|
OFFLINE
|
READ_WRITE
|
|
ONLINE
|
MODIFY FILEGROUP DEFAULT
|
|
MODIFY_NAME
|
MODIFY FILEGROUP READ_WRITE
|
|
COLLATE
|
MODIFY FILEGROUP READ_ONLY
|
|
READ_ONLY
|
|
Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server 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.
Moving Files
In SQL Server 2005, you can move system or user-defined data and log files by specifying the new location in FILENAME. This may be useful in the following scenarios:
-
Failure recovery. For example, the database is in suspect mode or shutdown caused by hardware failure.
-
Planned relocation.
-
Relocation for scheduled disk maintenance.
For more information, see Moving Database Files.
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
-
Create a database.
-
Add files to an existing database.
-
Increase the size of an existing file.
-
Restore a database or filegroup.
In SQL Server 2005, data files can be initialized instantaneously. This enables for fast execution of these file operations. For more information, see Database File Initialization.
Changing the Database Collation
Before you apply a different collation to a database, make sure that the following conditions are in place:
-
You are the only one currently using the database.
-
No schema-bound object depends on the collation of the database.
If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:
-
User-defined functions and views created with SCHEMABINDING.
-
Computed columns.
-
CHECK constraints.
-
Table-valued functions that return tables with character columns with collations inherited from the default database collation.
-
Changing the database collation does not create duplicates among any system names for the database objects.
The following namespaces may cause the failure of a database collation change if duplicate names result from the changed collation:
-
Object names such as a procedure, table, trigger, or view.
-
Schema names
-
Principals such as a group, role, or user.
-
Scalar-type names such as system and user-defined types.
-
Full-text catalog names.
-
Column or parameter names within an object.
-
Index names within a table.
Duplicate names resulting from the new collation will cause the change action to fail, and SQL Server will return an error message specifying the namespace where the duplicate was found.
Viewing Database Information
You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups. For more information, see Viewing Database Metadata.
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks database.
USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD FILE
(
NAME = Test1dat2,
FILENAME = '''+ @data_path + 't1dat2.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)'
);
GO
B. Adding a filegroup with two files to a database
The following example creates the filegroup Test1FG1 in the AdventureWorks database and adds two 5-MB files to the filegroup.
USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD FILE
(
NAME = test1dat3,
FILENAME = '''+ @data_path + 't1dat3.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1dat4,
FILENAME = '''+ @data_path + 't1dat4.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO
C. Adding two log files to a database
The following example adds two 5-MB log files to the AdventureWorks database.
USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD LOG FILE
(
NAME = test1log2,
FILENAME = '''+ @data_path + 'test2log.ldf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1log3,
FILENAME = '''+ @data_path + 'test3log.ldf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)'
);
GO
D. Removing a file from a database
The following example removes one of the files added in example B.
USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO
E. Modifying a file
The following example increases the size of one of the files added in example B.
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB);
GO
F. Moving a file to a new location
The following example moves the Test1dat2 file created in example A to a new directory.
.gif) Note: |
|---|
|
You must physically move the file to the new directory before running this example. Afterward, stop and start the instance of SQL Server or take the AdventureWorks database OFFLINE and then ONLINE to implement the change.
|
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
NAME = Test1dat2,
FILENAME = N'c:\t1dat2.ndf'
);
GO
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.
-
Determine the logical file names of the
tempdb database and their current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
-
Change the location of each file by using
ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
-
Stop and restart the instance of SQL Server.
-
Verify the file change.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
-
Delete the tempdb.mdf and templog.ldf files from their original location.
H. Making a filegroup the default
The following example makes the Test1FG1 filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note that PRIMARY must be delimited by brackets or quotation marks.
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
I. Setting options on a database
The following example sets the recovery model and data page verification options for the AdventureWorks sample database.
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO
J. Setting the database to READ_ONLY
Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database. The following example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks database to READ_ONLY and returns access to the database to all users.
| Note: |
|---|
This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected.
|
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
K. Enabling snapshot isolation on a database
The following example enables the snapshot isolation framework option for the AdventureWorks database.
USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
The result set shows that the snapshot isolation framework is enabled.
name snapshot_isolation_state description
--------------- ------------------------ -----------
AdventureWorks 1 ON
L. Creating a database mirroring session with a witness
Setting up database mirroring with a witness requires configuring security and preparing the mirror database, and also using ALTER DATABASE to set the partners. For an example of the complete setup process, see Setting Up Database Mirroring.
M. Manually failing over a database mirroring session
Manual failover can be initiated from either database mirroring partner. Before failing over, you should verify that the server you believe to be the current principal server actually is the principal server. For example, for the AdventureWorks database, on that server instance that you think is the current principal server, execute the following query:
SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks'
GO
If the server instance is in fact the principal, the value of mirroring_role_desc is Principal. If this server instance were the mirror server, the SELECT statement would return Mirror.
The following example assumes that the server is the current principal.
-
Manually fail over to the database mirroring partner:
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO
-
To verify the results of the failover on the new mirror, execute the following query:
SELECT name, mirroring_role_desc
FROM sys.databases WHERE name = N'AdventureWorks';
GO
The current value of mirroring_role_desc is now Mirror.