The Multifunctioning DBA

Jun 22 2011   11:00PM GMT

Check DB online before running Job



Posted by: Colin Smith
Tags:
DB Online
T-SQL

I was also asked to make sure that I have jobs set up on my servers that have database mirroring set up. They only want the job to run if the database is online and they do not want to have to enable and disable jobs when failovers occur. So I did the following.


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:”";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

declare @online int

set @online = (select state from sys.databases

where name = ‘Database_name’)

if @online = 1

begin

raiserror (‘Database is not the current principal. not attempting to backup.’, 16,1)

End

else

Print ‘The Database is online’

So you can see that this is also pretty simple. I just check in sys.databases to make sure that the database of interest is online. If not then I have the step fail and I have the job set to quit the job reporting success. That way I do not see a bunch of job failures when it actually did just what I wanted. If the database is online then the step succeeds and moves on to step 2. This way everyone is happy.

hope this helps

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: