Post reply

How to programmatically increase size of a database.

  • August 23, 2006 at 9:07 am

    #169993

    Requesting help!

    I have an alert setup to let me know when Error 1105 File Group is Full is true.  I'd like to be able to setup an ACTION associated with this alert that will run code to increase the size of the database by 10% automaticly, if that can be done.  Although the db is set to grow by 10%, without limits, we have another software package running that sometimes interferres with autogrowth.  So, when the alert is true, I'd like to somehow force the db to grow by 10%.

    Any help would be most appreciated!

    Thanks

  • August 23, 2006 at 9:41 am

    #656404

    have you had a look at the alter database command?

     

  • August 23, 2006 at 9:47 am

    #656406

    Yes, but I'm unsure how to apply/use it.

  • August 24, 2006 at 4:28 am

    #656522

    BOL:

    E. Modify a file

    This example increases the size of one of the files added to the Test1 database in Example B.

    USE master
    GO
    ALTER DATABASE Test1 
    MODIFY FILE   (NAME = test1dat3,   SIZE = 20MB)
    GO
     

  • August 24, 2006 at 4:33 am

    #656523

    I guess you could dump the contents of sp_helpfile into a table, strip the text out of the size field, and then run the alter database on that basis.

    Probably an easy way, but this would work...

  • August 24, 2006 at 11:18 am

    #656668

    I want to first thank you all for your help and support!  Them I'd like to explain where I am with this issue as I (we) still need help.  Below is a sample of the code we have to date and it still does not work.  You'll note that it does answer my first question correctly as it does compute the current size of the db and multiply it by a 10% increase.

    Create Table #dbStats (

    [name] [nvarchar](255),

    [fileid] [smallint],

    [filename] [nvarchar](500),

    [filegroup] [nvarchar](255),

    [nvarchar](255),

    [maxsize] [nvarchar](255),

    [growth] [nvarchar](255),

    [usage] [nvarchar](255)

    )

    Insert into #dbStats ([name], [fileID], [filename], [filegroup], , [maxsize], [growth], [usage])

    exec sp_helpdb_B SharePoint_Test

    DECLARE @txtsize nvarchar(255)

    DECLARE @sizetype nvarchar(255)

    DECLARE @intsize int

    DECLARE @newtxtsize nvarchar(255)

    SELECT @txtsize = size FROM #dbStats WHERE usage = 'data only'

    SET @sizetype = RIGHT(@txtsize,3)

    SET @intsize = CAST(LEFT(@txtsize,LEN(@txtsize)-3) AS INT) * 1.1

    SET @newtxtsize = CAST(@intsize AS VARCHAR(10)) + @sizetype

    SELECT @newtxtsize

    drop table #dbStats

    USE master

    GO

    ALTER DATABASE SharePoint_Test

    MODIFY FILE   (NAME = SharePoint_Test, SIZE = @newtxtsize)

    GO

     

    Where it fails is on the MODIFY FILE   (NAME = SharePoint_Test, SIZE = @newtxtsize) line, SQL Server will not accept a variable for Size=

    Also, in order to make the above work at the level it does, my team mate (who deserves much of the credit for the above) changed the sp_helpdb so that it only outputs 1 table instead of two.

    So, here is the question: does anyone have any ideas as to how to have Sql accept a variable for the Size??

    Thank you again for your help!

     

  • August 24, 2006 at 12:57 pm

    #656690

     

    have you tried something like this example?

    declare @strSQL varchar (512)

    declare @newtxtsize varchar(8)

    set @newtxtsize = '475MB'

    set @strSQL = 'ALTER DATABASE Northwind

    MODIFY FILE   (NAME = Northwind_log, SIZE = ' + @newtxtsize + ')'

    print @strSQL

    exec (@strSQL)

     

  • August 24, 2006 at 2:04 pm

    #656710

    THANK YOU!

    With the final post (above), we have been able to successfuly build the job and associate it to the alert!

    Thank you all!

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

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