Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
Agent does this for you, if you refer to job history. It is configurable, on one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but backup history. Check
out sp_delete_backuphistory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.
|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.