ALTER DATABASE Statement
The ALTER DATABASE statement in SQL Server is used to modify the properties of an existing database. It allows you to change various aspects of a database, such as its size, file growth settings, recovery model, and collation.
Syntax
The basic syntax for ALTER DATABASE is as follows:
ALTER DATABASE database_name
{
-- File-related options
MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path_to_file' )
| MODIFY FILE ( NAME = logical_file_name, SIZE = new_size [KB | MB | GB | TB] )
| MODIFY FILE ( NAME = logical_file_name, MAXSIZE = max_size [KB | MB | GB | TB | UNLIMITED] )
| MODIFY FILEGROUP filegroup_name { READ_ONLY | READ_WRITE }
| ADD FILE ( NAME = logical_file_name, FILENAME = 'path_to_new_file' [, SIZE = size] [, MAXSIZE = max_size] [, FILEGROWTH = growth_increment] )
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name [ DEFAULT ]
-- Recovery and other database options
SET READ_COMMITTED_SNAPSHOT { ON | OFF } [ WITH ROLLBACK [ IMMEDIATE | AFTER_ABORT ] ]
| SET ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
| SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }
| SET CHANGE_TRACKING { ON | OFF } [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]
| SET COMPATIBILITY_LEVEL = { 150 | 160 | ... } -- Corresponding to SQL Server version
| SET FILESTREAM ( NON_TRANSACTIONAL_ACCESS = { READ_ONLY | FULL | OFF } )
| SET CONTAINMENT = { NONE | PARTIAL }
| SET PARAMETERIZATION { SIMPLE | FOR_INDEXED_VIEW_INDEXES | FOR_ as
Note: The exact syntax and available options can vary slightly between different versions of SQL Server. Always refer to the official Microsoft documentation for the most up-to-date and specific information.
Commonly Used Options
File Management
MODIFY FILE: Used to change file properties like the file name (logical name), path, size, maximum size, or file growth increment.ADD FILE: Adds a new data file or log file to the database.REMOVE FILE: Removes a file from the database. This is typically done after moving the data to another file and ensuring the database is no longer using the file to be removed.ADD FILEGROUP: Adds a new filegroup to the database.
Database Properties
SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }: Configures the database recovery model, which impacts transaction log management and recovery options.FULL: Logs all operations, allowing for point-in-time recovery. Requires regular transaction log backups.BULK_LOGGED: Logs bulk operations minimally. Good for scenarios with frequent large imports/exports where point-in-time recovery is not critical for those specific operations.SIMPLE: Logs only minimal information to recover to the last known good backup. Transaction logs are automatically truncated after operations.
SET READ_COMMITTED_SNAPSHOT { ON | OFF }: Enables or disables the Read Committed Snapshot Isolation (RCSI) transaction isolation level. When ON, readers do not block writers, and writers do not block readers at theREAD COMMITTEDlevel.SET ALLOW_SNAPSHOT_ISOLATION { ON | OFF }: Enables or disables Snapshot Isolation, another transaction isolation level that uses row versioning.SET COMPATIBILITY_LEVEL: Sets the database compatibility level, which affects database behavior to align with specific SQL Server versions.
Examples
Example 1: Increasing the size of a data file
This example increases the maximum size of the MyDatabase_Data file to 10GB.
ALTER DATABASE MyDatabase
MODIFY FILE ( NAME = MyDatabase_Data, MAXSIZE = 10GB );Example 2: Changing the recovery model to FULL
This example sets the recovery model for MyDatabase to FULL.
ALTER DATABASE MyDatabase
SET RECOVERY FULL;Example 3: Adding a new data file
This example adds a new data file named MyDatabase_Data2 to the MyDatabase database.
ALTER DATABASE MyDatabase
ADD FILE (
NAME = MyDatabase_Data2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 2GB,
FILEGROWTH = 100MB
);Example 4: Enabling Read Committed Snapshot Isolation (RCSI)
This example enables RCSI for MyDatabase. Any running transactions will be rolled back.
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;Important Considerations
- Always back up your database before performing significant
ALTER DATABASEoperations. - Understand the implications of changing the recovery model, especially regarding transaction log management and backup strategies.
- Be cautious when changing file paths or sizes, as this can impact database availability if done incorrectly.
- Test
ALTER DATABASEoperations in a non-production environment first.