SQL Server MSDB size checklist

11 June,2011 by Jack Vamvas

Maintaining the SQL Server msdb size should be a regular DBA task. The exact tasks to complete , depend on how you use SQL server

 A typical question I receive is:

 “The MSDB database has grown till 10 GB and now I'm experiencing no more space on disk C:.”

Note: There are different situations where you won’t want to delete history. Plan ahead – and only apply these procedures in accordance with system policies

 

Exceute SP_DELETE_BACKUPHISTORY 

A system stored procedure . Maintains the size of backup and restore history tables.

It takes one argument : @oldest_date

Execute SP_PURGE_JOBHISTORY

A system stored procedure. The option of deleting all job history records  or the history of one job

Execute SP_MAINTPLAN_DELETE_LOG

Clears up the Maintenance Plan History . This undocumented store  procedure is the equivalent of the “Delete” button on the Maintenance GUI

Possible scenarios include:

 1: User wants to delete all logs

2: User wants to delete all logs older than X date

3: User wants to delete all logs for a given plan

4: User wants to delete all logs for a specific subplan

5: User wants to delete all logs for a given plan older than X date

6: User wants to delete all logs for a specific subplan older than X date

 

Read More

SQL Server - Get datetime values with agent_datetime function and msdb..sysjobhistory

List failed SQL server Jobs with Powershell

SQL Server Agent Job Steps

SQL Server agent fixed roles

SQL Agent Jobs – Schedule in seconds




Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server MSDB size checklist


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer