SQL 2008 R2 msdb 95.66% frag

Question

SQL 2008 R2 msdb 95.66% frag

TC-416047

Ten Centuries

Points: 1312
More actions
August 25, 2011 at 2:35 pm

#242275

I'm so confused about what to do about this.
I have two table in msdb that are fragmented
sysmaintplan at 95.55% and sysjobhist at 90.54%
From reading online people say don't touch system tables.
What happens if I don't defrag?

Viewing 5 posts - 1 through 4 (of 4 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Todd Canedy-416047 (8/25/2011)
I'm so confused about what to do about this.
I have two table in msdb that are fragmented
sysmaintplan at 95.55% and sysjobhist at 90.54%
From reading online people say don't touch system tables.
What happens if I don't defrag?

What you have to do? check the page_count

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

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

The tables in MSDB aren't system tables

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Reindex those tables regularly, depending on how much job, backup and maintenance plan activity you have. Here is what I do:

-- Try this on a test server before unleashing it in production

-- Also make sure you understand the implications of

-- running the two stored procedures at the end

USE msdb

-- Reindex before attempting to clear tables,

-- or it could take a long time

ALTER INDEX ALL ON backupfile REBUILD

ALTER INDEX ALL ON backupset REBUILD

ALTER INDEX ALL ON backupmediaset REBUILD

ALTER INDEX ALL ON sysmaintplan_logdetail REBUILD

ALTER INDEX ALL ON sysmaintplan_log REBUILD

ALTER INDEX ALL ON sysjobhistory REBUILD

DECLARE @time datetime

-- Choose a date older than which you want to purge.

-- We use three months

SET @time = '20110526'

EXEC sp_delete_backuphistory @time

EXEC sp_maintplan_delete_log @oldest_time = @time

-- Now run the first five ALTER INDEX statements again

Also, configure the job history log size to something realistic in the properties of SQL Server Agent.

John

TC-416047 Ten Centuries Points: 1312 More actions · Answer 4

Thanks everyone.

I really appreciate the help.

Hope you can forgive my ignorance.

-Todd