Share via

Rebuild index in SQL server system database

Zahid Butt 556 Reputation points
2020-10-20T12:14:14.51+00:00

Hi,

I just want to confirm , is it recommended to rebuild indexes in system database e.g. MSDB or distribution db in transactional replication.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,950 questions
0 comments
Accepted answer
  1. Shashank Singh 6,246 Reputation points
    2020-10-20T12:28:10.527+00:00

    There is no need to rebuild index of system databases definitely not for master and model database. There may be few conditions where you can rebuild index for msdb but that is rare as well. Tempdb being temporary one has no such requirement at all.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ben Miller (DBAduck) 951 Reputation points
    2020-10-20T14:31:39.483+00:00

    System databases are just that "databases" and when indexes get fragmented, you should have them rebuilt/reorganized. MSDB is the best example as backupset and all the information about jobs, etc. should be maintained. Would I obsess over it, no, but I would not ignore it. Distribution database is also a good example.

    Maintain your system databases as you would a user database to ensure that things are as they should be. It certainly doesn't hurt to maintain them.

    3 people found this answer helpful.
    0 comments
  2. Zahid Butt 556 Reputation points
    2020-10-20T12:55:39.33+00:00

    Actually 1 table from MSDB & 1 table from distribution database are fragmented up to 90% and highlighted in our net mon (performance monitoring tool).

    That is why I was asking.

  3. Cris Zhan-MSFT 6,611 Reputation points
    2020-10-21T08:35:11.26+00:00

    Hi @Zahid Butt ,

    Please refer to more discussions on this topic.
    https://www.mssqltips.com/sqlservertutorial/9299/sql-server-msdb-database-maintenance/
    https://www.sqlservercentral.com/forums/topic/rebuilding-indexes-in-system-database
    https://www.sqlservercentral.com/forums/topic/distribution-database-maintenance

    Optimizing indexes in msdb may be necessary.You can defragment the highly fragmented index in msdb or distribution db just like the user databases.

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  4. Zahid Butt 556 Reputation points
    2020-10-21T13:19:30.13+00:00

    Hi @Cris Zhan-MSFT ,

    For now I have reorganized indexes & added step on maintenance plan job as well. The issue is resolved.

    One thing to ask! When I reorganize MSDB.dbo.Sysjobhistory index "nc1" that is on job_id, its fragmentation does not go less than 66.

    Total records at that time were 879 & after short time fragmentation went up to 80.

    Please advise.

    Thanks