Rebuilding indexes in system database

Question

Rebuilding indexes in system database

marklegosz

SSCrazy

Points: 2816
More actions
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

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

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 3

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?

ScottPletcher SSC Guru Points: 100948 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 5

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?

Velveeta22 Ten Centuries Points: 1289 More actions · Answer 6

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