I have inherited a number of SQL Server 2005 and 2008 systems (all running in Simple recovery mode) that each currently do the following things each night from an SQL Agent task:
- Backup
- Shrink
- Reindex
I have stopped the shrink from happening as this is likely to be causing more problems than it is solving. (The usage of the server is to store a consistent amount of information on a rolling 5 day basis, so the space from deleted rows will be used again for fresh information without much growth).
However, I do not know whether I should also stop the nightly re-index job. It was necessary when the shrink was active because of the massive index fragmentation that shrinking causes. I cannot find much information on the internet suggesting when a nightly re-index might be sensible. Should I disable this too?