October 16, 2012

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

SQL Yoga T-SQL to find out when database was restored

Reference: Tejas Shah (http://www.SQLYoga.com)

October 15, 2012

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)

October 11, 2012

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:

SQL Yoga Configure notification to send an email when JOB fails #1
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:

SQL Yoga Configure notification to send an email when JOB fails, create Operator #2
Now, we have created Operator, so, lets set it up for the job as below:

SQL Yoga Configure notification to send an email when JOB fails #3
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)

October 10, 2012

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:

SQL Yoga T-SQL to display job history


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)