December 4, 2012 at 8:58 am
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!
December 5, 2012 at 7:51 pm
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.
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
December 6, 2012 at 2:22 pm
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!
December 10, 2012 at 5:54 pm
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
December 11, 2012 at 8:37 am
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!
December 11, 2012 at 9:41 am
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
December 11, 2012 at 3:16 pm
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 😛
----------------------------------------------------------
Gifting myself with technology related headaches since 2005!
December 12, 2012 at 2:11 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply