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

  • Ed7

    SSCrazy Eights

    Points: 8352

    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

  • ricardo_chicas

    SSCarpal Tunnel

    Points: 4958

    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: 8352

    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

    SSCarpal Tunnel

    Points: 4958

    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: 8352

    Thank you and very appreciated.

  • digitalox

    SSCommitted

    Points: 1829

    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

    SSCarpal Tunnel

    Points: 4958

    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: 8352

    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.

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

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