June 5, 2008 at 3:04 pm
Hi,
How I can get the last maintenance action information for a database instance programmaticall by SMO. The last maintenace action may be log backup, full database backup, etc. What I mean is that I want to know what kind of action the last maintenance is and it is success or failed.
Any suggestion would be high appreciated,
Mark
June 5, 2008 at 3:14 pm
Are you running these maintenance tasks as jobs in SQL Agent?
If so, you can right-click a job and check its history. (Or query it from msdb, I believe.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 3:23 pm
Thank you very much for your information. However some tasks may be done not by Agent. Also I have to grab those information programatically via SMO (Sqlserver Management Object). Could you please give me more information about that?
And you just mentioned that I can get those information by querying against database, then which table contains those information, and do you have any query on your hand? If so, could you please post the query?
Thanks again,
Mark
June 5, 2008 at 4:11 pm
You can use the properties 'LastBackupDate' (date of last full backup) and 'LastLogBackupDate' (date of last log backup).
I don't see anything available to identify a differential backup though, if you need that.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2008 at 7:08 am
Thanks a lot. The information is so helpful for me.
One more question, if the last maintenance task is not Backup how can I exactly know it's detail? Can I programmatically get the last task's information from somewhere like log file? If so, could you please let me know which class or property I should use to parse log file.
Thank you once again,
Mark
June 6, 2008 at 3:29 pm
I am not sure what you are asking for. I don't know of any properties that keep track of maintenance plans in SMO. You could look at Microsoft.SqlServer.Management.Smo.Agent to see if there is anything there, but this would not get you anything related to a task in a maintenance plan.
With that said - you could always run a query to get that information:
;WITH LogDetail (task_detail_id, row)
AS (
SELECT task_detail_id
,ROW_NUMBER() OVER(ORDER BY start_time DESC)
FROM msdb.dbo.sysmaintplan_logdetail
)
SELECT m.line1
,m.line2
,m.line3
,m.line4
,m.line5
,m.start_time
,m.end_time
,m.error_number
,m.error_message
,m.command
,m.succeeded
FROM msdb.dbo.sysmaintplan_logdetailm
INNER JOIN LogDetaill ON l.task_detail_id = m.task_detail_id
WHERE row = 1;
HTH,
Jeff
Edit: tried to fix the spacing in the code block. Cut & Paste just doesn't seem to work right all the time 😀
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2008 at 4:02 pm
You can query msdb..backupset for information about what table has bene backed up when.
select Database_Name, Physical_Device_name, Case When type = 'D' then 'Full'
When type = 'L' Then 'log'
When type = 'I' then 'Differential' end as Database_Type,
Backup_Start_Date,
Backup_finish_date
from msdb..backupset a
join msdb..backupmediafamily b
on a.media_set_id = b.media_set_id
where Backup_finish_date > getdate() - 1
June 10, 2008 at 11:27 am
Thank you guys' replys very much, I hope I can get one more chance to state my intention. Sorry for the confusions.
I am implementing a web application (back-end is SqlServer) that needs below informations got presented on a page:
1. Last Database Maintenance Job Date and Status (Success or Failed)
2. Drive Space Available (Drive where Data File is located)
3. Percent(%) of Drive Space Free
For the last two items, I guess I can get them done by using WMI or entended Stored Procedures. But for the first one, I have no idea so far. I didn't find any useful information from SMO document(I was suppose that I can get it done by using SMO).
The complexity comes from the fact that I cannot know exactly what kind of action the last maintenance is. Because "Last Database Maintenance" may refer to a action like Server Memory Change, databse backup or log backup ... . I am imaging all maintenance actions would be put into somewhere by Sqlserver, and the only thing I should do is to grab the last record from there then show the record out.
Hope you guys can understand me more this time. Any help would be high appreciated. 🙂
Mark
February 8, 2024 at 11:55 pm
Hi Can i get a script for to all the Database Maintenance Jobs in sql which stop executing after 8 am .
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply