When does AUTOSHRINK kick in in MS SQL SERVER 2k

[koolestj]

I ask this question because we have a server in production. It is a std. edition and is only accessed by an application in the local machine. I am wondering when the application has the database connection open through ODBC and is continously writing to the database, does the autoshrink ever kick in?
I would like to know so that I can factor it in to the overall performance of the application with SQL Server.
Thanks.

 

[JayKusch]

Well ... you might want to reconsider using AutoShrink if your statement of "... and is continously writing to the database."

If this is the case, you a hindering the performance of you server. Here is why. When AutoShrink runs, it does shrink your database. BUT ... as you continue to write, you are causing the database to grow in what we call extents. The growth is either based on a percentage or a set amount like 100MB. Either way, there is overhead when it grows. Now if you have it grow by a small % or a small set size like 5MB and your DB is rather active, you could be extending the DB quite often an incurring a major resource hit.

The best way to handle this is to determine what the
"reasonable" size of the DB should be based on expected growth over a set time and grow it out to that size.

This not only reduces the number of extents having to be created, which should be very few, but it also builds the DB file in a more contiguous manner which is optimal for DB operations like reads, updates, deletes ...



Thanks

J. Kusch

 

[nigelrivett]

Turn off autoshrink on production systems.
It takes a lot of resources and usually the database will just grow again which takes even more resources.

Pick a size which doesn't require growing too often and leave it at that. If you need to administer sizes then do it via a script rather than letting the server do it automatically.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.