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.
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.
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.
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