Defrag MASTER and MSDB

Question

Defrag MASTER and MSDB

fizzleme

SSCertifiable

Points: 6906
More actions
October 10, 2014 at 4:45 pm

#291473

On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.
Is it ok to defrag them using the standard Alter Index command?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I've never defragged either but there's nothing in Books Online under "Restrictions" when you lookup either database that says you can't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

george sibbald SSC Guru Points: 104210 More actions · Answer 2

you can but its probably pointless. A performance problem with either database is unlikely and are any of the tables large enough to benefit from defragging (> 1000 pages)

---------------------------------------------------------------------

fizzleme SSCertifiable Points: 6906 More actions · Answer 3

Thanks. I probably won't bother.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

MSDB you can, but you're probably better off making sure the job and backup history tables are correctly cleaned up. Master has no user tables (or shouldn't have any), hence nothing that you can run an index rebuild on.

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

djj SSCoach Points: 18831 More actions · Answer 5

GilaMonster (10/13/2014)
Master has no user tables (or shouldn't have any)

Thanks! I had forgotten that I did a big no-no in June on the production instance and created a table in master thinking I was on a different server in a different database. So table is now gone. :rolleyes:

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

I have seen plenty of benefit from defragging MSDB, but never in master without there being custom tables in there.

That said, usually the benefit in MSDB is around the agent and backup history tables. So as Gail said, tune your retention there.

On a side note, there are usually a couple of missing indexes in msdb pertinent to agent and backup history. The cleanup process is much better if the indexes are implemented.

Here is something that talks about that.

http://sirsql.net/blog/2011/1/25/keeping-msdb-clean.html

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

fizzleme SSCertifiable Points: 6906 More actions · Answer 7

Nice feedback. I appreciate it.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.