SQL server database automate size increase.


  • I wanted to know is there any way can do automation  like there is an X database 3GB with autogrowth 250MB for the datafile & consider 2.6GB is increased & I dont want to wait for autogrowth to trigger instead the drive I have enough space in it instead I want to increase the database file size itself from 2.6GB to 5GB..

    Consider some condition when the database data file reached 85% full then automatically should get increase by check the drive free space(where the db file resides) has 30% of free space & my increment for the data file would be consider 10GB.

    excuse Me if not clear this ,but just requirement basic script to make automate on this-but Iam not getting on this, I knew that first should ensure drive space should be there & review weekly (or) may be Iam incorrect.

    Regards, S_NO "_"

    Wednesday, July 11, 2018 4:17 PM

All replies

  • The easiest way would be to pre-allocate the datafile. If you still prefer to do it the way you desired, a dirty way would be something as follows:

    1) Write the total, used, free space and free space in percentage numbers to a SQL table. You can use PowerShell etc to do this. 

    2) Once you have the used and total space for the drive in question, calculate the 30% free space. For example, 30% of a 100 GB drive would be 30 GB so this drive can only be used up to 70 GB. Hence, calculate the intended size to increase as follows:

    SpaceToAddToDataFile (Say, x)= 70 GB - Used drive Space (got from step 1)

    3) Once you have the value of X, get the current data file size and add x to it. 

    ActualSpace (Say, y) = Current data file size + x

    4) Run the following with y as the SIZE.

    USE [master]
    ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'Test_Auto', SIZE = <y> )

    Of course, you can automate everything and club in 3-4 job steps and schedule using SQL job

    Caution: if the drive contains any other data files, they would still grow from the remaining 30% free space that would defeat your purpose of leaving 30% free on the drive. 

    You're better off testing the application in a lower environment and coming up with a good number to increase your data file so you don't have to rely on alerts or auto growth. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, July 11, 2018 6:27 PM
  • Yes logic is fine,but Iam not an programmer background and need if some one has scripts from first to last-- it helps a lot & appreciated on that.

    Regards, S_NO "_"

    Thursday, July 12, 2018 9:21 AM