How do create a t-sql script that will auto increase my current SQL Server database size to 50%.

Question

Post reply

How do create a t-sql script that will auto increase my current SQL Server database size to 50%.

Ed7

SSCrazy Eights

Points: 8516
More actions
March 13, 2014 at 1:41 pm

#284585

Questions:
How do create a t-sql script that will auto increase my current SQL Server database size to 50%.
For instance, the current database COSTDB 100 GB and I wanted to auto increase 50% every five month.
My first script will return me the current database size:
SELECT DB_NAME (Database_id) AS Database_name,
Name as logical_name, Physical_name, (size*8)/1024 SizeDB
FROM sys.master_files
where DB_Name (database_id) = 'COSTDB'
Go
My second scripts should increase the current database size. However, i stuck in here.
ALTER Database COSTDB
MODIFY FILE (COSTDB_DATA, SIZE = ?? MB)
GO
Can someone helps please and very appreciated.
Thank you.
Edwin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 1

Hi, first question, why do you want to do that?

I dont see why you are stuck in there, have you tried something like

Declare

@sql nvarchar(500)

set @sql=N'alter database......'+newsize+' mb)'

execute sp_executesql @sql

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 2

Personally, when I create the database. I will allocated enough space to grow for minimum 3 to 5 years.

However, this database applications already existed and it was the MSFT healthcheck recommendation.

Can you provide the complete scripts?

I am going to setup SQL Agent job to run this script on Demand.

Much appreciated your help.

Thank you.

Edwin

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 3

Sure

this should work

Declare

@Newsize int,

@sql nvarchar(500),

@filename nvarchar(100)

SELECT @Newsize=((size*8)/1024)*1.5,@filename=name

FROM sys.master_files

where DB_Name (database_id) = 'COSTDB'

and type=0

set @sql=N'ALTER Database COSTDB

MODIFY FILE (name=''' +@filename+''', SIZE = '+cast(@newsize as nvarchar(10))+' mb)'

execute sp_executesql @sql

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 4

Ed7

SSCrazy Eights

Points: 8516

March 13, 2014 at 3:31 pm

#1696972

Thank you and very appreciated.

digitalox SSCommitted Points: 1830 More actions · Answer 5

You can also set the database's autogrow property. Right click on the database, click properties, select files and click on the ellipsis for the data file in the Autogrowth column. Setting autogrow at 50% of the size is probably not a great idea though but you can set to something more sane and then not have to worry about running script.

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 6

yes the autogrowth will be my choice too, you can just set it to grow to something that is not a percentage ( I found that like a bad idea )

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 7

The best practice should create the database and allocate database size based on proper planning and estimate grow for minimum 3 years.

I won't let the application create the database.