Solved

housekeeping of the MSDB

Posted on 2012-04-10
6
667 Views
Last Modified: 2012-08-17
Dear all,

AS the MSDB keep all the:

1)Backup and restore history,
2)SQL server agent job history.
3)Maintenance plan history.

and if we do not purge that regularily, the backup task will run longer and longer.

Any script to run on how many days of all of the above kept so that we know how many days only history we should keep?

DBA100.
0
Comment
Question by:marrowyung
6 Comments
 
LVL 21

Assisted Solution

by:huslayer
huslayer earned 125 total points
ID: 37829754
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 125 total points
ID: 37830077
We do this cleanup as a step in our Maintenance plans, not as a separate job, but there's no reason you couldn't. Also, the amount of data generated is relatively small - while I'm not saying it's a good idea to never maintain it, it's small compared to the logging data generated elsewhere on your server, and I've never seen a case (even on servers that aren't maintained at all) of a large MSDB or out of control agent logs slowing down job execution.

Maybe I'm sheltered, but I wouldn't put too much stress on this - adding a "Maintenance Plan Cleanup" task, as mentioned above, should accomplish what you're looking for, even if you keep a few months worth of logs.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 37830649
1)  sp_delete_backuphistory
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 42

Accepted Solution

by:
EugeneZ earned 125 total points
ID: 37830786
0
 
LVL 1

Author Comment

by:marrowyung
ID: 37850659
let me check and get back to you all soon.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 38307279
we don't consider this option right now ! it should be the housekeeping job for the rest of the DB instead of the MSDB one.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now