creating objects in master or msdb database

Question

creating objects in master or msdb database

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
April 8, 2017 at 11:51 am

#322856

Hi all,
I am creating a simple job that does the following
1) create a table xxx that record the current jobs and their created time + modified time.
2) everyday, it will compare this table with the actual sysjob table matching the job with its created and modified time
3) if there is a difference, send email alert.
Just wondering, if it is okay this table xxx to be created in the master or msdb database ? what would be the good practise on the location of such table ?
Regards,
Noob

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

A better practice would be to create a small "utility" database for such things.

To be honest, though... you don't need a separate table to do comparisons with. Jobs have both a created date and a modified date. For you daily check, just have the proc check for either of those dates changing in the last 24 hours and have a "morning report" job send you an email with a list of jobs that have a created or modified date within the last 24 hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

I also use "utility" databases for such purposes. You'd better go with such one, but if you insist on master or msdb, then look to keep it small and simple.

Igor Micev,My blog: www.igormicev.com

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 3

Jeff Moden - Saturday, April 8, 2017 6:55 PM
A better practice would be to create a small "utility" database for such things.
To be honest, though... you don't need a separate table to do comparisons with. Jobs have both a created date and a modified date. For you daily check, just have the proc check for either of those dates changing in the last 24 hours and have a "morning report" job send you an email with a list of jobs that have a created or modified date within the last 24 hours.

Hi Jeff,

So sorry. I totally read and miss out to reply! Yeap, i dont know what came over me to miss the part whereby i can actually just compare the created/modified date to see if there are any change within the past x period.

Thank you!