October 16, 2012 at 11:54 am
Hello
We have one existing database. currently it allocate 95 GB space for Data File. Now i need to expand space from 95 GB to 120 GB. so How can i do that,
can I use ALTER DATABASE command or other way.
Please give me query or proper guidance to handle this.
Thanks in Advance.
October 16, 2012 at 12:11 pm
USE master;
GO
ALTER DATABASE yourdb
MODIFY FILE
(NAME = logical file name,
SIZE = size you wantMB);
GO
see http://technet.microsoft.com/en-us/library/bb522469(v=sql.100).aspx
you can also do it via SSMS, right click the db, go to properties - files and specify the new file size there. Do this at a quite time for the system especially if increasing by a large amount. If you have windows 2008 or above you should have instant file initialisation configured for the service account, google that. If the service account has sysadmin it will already have that right. This will make data file growths instantaneous (not log file growths)
If autogrow is enabled for the database the file will grow automatically when it fills anyway, but best practice is to pre-empt thipreemptng manually. Make sure any autogrowth setttings are set sensibly (not percentages and not small MB growths)
---------------------------------------------------------------------
October 16, 2012 at 12:20 pm
george sibbald (10/16/2012)
If autogrow is enabled for the database the file will grow automatically when it fills anyway, but best practice is to pre-empt thipreemptng manually. Make sure any autogrowth setttings are set sensibly (not percentages and not small MB growths)
Thanks for Your reply.
currently, The Auto growth Enable for this database. The File Growth is 500MB and Restricted File Growth is 95GB.
The Initial size for this database has 94,644 MB.
so if i want to add space i need to change in Initial Size and Restricted File Growth right ?
October 16, 2012 at 12:26 pm
correct. Restricted size needs to be >= to the size you want to increase to.
---------------------------------------------------------------------
October 16, 2012 at 12:29 pm
george sibbald (10/16/2012)
correct. Restricted size needs to be >= to the size you want to increase to.
Ok, so that means
if i set up Initial size to 119.644 MB. then Restricted size will be 120 GB.
right?
Thanks for Your Help.
October 16, 2012 at 1:45 pm
Really need to get an idea of the current file sizes, post the results of this please run against your database
USE YourDB
GO
select name
, size / 128 AS FileSize
, case max_size
when -1 then 'Unlimited'
when 0 then 'No growth'
when 268435456 then '2TB'
else CAST(max_size / 128 AS VARCHAR(20))
end AS Maxsize
, case is_percent_growth
when 0 then CAST(growth / 128 as VARCHAR(20)) + 'MB'
when 1 then CAST(growth as VARCHAR(20)) + '%'
end AS Growth
from sys.database_files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 16, 2012 at 1:51 pm
Perry Whittle (10/16/2012)
Really need to get an idea of the current file sizes, post the results of this please run against your database
USE YourDB
GO
select name
, size / 128 AS FileSize
, case max_size
when -1 then 'Unlimited'
when 0 then 'No growth'
when 268435456 then '2TB'
else CAST(max_size / 128 AS VARCHAR(20))
end AS Maxsize
, case is_percent_growth
when 0 then CAST(growth / 128 as VARCHAR(20)) + 'MB'
when 1 then CAST(growth as VARCHAR(20)) + '%'
end AS Growth
from sys.database_files
Here is the results
name FileSize Maxsize Growth
Stars_Data 94644 95000 500MB
Stars_Log 17607 20000 500MB
October 16, 2012 at 2:20 pm
yogi123 (10/16/2012)
george sibbald (10/16/2012)
correct. Restricted size needs to be >= to the size you want to increase to.Ok, so that means
if i set up Initial size to 119.644 MB. then Restricted size will be 120 GB.
right?
Thanks for Your Help.
as a minimum. There does not have to be any direct correlation between them. Set it to something that makes sense for available space on the drive, or remove it entirely if it makes no sense in your environment.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply