SQL SERVER: T-SQL to find out when database was restored

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database. We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found: SELECT    * FROM    msdb.dbo.RestoreHistory WITH (nolock) WHERE    destination_database_name = '<Database Name>' ORDER BY restore_date DESC Reference: Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Clear Job History

Recently, we have implemented few jobs and we were testing the jobs. After our test runs successfully, we wanted to clear Job’s history, so we can have accurate job status. I have found following SQL to delete job’s history: USE msdb GO EXEC dbo.sp_purge_jobhistory @job_name = '<Job Name>' This will clear all history for the specified job. If you wanted to clear job history up to specific date, you can use following: USE msdb GO EXEC dbo.sp_purge_jobhistory @job_name = '<Job Name>' , @oldest_date='2012-10-10' If you wanted to clear job history for ALL SQL jobs, you should just execute: USE msdb GO EXEC dbo.sp_purge_jobhistory Reference: Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Configure notification to send an email when JOB fails

In my previous post, T SQL to display Job history, I have mentioned to get list with the job statuses. One of my developer asked me to share, How to configure Notification to send an email when Job fails. Developers problem is, he is getting empty dropdown while configuring notification as shown in below figure: Here, This dropdown contains list of Operators, so we have to create operators to whom we can send a notification email. Its very simple to configure operator, see below: Now, we have created Operator, so, lets set it up for the job as below: Now, Mail will be send to the email specified in “ErrorEmails” operator. Note: Make sure to Configure Database email to send an email from the SQL SERVER. Reference: Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: T-SQL to display Job history

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed. We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query: SELECT     j.name As JobName,     run_date As RunDate,     RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,     CASE h.run_Status            WHEN  0 THEN 'Failed'         WHEN 1 THEN 'Succeeded'         WHEN 2 THEN  'Retry'         WHEN 3 THEN 'Cancelled'     ELSE ''     END AS JobStatus,     RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS FROM msdb..sysjobhistory h INNER JOIN msdb..sysjobs j on h.job_id = j.job_id     AND h.step_id = 0 ORDER BY j.Name, h.run_date Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows: We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments Reference: Tejas Shah (http://www.SQLYoga.com)