Solved

The housekeeping task of MSDB

Posted on 2012-04-10
6
706 Views
Last Modified: 2012-05-09
Dear all,

I found out that if I use the SSIS method to create the history cleanup task, which is only designed for MSDB, I can generate the following t-SQL script:


declare @dt datetime select @dt = cast(N'2012-03-13T19:21:30' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2012-03-13T19:21:30'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2012-03-13T19:21:30'

But it seems that it "Hard code" a date for me and I can't see how can I make the script dynamic so that the current date is the current data everyday. So I can script this one to safely remove historical data older than 2 weeks.

Any ideas?

DBA100.
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 37827448
Hi, can you use  " dateadd(WK, -2, getdate()) " to get you the date for 2 weeks ago?

something like that

DECLARE @dt DATETIME = dateadd(WK, -2, getdate())

exec msdb.dbo.sp_delete_backuphistory @dt

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date= @dt

EXECUTE msdb..sp_maintplan_delete_log null,null,@dt

Open in new window


in a shceduled sql agent job ..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 37850655
let me check
0
 
LVL 1

Author Comment

by:marrowyung
ID: 37879207
how can I check how many days of history I have kept on the MSDB?  I have to use it to verify what I have purged .
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37880637
Well, you can visualize the "run_date" column from the  msdb.dbo.sysjobhistory

or something simple like that...

SELECT min(run_date),  max(run_date) FROM  msdb.dbo.sysjobhistory
0
 
LVL 1

Author Comment

by:marrowyung
ID: 37884835
but it seems that the min(run_date) and  max(run_date) is the same.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37886919
did you deleted everything?

The query works good in my environment.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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