February 17, 2010 at 8:43 am
I've been given the task of summarising maintenance plan/job activity across several servers (2000 and 2005).
I have been able to find most of the information I need with the script at the end of this post (Don't think the script is particularly relavent to this question)
The two pieces of information I am missing are:
-Where the maintenance plan produces a backup file, what is the disk target?
-Where the maintenance plan cleans up files, after what time does it clean them?
Can anyone suggest where or if I can track down this information?
So far I have searched:
dbo.sysdbmaintplans
dbo.sysdbmaintplan_jobs
dbo.sysdbmaintplan_history
dbo.sysdbmaintplan_databases
dbo.sysdtspackages90
sp_help_maintenance_plan
sp_help_job
The script so far is:
USE msdb
Go
SELECT
DISTINCT
dj.job_id,
dj.name JobName,
suser_sname(j.owner_sid) AS [OwnerName],
j.owner_sid,
h.step_name StepName,
dbo.udf_schedule_description
(ss.freq_type,
ss.freq_interval,
ss.freq_subday_type,
ss.freq_subday_interval,
ss.freq_relative_interval,
ss.freq_recurrence_factor,
ss.active_start_date,
ss.active_end_date,
ss.active_start_time,
ss.active_end_time)
AS ScheduleDscr,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus
FROM sysjobhistory h
inner join sysjobs j
ON j.job_id = h.job_id
INNER JOIN dbo.sysjobschedules sjs
ON j.job_id = sjs.job_id
INNER JOIN dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id
--INNER JOIN master.dbo.syslogins l
--ON j.owner_sid = l.sid
INNER JOIN (SELECT DISTINCT job_id, [name] FROM sysjobs)AS dj
ON dj.job_id = h.job_id
WHERE h.step_name <> '(Job outcome)'
GO
February 17, 2010 at 9:55 am
The thread: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e2d90691-74be-4886-9adf-93962e9253a1
Claims "The maintenance plan package detail information in msdb is encrypted. I am guessing that it is stored as encrypted XML."
Anyone like to confirm or deny? 🙂
February 17, 2010 at 10:25 am
The Databases and Files Catalog View sys.backup_devices contains a list of backup devices.
Further if no backup device is specified in the backup command the:
BACKUP DATABASE AdventureWorks
TO DISK = ’AdventureWorks.bak’;
GO
Note:
The default location is stored in the BackupDirectory registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer.
Hope this helps to some extent
February 18, 2010 at 4:17 am
The solution came courtesy of http://jahaines.blogspot.com/
It uses XQuery to pull the data out and will only work if the package data is not encrypted.
;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
FROM(
select name as PackageName, CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) as PackageCode
from msdb.dbo.sysdtspackages90 main
--where main.name = '<MyPackageName>'
)AS cte CROSS APPLY cte.PackageCode.nodes('/DTS:Executable/DTS:Executable/DTS:Executable/DTS:ObjectData/SQLTask:SqlTaskData') v(x)
;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.sysdtspackages90 main
--WHERE main.name = 'MySSISPackageName'
)AS cte
CROSS APPLY cte.PackageCode.nodes('/DTS:Executable/DTS:Executable/DTS:Executable/DTS:ObjectData/SQLTask:SqlTaskData') v(x)
February 18, 2010 at 6:23 am
--Crude way
Try running below mentioned query and try to output only the latest execution of plan and you will get your data in columns named 'command' and 'line4'.
select * from sysmaintplan_logdetail
You can link it with sysdtspackages90 to find info for particular maintenance plan.
MJ
February 18, 2010 at 8:18 am
Cool. This is useful as WITH XMLNAMESPACES is not an option on my 2000 dbs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply