MS SQL Server 2012 - DBA Articles

ALTER DATABASE - Add, Modify, Delete Secondary Data File

ALTER DATABASE keywords are used to add or modify or delete an existing database Primary Data File, Secondary Data File(s), Log Information File(s) and Database Filegroups Information. Moreover, ALTER DATABASE is used to modify the attribute of a database through SET Option.

Database snapshots cannot be modified through ALTER DATABASE T-SQL statement. SQL Server sp_replicationdboption system procedure is helps to modify a database that associated with replication options.

Example 04

Add Log Information File

The following T-SQL statement is used to add log information file in the eEducation database.

-- Add Log Information File --

USE master;

ALTER DATABASE eEducation

ADD LOG FILE

(

NAME = eEducation_New_Log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eEducation_New_Log.ldf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

Note: ADD LOG FILE keywords are used to add log information file(s) in the specified Database.

Example 05

Modify Log Information File

The following T-SQL statement is used to modify log information file size.

-- Modify Log Information File --

USE master;

ALTER DATABASE eEducation

MODIFY FILE

(NAME = eEducation_New_Log,

SIZE = 20MB);

Note: MODIFY FILE keywords are used to update logical and physical data or log file name, file path and file size information. Example 06

Delete Log Information File

The following T-SQL statement is used to delete a log information file from eEducation database.

-- Delete Log Information File --

USE master;

ALTER DATABASE eEducation

REMOVE FILE eEducation_New_Log;

Note: REMOVE FILE keywords are used to delete logical file description from an instance of SQL Server and delete empty physical data or log file.

* * * * *

Your Comment To AUTHOR