February 17, 2010 at 12:22 pm
Could you please help me out on this.
I am trying to query the table from msdb which would give me the retention period of the backup files of maintenance plans.
Is it there on some table, if you already know, please share with me. Thanks.
M&M
February 17, 2010 at 1:02 pm
If you mean when the file will be deleted by the Cleanup Task in the maintenance plan, I think you can only find that in the plan itself, not in any system table or view.
If you mean the expiration date i.e. when the file can be overwritten, that stored in the dbo.backupset system table in msdb.
Greg
February 17, 2010 at 1:50 pm
SELECT [task_detail_id]
,[line1]
,[line2]
,[line3]
,[line4]--Displays Age: Older than 4 Weeks for my cleanup task
,[line5]
,[server_name]
,[start_time]
,[end_time]
,[error_number]
,[error_message]
,[command]
,[succeeded]
FROM [msdb].[dbo].[sysmaintplan_logdetail]
HTH!
Manu
February 17, 2010 at 4:20 pm
Greg Charles (2/17/2010)
If you mean when the file will be deleted by the Cleanup Task in the maintenance plan, I think you can only find that in the plan itself, not in any system table or view.If you mean the expiration date i.e. when the file can be overwritten, that stored in the dbo.backupset system table in msdb.
Yes Greg, I was looking for when the file will be deleted by the cleanup task in the maintenance plan.
Thanks for letting me know.
M&M
February 17, 2010 at 4:21 pm
MANU-J. (2/17/2010)
SELECT [task_detail_id],[line1]
,[line2]
,[line3]
,[line4]--Displays Age: Older than 4 Weeks for my cleanup task
,[line5]
,[server_name]
,[start_time]
,[end_time]
,[error_number]
,[error_message]
,[command]
,[succeeded]
FROM [msdb].[dbo].[sysmaintplan_logdetail]
HTH!
Manu
Manu, Just a question. When I ran this on my instance. There is just one maintenance plan and 2 tasks.
But this query gave me a 18 row output. I was not able to figure this.
M&M
February 17, 2010 at 5:19 pm
Moin,
It is because its maintenance plan log table which logs every execution information. Would be better if you just order by end_time desc and check the first few rows(in ur case two as you have onl two tasks in ur plan). You will see the retention information clearly in output.
Hope it helps you in getting retention information.
Manu
February 17, 2010 at 5:43 pm
Thanks Manu
M&M
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply