Which SQL server version do you use ?
Please read this post from Paul Randall - a SQL Guru and the guy who wrote the Shrink Database code. It talks about the negative effect of performing a Shrink on your SQL Database (with a nice example script that demonstrates the index fragmentation ) - and specifically advises to avoid it to the maximum extent possible.
I'm using SQL 2005 Standard edition.
Thank Sailendra Pamidi for the link.
below please find a link to a sql maintenance and backup script. This can be run via the Windows task scheduler with an appropriate account. This account must have access to the SQL server (e.g. a domain admin).
In all scripts replace %eposervername% and %sqlservername% with your system names.
Thank for the script. But do you have any experience doing database maintanance by using SQL agent. I'm just realize that automate task can be achieved if we are using SQL agent.
Do you have any experience with SQL agent.
oh sorry, I missed that you are using a full sql version.
If so you can use the "Database maintence wizard" inside the SQL Enterprise Management Tool.
As you already mentioned the SQL server agent must be running to regularly execute the plan.
We are using this in any full SQL server installation without problems.
As Sailendra already said shrinking has a negative performance effect.
With my "small" sql knowledge I would think that regularly rebuildung the index would remove this effect.