SQL Script to modify the database file size

Leave a comment (0) Go to comments

Script that modifies the data file size for a specific database file.

SQL Script to modify the initial size of a specific database file

-- ***********************************************************************************/
-- Description  :    SQL Script to modify the initial size of a specific database file
--                    Script that Script to modify the data file size for a specific database  
-- Compatibility  :     7.0+
-- This Script should be executed in specific database or master
-- ***********************************************************************************/
ALTER DATABASE <database_name> MODIFY FILE 
(NAME=logical_db_name , SIZE=size[ KB | MB | GB | TB ])
go

Example


Current database file size information, which you can check by right click on database and say properties

SQL Script to modify the database file size Script to modify database file size query for dbfile size SQL Server Change initial log size script Change initial datafile size ALTER DATABASE modify file ALTER DATABASE File and Filegroup Options

You checked the current database file size and identified what should be the new size of the

Modify the Database file size using TSQL statement

-- Getting Information about Database files like logical name , location and size.
sp_helpfile
go
--- Modifying the Initial size of a database logfile to 12 MB, in this example
ALTER DATABASE Northwind MODIFY FILE 
(NAME=Northwind_log , SIZE=12MB)
go

Once, this is down, we can again check graphically or by TSQL statement, here is the output

SQL Script to modify the database file size Script to modify database file size query for dbfile size SQL Server Change initial log size script Change initial datafile size ALTER DATABASE modify file ALTER DATABASE File and Filegroup Options

EOF - SQL Script to modify the database file size, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.