SQL 2012 Maintenance Clean Up Task

Question

SQL 2012 Maintenance Clean Up Task

ayemya

Hall of Fame

Points: 3495
More actions
July 2, 2014 at 1:37 pm

#293048

Hi,
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
Thank you
ayemya

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

Although there are a number of tables in msdb relating to Maintenance Plans, the Maintenance Plans themselves are actually SSIS packages so if you need to change something such as cleanup history retention period, you'll need to modify the Maintenace Plan itself; you can't do it by modifying a database table.

This topic should be in the 2012 General forum by the way.

Regards

Lempster

ayemya Hall of Fame Points: 3495 More actions · Answer 2

Thank you for your reply.

I will want to write a poweshell script to retrieve a retention of Maintenance Cleanup Task from more than 80 SQL servers. If I know the table where maintenance cleanup task configuration setting for a back maintenance plan exists, I will be able to get information not opening each maintenance plan.

Appreciated!

Lempster SSCoach Points: 15651 More actions · Answer 3

The msdb.dbo.sysmaintplan_logdetail table is the one you want to look at then.

Regards

Lempster

ayemya Hall of Fame Points: 3495 More actions · Answer 4

Thank you for your reply.

I looked select * from msdb.dbo.sysmaintplan_logdetail. I don't see Cleanup Task retention period.

ayemya Hall of Fame Points: 3495 More actions · Answer 5

I found the answer from the link.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f4c858a2-992a-410b-97b4-8bf63ee5138f/maintenance-plans-gathering-information-from-msdb?forum=transactsql

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS,'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask)

SELECT

v.x.value('(@SQLTask:TaskName)','varchar(1000)') as TaskName,

COALESCE(v.x.value('@SQLTask:BackupDestinationAutoFolderPath','varchar(1000)'),v.x.value('@SQLTask:FolderPath','varchar(1000)')) as FolderPath,

COALESCE(v.x.value('@SQLTask:BackupFileExtension','varchar(1000)'),v.x.value('@SQLTask:FileExtension','varchar(1000)')) as Extension,

v.x.value('@SQLTask:DeleteSpecificFile','BIT') AS Del_Specific_File,

v.x.value('@SQLTask:AgeBased','BIT') AS Aged_Based_Del,

'DELETE backups older than ' + v.x.value('@SQLTask:RemoveOlderThan','VARCHAR(3)') +

CASE v.x.value('@SQLTask:TimeUnitsType','VARCHAR(10)') --0=Daily,1=Weekly,2=Monthly,3=Yearly,5=Hourly

WHEN 0 THEN ' Day(s)'

WHEN 1 THEN ' Week(s)'

WHEN 2 THEN ' Month(s)'

WHEN 3 THEN ' Year(s)'

WHEN 4 THEN ' Minute(s)'

WHEN 5 THEN ' Hour(s)'

END AS Del_Freqency,

v.x.value('@SQLTask:CleanSubFolders','BIT') AS Del_Sub_Folder,

STUFF(

(

SELECT ', ' + QUOTENAME(db.i.value('@SQLTask:DatabaseName','VARCHAR(100)'))

FROM cte.PackageCode.nodes('/DTS:Executable/DTS:Executable/DTS:Executable/DTS:ObjectData/SQLTask:SqlTaskData/SQLTask:SelectedDatabases') db(i)

WHERE db.i.value('../@SQLTask:TaskName','varchar(1000)') = v.x.value('(@SQLTask:TaskName)','varchar(1000)')

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)')

,1,2,'') AS Selected_DBs

FROM(

SELECT [name] as PackageName, CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) as PackageCode

FROM msdb.dbo.sysssispackages main

WHERE main.name = 'Backups'

)AS cte

CROSS APPLY cte.PackageCode.nodes('/DTS:Executable/DTS:Executable/DTS:Executable/DTS:ObjectData/SQLTask:SqlTaskData') v(x)