Post reply

Rebuilding indexes in system database

  • March 2, 2011 at 7:42 am

    #232917

    Hi there,

    A quick question - is there anything inherintely wrong with including master, model & msdb into maintenance plans that rebuild database indexes?

    thanks, Mark

  • March 2, 2011 at 7:53 am

    #1293513

    Basically, it wouldn't be needed. You may need to run a dbcc checkdb regularly on system databases to verify the integrity of the system dbs. But regarding rebuilding indexes on system dbs, we barely change the data in that. It may be useful for msdb as it would get updated with details of job history etc but not in the case of the other 2 databases-master and model. Tempdb is out of the picture anyways.

    M&M

  • March 2, 2011 at 1:03 pm

    #1293706

    Master and model, no point. The tables in them are system databases. Msdb may be necessary, nothing wrong with rebuilding indexes on it

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2015 at 10:21 am

    #1842125

    The accidental DBA before me had they server level default fill factor setting at 20. I have fixed all fill factors in user databases but am getting results in tables such as sysrowsetcolumns and syshobtcolumns with the 20 fill factor (along with about 30 other system indexes). How to fix?

  • November 24, 2015 at 10:25 am

    #1842126

    As noted, msdb is the main concern here.

    But it's possible you might need to do rebuilds in model if you add/adjust tables in there, especially since you'd want the model db to be properly tuned given its role as the starting point for all future dbs.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • November 24, 2015 at 10:34 am

    #1842130

    I normally use SQL Admin Toolset by Idera to change fill factors. System databases are not analyzed with this tool. So script out all the indexes in msdb and rebuild online with new fill factor?

  • November 24, 2015 at 11:00 am

    #1842137

    Are these not read-only tables in the Resource database? http://blog.sqlauthority.com/2008/07/27/sql-server-mssqlsystemresource-resource-database/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply