Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Precendence Constraint Logical AND/OR Confusion


Precendence Constraint Logical AND/OR Confusion

Author
Message
DatacenterCras
DatacenterCras
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 35
Hi All,

I am wet behind the ears with all things SQL and I'm currently just beginning to study for my MCDBA. I'm working on some notify operator tasks for a maintenance plan and I'm running in to a few issues...

See the photo attached: I currently have failures pointing to a notify operator with a logical OR option, so if anything fails, it will tell me. The success has a logical AND so that the entire plan/task must succeed for me to be notified. Currently I have some superficial error I can't figure out that's sending me a failure even though overall the job is completing and I cannot find any error relating to the problem in the log file viewer.

What would be the best practice for this setup? The error is not actual harming my backups (whatever the error may be), so would it be best to switch the success to all logical OR and the failures to logical AND? Just looking for some advice, like I said I am very new to this and have a large learning task ahead of me!

Thanks in advance for the help :-)

----------------------------------------------------------
Gifting myself with technology related headaches since 2005!
Attachments
SQLPlan.JPG (30 views, 80.00 KB)
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
Ut oh. Are you drawing a maintenance plan? If so, here are some comments.

1 - Break plans into small pieces since they need to be scheduled at different times.

2 - Never use the shrink database as part of a scheduled plan. This should be a one time operation that you watch run if you ever do it.


Here are some suggestions.

A - Create plan to do a full backup of all user databases and then clean up *.bak files > 4 weeks old.

If databases are small, schedule every night.

B - Create plan to do a differentail backup of all selected databases and then clean up *.bak files > 4 weeks old.

This is for larger databases. Schedule the full plan on Sunday nite, schedule the differential plan on Mon-Sat.

C - Create plan to do a log backup of all user databases and then clean up *.trn files > 4 weeks old.

There is where you choose how much data loss the business line is willing to accept. A hourly log backup is saying that you business is willing to lose 1 hr of data.


D - Size your databases correctly. Current size + delta. Grow by MB, not size. The log, if backed up regularly and does not have massive data input should not grow.

E - Do not forget to repeat steps A & B for system databases.

F - Do not forget to have at least 4 data files for tempdb.

If you are interested in plans created by SSIS, I am doing a series of articles on my blog.

http://craftydba.com/?p=3145

Also, take a look at http://ola.hallengren.com/, ola hallengrens backup scripts if you decide to use TSQL.

Good luck with your MCDBA exam. I did mine serveral years back.

John Miner
Crafty DBA
www.craftydba.com
DatacenterCras
DatacenterCras
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 35
Thank you for the insight, I'll have to take a close look and break this plan down a little bit. As of now the largest few databases are around 50GB each but there are only 3 to 4 of those out of around 60 DBs. Overall the plan could use a good revamping so that's something I need to work on in the coming days.

As for the precedence constraints, am I missing something? I feel like I understand it, but it just doesn't seem to work for me.

----------------------------------------------------------
Gifting myself with technology related headaches since 2005!
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
The precedence contraints are the lines. The maintenance plan is actual an SSIS package under the covers.

There are three types of lines: green (good status), red (bad status), and blue (completed action).

John Miner
Crafty DBA
www.craftydba.com
DatacenterCras
DatacenterCras
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 35
I understand the different constraints, just not why I'm not getting the notification at the end of my plan. I'm continuing to troubleshoot now.

----------------------------------------------------------
Gifting myself with technology related headaches since 2005!
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
First and foremost is DBMail set up and does the SQLAgent service account have rights to send mails?

You should be able to check in the SQLAgent logs look for something around the time the job finishes, though for the life of me I cant remember the error message, it will be something like SMTP mail failed for User (dont quote me!!).

_________________________________________________________________________
SSC Guide to Posting and Best Practices
DatacenterCras
DatacenterCras
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 35
Hi Jason,

Yes DBMail is enabled and the account is configured. If I send a test email through DBMail is goes through without a problem. The operators I'm using are also set up with the proper address. Though for some reason I'm not getting a notification at the end of my plan. I use a linking success constraint through each part of the plan that says "Logical AND", so everything in each individual task must evaluate as "successful" for it to continue the plan. Now the plan finishes properly (at least I have the backup files I need) but I get no notification at the end.

I was thinking it must be that there is some superficial error earlier in a phase of the plan that's stopping it from continuing, but if it weren't continuing, I wouldn't have the backups I have. Semi-confusing I know Tongue

----------------------------------------------------------
Gifting myself with technology related headaches since 2005!
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
I'm familair with the constriants, everything looks like it should send the final email or an error email.

The problem is that if you are logged on as you it will connect as you and send an email as though its you, which isnt the same.

Thats why I think you have a problem with the SQLAgent connecting to the DBMail.

I think you might need to set up the Account SQLAgent runs under to be a member of the role DatabaseMailUserRole in the MSDB database.

Grant the permission and restart the SQLAgent.

Theres a simple way to check create a simple 1 step job that does a TSql Select 1 from a database

Set up the Notification at the job level to send an email on success and execute this package through the Activity Monitor, dont run it through the jobs, you should receive an email.

If not check the Log files under SQLAgent and you should see an error being raised in there.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search