SQL Server How to alter Database

In the previous post you have learnt how to create database in SQL Server. In this post you will learn how to alter user defined databases in SQL Server.

To alter a database means changing the properties of data and log files which are created when a database is created. Altering database implies several meanings; some of them are as follows.

1. Change the file group of data file and log file
2. Change the size of database e.g. shrinking or expanding the database
3. Rename database

We use Alter Database statement to modify a database.

ALTER DATABASE statement is used to do any of the following activity.

1. to add a data file into the database
2. to add a log file into the database
3. to add a filegroup into the database
4. to remove a data file from the database
5. to remove a log file from the database
6. to remove a filegroup from the database
7. to modify the file
8. to modify the filegroup etc.

We use following clauses with alter database statement.

• ADD FILE
• ADD FILEGROUP
• ADD LOG FILE
• REMOVE FILE
• REMOVE FILEGROUP
• MODIFY FILE
• MODIFY FILEGROUP

Note that when we add a file it must be added also to a filegroup.

We use following examples to illustrate how we can change a database.

Example1.  We can expand a database by adding additional files for its growth. For example, we can add additional data file to the existing database. This is given in the following example.

ALTER DATABASE Appliedk
ADD FILE
(
NAME = appliedkDataFile3,
FILENAME = 'D:\TestDb\appliedk3.MDF',
SIZE = 25MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB
)

Example2.  To add a log file to the database, use the following alter database command with add log file clause as given in the following example.

ALTER DATABASE Appliedk
ADD LOG FILE 
(
NAME = appliedkLogFile3,
FILENAME = 'D:\TestDb\appliedk3.LDF',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 2MB
)

Example3.  To add a filegroup to the database, use the following alter database.

ALTER DATABASE Appliedk
ADD FILEGROUP appliedkFileGroup 
GO

Example4.  To add a file to the created filegroup of the database, use the following alter database command with add file clause as given in the following example.

ALTER DATABASE Appliedk
ADD FILE 
(
NAME = appliedkDataFile4,
FILENAME = 'D:\TestDb\appliedk4.MDF',
SIZE = 25MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB
)
TO FILEGROUP appliedkFileGroup

Example5.  To remove a filegroup from the database, use the following alter database.

ALTER DATABASE Appliedk
REMOVE FILEGROUP appliedkFileGroup 
GO

Example6.  To remove a data file from the database, use the following alter database.

ALTER DATABASE Appliedk
REMOVE FILE appliedkDataFile4
GO

Example7.  To remove a log file from the database, use the following alter database.

ALTER DATABASE Appliedk
REMOVE FILE appliedkLogFile3
GO

Note: The file must be empty before removing it. Use DBCC SHRINKFILE command to empty a file as given below.

Shrink Databases

To shrink a database, use DBCC SHRINKDATABASE statement. It is used to shrink a database by a target percentage it is very much similar to defragmenting a disc drive. The scattered rows in database are relocated, cleaning the space. The use DBCC SHRINKDATABASE takes three parameters as given in the following example.

Look at the screen shot to get logical file name in Database Properties.

Example1. This example shows to shrink Appliedk database by 10 percent.

USE Appliedk
GO
DBCC SHRINKDATABASE (Appliedk, 10, NOTRUNCATE)
GO

Note that SHRINKDATABASE command is followed by database name, target percentage and truncate or no truncate parameters in parentheses.

What if you want to shrink a data file of the database and not just to shrink the entire database? For this, we use DBCC SHRINKFILE command.

Example2. The following example shows how to shrink particular file of Appliedk database by 10 percentages. The first parameter is the logical file name and second one is the target size. You can use EMPTYFILE, TRUNCATE or NOTRUNCATE keyword as third parameter.

USE Appliedk
GO
DBCC  SHRINKFILE  (appliedkDataFile3, 10, NOTRUNCATE)
GO 

Look at the result below.

Example3. The following example shows how to empty a particular file of Appliedk database.

USE Appliedk
GO
DBCC  SHRINKFILE  (appliedkDataFile2, EMPTYFILE)
GO

Look at the result below.

In this post we learnt about how to ALTER Database. In the next post we will learn about how to Gather Information about Database.