Hi Eric

Thanks for repling my previous question on BCP.

Could u tell me what is the standard practise for production databases with regards to AUTOGROW option. Currently I am maintaining the BUAT / DEV servers in which I have set AUTOGROW option TRUE for all the databases, so the maintenance is very easy as I never get log full mesgs. ( Later I execute a job to truncate and shrink the db periodically )

But I have read in P & T articles that MS SQL adds a new piece of disk space, the more pieces it adds to the db there is going to be a performance decreases (???)

Could u suggest me how to deal with this

Thanks in advance

Sudhakar

-- Anonymous, October 29, 2001

Answers

Sudhakar,

The fragmentation has little effect on performance since access to the data is not typically sequential anyway.

The main decrease in performance is during the actual growing process. To avoid this, make sure that your database is large enough and the grow percentage is large enough so that your databases are not growing too often. For instance, it is probably reasonable to set the original size of the database to be large enough to contain 5 years worth of data and set the autogrow to 10%. I have seen these guidelines successfully implemented on production systems.

Hope this helps,

Eric

-- Anonymous, November 02, 2001