[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

The housekeeping task of MSDB

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
marrowyung
Asked:
marrowyung
  • 3
  • 3
1 Solution
 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
marrowyungAuthor Commented:
let me check
0
 
marrowyungAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Jason Yousef, MSSr. BI  DeveloperCommented:
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
 
marrowyungAuthor Commented:
but it seems that the min(run_date) and  max(run_date) is the same.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
did you deleted everything?

The query works good in my environment.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now