Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Increase database size

    I have a database that is over 45 GB and need to increase the size, can someone help me with the code to get it done?

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    From Books Online:


    ALTER DATABASE database
    { ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
    | ADD LOG FILE < filespec > [ ,...n ]
    | REMOVE FILE logical_file_name
    | ADD FILEGROUP filegroup_name
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILE < filespec >
    | MODIFY NAME = new_dbname
    | MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
    | SET < optionspec > [ ,...n ] [ WITH < termination > ]
    | COLLATE < collation_name >
    }

    < filespec > ::=

    ( NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = 'os_file_name' ]
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] )
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you fill in a few blanks first? Normally a database will grow as needed, until it fills the available disk space. Is your database refusing to grow (a setting), or running out of free disk (meaning you need to start using a new drive for it to grow more)?

    Another question is which part of your database is growing, the data or the log? It might be as simple as needing to do a log backup.

    This question makes me leary... There are way too many ways to get into trouble if you don't understand this kind of problem!

    -PatP

  4. #4
    Join Date
    Jul 2004
    Posts
    191
    The problem was that the log was NOT set to automatically grow. It's growth was restricted. The person that was in charge of the database before me realize the problem. I tell you it is a long process to becoming a GOOD DBA. God help me.

    Lystra

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How long does the backup take?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2004
    Posts
    191
    About 40 minutes, it about 33GB in size. That reminds me, when you backup a database are you suppose to get back log space? I thought you get it back when you use the with truncate_only.

    Thanks

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big are the ldf and mdf files on the server?

    The ldf, once it's grown, will not shrink, unless you have autoshrink on, which is not a good idea.

    It's also not just the backup, you have to dump the transaction logs. That will "empty out" the log, but leave the log at the size it has grown to...which is good in a well maintained databse because it figures it will grow to that size again, so you don't want to to incur the overhead of shrinking a growing all the time, which is overhead.

    BUT, if it's not well maintained, you probably should dump the log, and use DBB SHRINKFILE or SHRINKDATABASE

    Look them up in BOL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •