Solved

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

Posted on 2013-01-17
7
303 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
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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:ScottPletcher
ScottPletcher 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now