Post reply

Auto growth

  • August 8, 2007 at 12:04 am

    #373139

    Hi,

     i've install sqlserver2005 on windows 2003. i have created some database n enable auto growth.the intial size of the database is 5mb but when datasbase size reached 5mb it is not growing automatically.how should i do the enable auto growth now.Help me

    Everybody's  help is appreciated

  • August 8, 2007 at 1:41 am

    #725368

    What makes you say it is not growing? Any errors?

    SQL will grow a file to a certain size and you will see the MDF will be 5MB, even though the tables in it are only using say 3MB.

     

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 8, 2007 at 3:15 am

    #725382

    Hi crispin,

       i enabled unrestricted autogrowth on database.after growing to 5mb. it is not growing automatically.The space available on database is 0MB.

     

    Regards,

    ajay

  • August 8, 2007 at 11:50 am

    #725501

    What is your growth rate set at?

    Others don't agree, but my opinion is to turn off autogrow. I don't like the fact that SQL Server can take more hard drive if it needs it. Too much potential for other worse problems. A big one that comes immediately to mind is fragmentation files due to multiple growths.

    Having unused space in a database isn't necessarily a bad thing.

    Live to Throw
    Throw to Live
    Will Summers

  • August 8, 2007 at 1:07 pm

    #725518

    Will speaks wisely.

     

    To add to his reason of SQL taking over, we had a serious issue a while back where SQL filled up one of the disks. This disk, amongst other files groups, was the master file. It was 100% full with nothing left.

    We could not add another file because SQL needed to grow master to add the row. Catch 22.

    It was mostly my fault as I failed to monitor it properly but also some moron in the team who enabled growth on the files. Catching me out.

    But yes, auto grow, especially in small amounts, is a bad thing for many reasons.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 8, 2007 at 10:39 pm

    #725608

    hi,

    database .mdf files are not expanding .even i enabled auto growth.can someone tell what would be the problem & provide me the exact solution for this.

     thanks,

    ajay

  • August 9, 2007 at 12:26 am

    #725621

    As per my first post - what vare the errors? What makes you think it should expand?

    Let's face it, there are many many reasons why it would not auto grow. Short of giving you a long check list of every possible reason, which would be long, no one would be able to give a reason without simply shooting in the dark.

     

    Give us the errors and we might be able to help.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 9, 2007 at 3:24 am

    #725653

    I would like to know if you have set the autogrow settings for each file. (one for the primary data file and one for the primary log file).

    If yes then what is the error message logged in Error log and SQL Profiler?

    What is version of the SQL Server 2005?

    Is there any dump file ?

    From KB :Q305635 " Do you receive any " Time out message " Check the log

    This may happen When a connection issues a Data Modification Lanaguage (DML) statement that requires additional space in a database, SQL Server tries to automatically expand the database, if you have the autogrow option set. The client that issued the statement may time out while the database is expanding, depending on the query timeout value that is configured on the client side. Several applications use a default timeout value of 30 seconds. This timeout ends the client connection and the automatic expansion of the database that it prompted.

    More info :http://support.microsoft.com/kb/315512

    http://support.microsoft.com/kb/305635/en-us

    Please get us the exact error message we will try to help you

    Minaz

     

     

    "More Green More Oxygen !! Plant a tree today"

  • August 9, 2007 at 6:49 am

    #725696

    As asked, what are the settings?   I suspect you are trying to grow more than you have space to grow.  Can you grow manually as is usually preferred. 

    I do use autogrow, but I do it as a fixed value vs a % and I set a cap on space. 

  • August 9, 2007 at 8:37 am

    #725744

    I would just size my database correctly and then turn autogrow off and move onto something else.

    My opinion only, but you are spending time trying to get something working that shouldn't be done in the first place.

    Autogrow is a nice idea in theory, but in practice it just shouldn't be used. If you are concerned about running out of space, then setup some kind of check that alerts you when the free space gets below a certain point. That's a topic for another thread though . . .

    Live to Throw
    Throw to Live
    Will Summers

Viewing 10 posts - 1 through 9 (of 9 total)

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