Post reply

MSDB heavy physical I/O and CPU in Activity Monitor

  • April 7, 2009 at 10:56 am

    #132672

    Hi Folks,

    I am playing cat and mouse with some performance issues on one of our main production servers.

    I noticed Physical I/O and CPU time for the default instance are unusually high.

    Specifically, when I look at the activity monitor, there is a process on the MSDB db with an extremely high number in CPU and I/O. Here are the details:

    ID: 69

    System: Yes

    User: SA

    DB: MSDB

    Status: Background

    OpenTrans: 0

    Command: Execute (varies... sometimes insert, etc...)

    CPU: 15516531

    PhysIO: 1905745

    We have a few SQL agent jobs on this box which execute often - one which executes every minute during production hours (damn vendor apps).

    I guess I have a couple of questions:

    1. Is this normal for the background msdb process?

    2. What is the PhysIO number measured in? (MB?)

    3. Any ideas re. how to mitigate?

    Thanks for any advice...

    Nick

  • April 7, 2009 at 1:33 pm

    #973755

    msdb also stores your backup history among other things.

    are you doing anything to purge backup history ?

  • April 7, 2009 at 1:47 pm

    #973769

    Actually I just created a new maint plan on this server and it is scheduled to clean up files after 74hrs. There are quite a few DBs in the default instance (around 45) with a total size of about 50GB.

    However, why would the db session be consuming CPU and tying up physical I/O?

    The jobs run late at night.

  • April 8, 2009 at 12:22 pm

    #974577

    This is still an issue for me... In addition, my MSDB has grown to about 10GB.

    Should I expect this after establishing a Maint Plan which runs full backups nightly for about 50 databases and tran log backups each hour for one of those dbs?

    I understand MSDB is in charge of keeping track of these backups for me, but 10gb seems like an awful lot...

    Any more words of wisdom would be appreciated.

    Thanks,

    nick

  • April 8, 2009 at 12:27 pm

    #974584

    Nick (4/7/2009)

    Hi Folks,

    We have a few SQL agent jobs on this box which execute often - one which executes every minute during production hours (damn vendor apps).

    Nick

    how long are you storing the agent job histories ?

    have you tried to run a profiler trace to see whats actually running in msdb ?

  • April 8, 2009 at 12:30 pm

    #974591

    Hmm... hadn't looked into that.

    Now that I do I see that it is set to "Limit size of job history log to:

    Max Log Size = 1000 rows and Max History rows per job = 100

    Should I change that to "Auto remove... older than x"?

  • April 8, 2009 at 12:46 pm

    #974609

    that depends on whether or not you need the history.

    that doesn't seem like a lot with the few job you have.

    run a trace to see whats going on. see where the reads are coming from.

  • April 8, 2009 at 1:01 pm

    #974621

    also run these in your msdb:

    select 'sysjobhistory', count(*) from sysjobhistory with (nolock)

    select 'sysjobs', count(*) from sysjobs with (nolock)

    select 'sysjobactivity', count(*) from sysjobactivity with (nolock)

    select 'sysjobsteps', count(*) from sysjobsteps with (nolock)

    select 'sysjobstepslogs', count(*) from sysjobstepslogs with (nolock)

    select 'sysjobschedules', count(*) from sysjobschedules with (nolock)

    select 'backupmediaset', count(*) from backupmediaset with (nolock)

    select 'backupmediafamily', count(*) from backupmediafamily with (nolock)

    select 'backupset', count(*) from backupset with (nolock)

    select 'backupfilegroup', count(*) from backupfilegroup with (nolock)

    select 'backupfile', count(*) from backupfile with (nolock)

  • April 8, 2009 at 1:16 pm

    #974632

    sysjobhistory498

    sysjobs 26

    sysjobactivity427

    sysjobsteps28

    sysjobstepslogs0

    sysjobschedules18

    backupmediaset13596

    backupmediafamily13596

    backupset38492

    backupfilegroup38481

    backupfile76992

    So... is every backup ever done tracked in the "backup..." tables? Seems pretty large.

  • April 8, 2009 at 1:21 pm

    #974636

    if you're not cleaning it up then i think yes.

    select min(backup_finish_date) from backupset

    how much you keep depends upon your needs and system's ability to handle it.

    i keep a year and use the stored backup sizes to monitor database growth over time.

  • April 8, 2009 at 1:44 pm

    #974663

    Well... looks like there has never been any cleanup.

    2006-02-27 18:27:52.000 is min backup_finish_date

    Thanks for your patience folks... new to this.

    So, in my new Maint Plan I have a "Cleanup Task" which I assumed would remove the physical backup files after nn days / hours, etc...

    How do I purge the records from the MSDB?

    Thanks again,

    Nick

  • April 8, 2009 at 1:46 pm

    #974665

    sp_delete_backuphistory

  • July 1, 2015 at 9:21 pm

    #1809709

    I have this script setup as a step within a frequently executed SQL Server Agent maintenance job. It prunes backup history older than 90 days. Just replace the 90 with how ever many days works for you:

    [font="Courier New"]/*

    Purges backup history from the MSDB database that is older than 90 days.

    Use this to identify the oldest backup recorded in the MSDB database: select min(backup_finish_date) from backupset

    */

    DECLARE @sql as nvarchar(max)

    DECLARE @DatePurge as datetime

    SET @DatePurge = CAST(GETDATE() - 90 AS datetime)

    SET @sql = ''

    Set @sql = '

    use msdb

    exec sp_delete_backuphistory ''' + LEFT(CONVERT(VARCHAR, @datepurge, 120), 10) + ''';'

    exec (@sql)[/font]

Viewing 13 posts - 1 through 12 (of 12 total)

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