0

I have entered in a new company as a DBA. I have seen they have a maintenance SP that do these steps:

1-Shrink database (this is bad!!)

2- check the indexes and rebuild those that have high fragmentation (this is good)

3- Create a script for those indexes and run it (not bad)

4- Run a shrink of the log file (is it a bad idea??)

And all these steps everyday. I will suggest to remove the shrink part and run it every weekend. What do you think about it?

Thank you very much...then I will ask you about the longs run queries.

David.

DavidLinares
  • 21
  • 1
  • 7
  • 1
    The best practice is to avoid shrinking so remove steps 1 and 4. – Dan Guzman May 28 '18 at 13:35
  • If your main question is about #4, there is a ton of info out there about log files and shrinking. It is just a Google search away... – dfundako May 28 '18 at 13:36
  • I agree, don't skrink it. I don't really understand how you can be creating so much free space on such a regular basis. – Thom A May 28 '18 at 13:42
  • Thanks. My quesionts are two: 1- Should I suggest them to remove the shrink steps? I definitely say Yes and you said that too. 2- Should I suggest them to run this SP every weekend and not everyday like they do right now? I don't know. I think yes but not sure. – DavidLinares May 28 '18 at 13:48
  • Shrinking is not good practice. If you really want your log database smaller the best and safest way to do that is to simply restart sql server at a time when there is no production – GuidoG May 28 '18 at 14:10
  • @DavidLinares, consider using [Ola's maintenance solution](https://ola.hallengren.com/) instead of maintenance plans. Those include smarter index maintenance. – Dan Guzman May 28 '18 at 14:15
  • Thanks everyone!!! @DanGuzman I will recomend to remove the step that shrink the database and then the log file. Maybe I could say to them to do the #4 step and adding TRUNCATEONLY option. The index maintenance I think it's fine and they created it so It's not bother me. Cheers!! – DavidLinares May 28 '18 at 14:33
  • @DavidLinares, As mentioned by my fellow techies and by you surely shrinking is not good practice. Although, sometimes we can use in space crisis. It will create an issue with log shipping as well. – Rajat Jaiswal May 28 '18 at 14:33
  • I think they don't have space issues. I think they never got a DBA that check that maintenance. They use replication but in ther instance. This DB is relative small, it use full recovery mode so I think they have a backup plan ( I have to check that too). Thanks @RajatJaiswal – DavidLinares May 28 '18 at 14:46
  • @DavidLinares, I would not shrink the log either as it will only need to grow again and impact performance during the grow. Shrinking a log file is only appropriate if there was a one-time activity that needed a lot of space. – Dan Guzman May 28 '18 at 14:52

0 Answers0