Improve company productivity with a Business Account.Sign Up

x
?
Solved

The housekeeping task of MSDB

Posted on 2012-04-10
6
Medium Priority
?
732 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
  • 3
  • 3
6 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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