Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Unanswered: Performance and filegrowth

    I'm new to SQL Server 2000 so I was hoping to get advice from some of you experienced SQL Administrators.

    I have been reading about managing user database size and thought that I may be able to gain some performance from my server by changing my current server from automatically growing. i understand that the system must continually monitor the size of each file to determine is and when it needs to grow.

    I wanted to change the setting on my server under the options for:

    Space allocated for the DATA and Transaction Log files.
    Change File Growth settings
    Change Maximum File Size settings

    My question is - Can this be done while users are connected or should it be done after hours?

    Any other information that maybe helpful to me when working with the setting mentioned above would be appreciated. Again I'm an SQL newbie.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Those values are dynamic, meaning that they can be implemented while there are active connections to the database. However, if you attempt to increase preallocated space it may impact current users under certain conditions. If performance is that import and there is high activity on the database that affects its growth (or rather requires periodic growth) I'd invest some time to investigate the rate of growth and preallocate enough room so that this background process does not divert server resources during business hours. But keep in mind that this approach will force you to commit more time administering this database, because along with the above you'll have to periodically monitor used space to confirm that the results of your investigation are correct and nothing unusual happened to affect the growth of the space used. You'll also have to perform scheduled increase in preallocated space manually, and preferrably after hours.

  3. #3
    Join Date
    Mar 2003
    Posts
    8
    Hello Arra2

    I did´t agree that you will have increment in performance disabling automatic grow.

    In fact if don´t realy knows how your database grows, keep it as built
    or you will receive SQL error at front end telling that log is full.

    If you realy want increase performance use Profile to find what commands are using more CPU (CPU > 2000) or reads (reads > 30000)
    or duration (Duration > 4000). Analyse indexes e sql commands structures.

    Good Luck

    Robinson

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Disabling Auto Growth in high activity environment WILL positively affect the performance, - that's a fact. And I think Mr. Robinson is also confusing Auto Growth with Recovery Mode, which are not the same All other suggestions are actually good, so go get'em, tiger

  5. #5
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19
    Thanks to all for the help.

Posting Permissions

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