none
DB Maintenance Plan RRS feed

  • Question


  • I have the below tasks in my maintenance Plan:
    -reorganize index
    -shrink database with truncate only.
    -dbcc checkdb
    -history cleanup
    -shrink log file [dbcc shrinkfile(db1_log,10)

    What should be the sequence?
    Also I could see the shrink database task failing as its conflicting with the log backup schedule.

    So I would also like to include 2 other tsql statement tasks to enable & disable the log backup job using 'exec sp_update_job'.
    Planning to include this step before & after shrink operation.
    disable log backup job -> dbcc shrinkdatabase -> enable log backup job
    The log backup job should be re-enabled even when the shrink task fails\succeeds.
    While defining the workflow between tasks, there are three options in the value list(success, failure, completion).
    Will it work if I go for 'completion' in this case?

    Friday, August 30, 2019 2:47 PM

Answers

  • Hello Friend,

    Your process should not include a shrink. You must maintain tlog backups to 'clean up' the .ldf file and have backup for base restore needs. Keep the .ldf file at a stable size so you do not need to periodically shrink it.

    You can keep Reorganizer (or rebuild, perhaps online depending on your version and the objects that will be in rebuild);
    Checkdb;
    history cleanup.

    - Keep the tlog backups.

    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    • Proposed as answer by Jefferson Silva DBA Friday, August 30, 2019 6:50 PM
    • Marked as answer by SSG92 Saturday, August 31, 2019 11:28 AM
    • Unmarked as answer by SSG92 Wednesday, September 4, 2019 6:13 AM
    • Marked as answer by SSG92 Wednesday, September 4, 2019 6:14 AM
    Friday, August 30, 2019 3:29 PM