0

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:

  1. Backup the database
  2. Change recovery model
  3. Shrink the .ldf file
  4. 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:

  1. How often should we rebuild or reorganize indexes in the sql tables?
  2. Is shrink for log files a good idea?
  3. 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

Improve this question
4
  • 1
    Why do you need to do this in C#? And why are you shrinking log files from anywhere, after all that reading?
    – Aaron Bertrand
    Jan 25, 2018 at 18:39
  • It is a requirement to do this with C# and i cannot change this. I dont want to shrink from anywhere, i need recommendation about the correct procedure and that why i posted my question here :)
    – alaa_sayegh
    Jan 25, 2018 at 18:40
  • Forget the requirements, you should understand (and express to whoever you need to) that using C# for this is a bad idea. If you search here you'll find plenty of questions that answer #1 and #2. The third question shows you don't fully understand recovery models so I'd go read up on them first and you'll realize why you shouldn't be shrinking.
    – LowlyDBA - John M
    Jan 25, 2018 at 19:26
  • @LowlyDBA, thanks for your feedback. From C# i will just call the sql statment that performs the backup BACKUP DATABASE xyz TO DISK = 'D:\xyz.bak'; And yes, i already stated that i don't have enough DBA experience, so it will be great if you can give me some hints :). Ok I'm now a bit sure that shrink is not the solution, however, does translation logs backup in full recovery model cuts back files growth?
    – alaa_sayegh
    Jan 25, 2018 at 19:49

1 Answer 1

Reset to default
2

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?

Improve this answer
2
  • thanks for your answer. let me clear thinks up, I asked if it is a good idea to shrink logs and I said that it is a requirement to do the work in c#, these are two different points. I just want to avoid that log files get bigger ad bigger as the application writes huge amount of data daily to the database, logging / simulation data. Accepted data loss is now in discussion,
    – alaa_sayegh
    Jan 25, 2018 at 19:26
  • Depending on the frequency of large DMLs you could use the bulk logged recovery model, or just increase the frequency of tlog backups in full recovery. This can* cut back on the amount of growth, which can save you disk space, however now you've just moved the goal as you have to store that tlog file. If the database needs large amounts of space for the log file say for large inserts, you'll run into issues with constantly shrinking the file, such as vlfs. It can be more of an art getting this balance.
    – Random_User
    Jan 25, 2018 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.