How To Increase Size Of SQL Database Using Management Studio And Transact-SQL

How To Increase Size Of SQL Database Using Management Studio And Transact-SQL

Description:

By default we can create SQL database with the specified size. Incase to increase the size of the database there is some procedure to follow.

Precaution:

Before perform the database size increase take the backup copy of the database.

1.Open SQL management studio
2.Select the database and right click on it.
3.Now select the backup and mention the location to backup the database.

Note: To perform the database increase need ALTER permission on Database.

SQL Server Management Studio Steps:

To expand the database size,

1.From object explorer choose  SQL Server Database Engine and connect it.
2.Now expand the databases, pickup the database which need to increase the size.
3.Right click the database and go to properties.
4.Choose File page, To increase existing file size increase the value (size should in MB) in file. DB size increase minimum is 1 MB.Maximum depends on SQL version.
5.If you want to increase the db by using new file , click on Add option and give the size of the new file. And click OK.

Transact-SQL Steps:

1.Connect the database and go to New query window.
2.Now copy the below query and execute it,  demofile is the increase file name

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = demofile,
SIZE = 200MB);
GO

Tags:SQL,windows,SQL queries,SQL Managment Studio,Database,mysql,mytecharticle,tech news,Openstack,Database index,Query Analyzer