We are planning to create c# jobs that perform some database maintenance actions such as backup, shrink files etc…
I have read in many blogs that database shrink is not recommended and thats why I'm posting here in order to have some recommendations to this topic.
We want also to rebuild / reorganize the indexes. This is an easy one and I already wrote the C# script for it.
The backup for the databases and shrink for the transactions log should be also done. The databases have recovery model: Full.
Some blogs recommend this order:
- Backup the database
- Change recovery model
- Shrink the .ldf file
- Re-set the original recovery model
some others recommend this not to do shrink at all, are to use recovery model: simple.
My questions are:
- How often should we rebuild or reorganize indexes in the sql tables?
- Is shrink for log files a good idea?
- In which order should we do the database backup / logs backup / logs shrink?
I don't have enough DBA experience, so it will be great if you can give me some hints.
Thanks
Best Answer
Aaron Bertrand is highly regarded in the SQL community, he is asking about the source to understand the reason behind shrinking the logs. There are very few cases where shrinking a log file is a sound process. In the question you ask if it is a good idea, and then in the reply you cite it is a requirement, which implies there is no room for discourse.
2) As a rule of thumb you do not shrink a log file, unless you understand why you should, or the technical details of the log file. If you are to shrink a log file, it should especially not be based on a time-frame or arbitrarily dictated.
The point is made Aaron or DBA01 would be amiss to give instruction that contradicts sound advice.
Basically you are only causing issues for the database, that often have a performance impact for the users, and will likely cause you or DBA01 additional grief.
Your questions elicit the golden default SQL answer 'It Depends'. You lack enough information for someone give you the advice you seek.
1) Rebuilding indexes is an offline operation, typically done when there is 30% fragmentation, when this will occur is based on how often you are updating said indexes. Reorgs are online operations, typically done after ~5% fragmentation but to do this every 30 minutes would also be futile. There are maintenance plan walk-throughs you could look up.
Aaron also hints at a larger issue, why C#? If anything it would be better to decouple your application from the database. One approach might be to use the tool set provided and then call the agent job or process from your C# application.
3) It Depends ... What are you trying to accomplish with your backups, RTO? RPO? which one is the focus, what are acceptable data losses?