Castlewood
asked on
Is it a good idea to include "Shrink db Task" in the backup plan?
In our company backup plan, we have the following tasks prior to the Full db backup task:
Check Db Integrity (for all db, include indexes)
Shrink Db (for all db, Limit: 50mb, Free space: 10%)
Update Statistics (for all db, Object: Tables and views, All existing statistics, Full scan)
The problem is, these three tasks hours to complete -- sometimes the Shrink Db even takes 24 hours, which causes the Full db backup to be pushed back so much. This makes just no sense. So can you tell me what I should do?
Thanks.
Check Db Integrity (for all db, include indexes)
Shrink Db (for all db, Limit: 50mb, Free space: 10%)
Update Statistics (for all db, Object: Tables and views, All existing statistics, Full scan)
The problem is, these three tasks hours to complete -- sometimes the Shrink Db even takes 24 hours, which causes the Full db backup to be pushed back so much. This makes just no sense. So can you tell me what I should do?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys for the prompt replies. Okay, pretty much I got the idea for Shrink now. But how about the other two tasks:
Check Db Integrity (for all db, include indexes)
Update Statistics (for all db, Object: Tables and views, All existing statistics, Full scan)
Is it a good idea to have the above two tasks running prior to the nightly db backup?
Check Db Integrity (for all db, include indexes)
Update Statistics (for all db, Object: Tables and views, All existing statistics, Full scan)
Is it a good idea to have the above two tasks running prior to the nightly db backup?
>Is it a good idea to have the above two tasks running prior to the nightly db backup?
I would say once a week should be enough.
I would say once a week should be enough.
You should run the CHECKDB for all your database both system and user, this allows you to check for any possible corruption in the database.
Update statistics is fine if you have disabled the auto update stats, if you enabled auto update stats then this is not needed as FULL scan option takes time to complete if your tables are huge.
Update statistics is fine if you have disabled the auto update stats, if you enabled auto update stats then this is not needed as FULL scan option takes time to complete if your tables are huge.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/