[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-01-17
7
Medium Priority
?
317 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 668 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 668 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

834 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