Solved

Is it a good idea to include "Shrink db Task" in the backup plan?

Posted on 2013-01-17
7
311 Views
Last Modified: 2013-02-01
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.
0
Comment
Question by:Castlewood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 167 total points
ID: 38787758
Shrink DB is not a regular maintenance task, this is recommended only if you have disk space problem and you did huge deletes or truncates from your tables or after you drop huge tables. The reason that shrink DB is not recommended is because,
-  it is very resource consuming
- another reason is that it fragment your indexes and makes performance problems.
- The space recovered using shrinkdb may be neededby your database at some point in future when your database grows, so again in that case your database will grow again.

So when you shrink, defragment your indexes also, but its not a routine task that a DBA performs.
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 167 total points
ID: 38787765
Microsoft outlines these best practices:

Best Practices
Consider the following information when you plan to shrink a database:

•A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

•Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

•A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

•Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

More information can be found here:

http://msdn.microsoft.com/en-us//library/ms190488.aspx
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38787880
Please DONT shrink your production databases, whickh is resource intensive and held locks for long time; more over once you shrink, it will regrow.

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/
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Castlewood
ID: 38787970
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?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38787989
>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.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38787992
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.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 38788967
>> Check Db Integrity <<

You need to check db integrity at least once a week, but if you have the resources, it's a good idea to do it daily.  Just to be sure to put a process in place to notify someone if an error is detected.  I've seen some places diligently running checks every day, but never noticing errors there for WEEKS because no one was checking it.


>> Update Statistics <<

How often this needs done depends on your specific data and query requirements.

Current statistics are extremely important for SQL to generate well-performing queries.

If the data is very volatile and critical, you might need to update stats several times a day.

If data is much more static, you may need to it only every few days.

If the table is large, DON'T rely on auto update of statistics.  Instead, do your own periodic update of stats at a low-use time, using FULL SCAN if you can afford the time to do that.

Bottom line: you really do need to create separate maintenance plans for updating statistics, with different dbs and tables in each one, with their own separate schedules.  It's not as quick-and-dirty as the one plan, but it is the only way to do it properly with MPs (technically it's better to use your own code rather than maintenance plans, but you may not someone who can code and maintain that, in which case MPs are fine, and much better than not doing stats updates).
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question