(I originally asked as a regular question but then found out the correct method - thanks BrentO)
No, never.
I've come across this several times now on ServerFault and want to reach a nice wide audience with some good advice. If people frown on this way of doing things, downvote and I'll remove this gladly.
Auto-shrink is a very common database setting to have enabled. It seems like a good idea - remove the extra space from the database. There are lots of 'involuntary DBAs' out there (think TFS, SharePoint, BizTalk, or just regular old SQL Server) who may not know that auto-shrink is positively evil.
While at Microsoft I used to own the SQL Server Storage Engine and tried to remove the auto-shrink feature, but it had to stay for backwards compatibility.
Why is auto-shrink so bad?
The database is likely to just grow again, so why shrink it?
- Shrink-grow-shrink-grow causes file-system level fragmentation and takes lots of resources.
- You can't control when it kicks-in (even though it's regular-ish)
- It uses lots of resources. Moving pages around in the database takes CPU, lots of IO, and generates lots of transaction log.
- Here's the real kicker: data file shrink (whether auto- or not) causes massive index fragmentation, which leads to poor performance.
I did a blog post a while back that has an example SQL script that shows the problems it causes and explains in a bit more detail. See Auto-shrink – turn it OFF! (no advertising or junk like that on my blog). Don't get this confused with shrinking the log file, which is useful and necessary on occasion.
So do yourselves a favor - look in your database settings and turn off auto-shrink. You should also not have shrink in your maintenance plans, for exactly the same reason. Spread the word to your colleagues.
Edit: I should add this, reminded by the second answer - there's common misconception that interrupting a shrink operation can cause corruption. No it won't. I used to own the shrink code in SQL Server - it rolls back the current page move that it's doing if interrupted.
Hope this helps!
Best Answer
Yes you need to update the statistics manually, especially as your database gets bigger.
The auto_update statistics logic only kicks in after the table has grown by 20% from what it was the last time the statistics were updated. In larger tables this can be hundreds of millions of records. In these cases the statistics will become out of date before the auto update logic kicks in so you'll have to update them manually.
Now if you rebuild your indexes on a regular basis, then the stats for the index would already be updated as this is done as part of the index rebuild process. If you defrag your indexes instead of rebuilding them then you will need to update statistics as the updating of statistics is not done automatically when you defrag the index.