May 16, 2014 at 2:17 pm
yeah this is now what we are keep doing everyday. the On-call DBA manually takes the backups of all the DBs (about 20). it is working without any issues.
Also the Underlying T-SQL on Maintenance plan works perfectly whenever I run it on SSMS.
Also I tried to backing up into a Local drive without going through network. Still NO LUCK.
I am just ran out of ideas now.
May 17, 2014 at 11:22 am
What is going on in activity monitor when the agent job is running? It must be in some state, ie running or suspended. If suspended what is it waiting on? when the sql agent job is running, fire up activity monitor locate the spid and screenshot it, then post it here.
Also, have you tried creating a brand new database, doesn't need any data, and backing that up via a maintenance plan on a schedule? give that a go and see what happens.
Let us know
May 17, 2014 at 11:27 am
Perry Whittle (5/16/2014)
can you post details of the maintenance plan and job history
No history at all ??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 19, 2014 at 7:11 am
nimalatissa (5/16/2014)
yeah this is now what we are keep doing everyday. the On-call DBA manually takes the backups of all the DBs (about 20). it is working without any issues.Also the Underlying T-SQL on Maintenance plan works perfectly whenever I run it on SSMS.
Also I tried to backing up into a Local drive without going through network. Still NO LUCK.
I am just ran out of ideas now.
on-call shouldn't have to run this every night, just create a agent job with the backup script, this will get you through for now. Have you looked at the error.log file in the sql program files folder? This will give us some clues as to what is going on. can you post it?
MCSE SQL Server 2012\2014\2016
May 19, 2014 at 7:26 am
nimalatissa (5/16/2014)
Thanks for you reply.Yeah, we thought about adding the Scripts to a Job and run it. My Manager did not like that idea at all.
Is your manager a DBA? Maintenance plans are for people who are not DBAs, as far as I am concerned. The reason I do not use them (and most DBAs do not) is because of the exact problems you are having. In fact, I would rather create my own SSIS packages so that I have control vs using a maintenance plan. If you manager is not a DBA, you need to explain that scripting these things is infinitely better than a maintenance plan.
Jared
CE - Microsoft
May 19, 2014 at 4:51 pm
I spoke to may manager about creating SSIS packages for handling this situation. His answer was why all other 50+ servers are working fine and not this one. He knows I am good at SSIS and I guess he needs to give me a challenge;-) ? NOT SURE.
But here is the Error I noticed today on SQLAGENT.OUT(C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log)
2014-05-19 15:38:46 - ! [136] Job MaintenancePlan.Subplan_1 reported: Unable to terminate process 21c8 launched by step 1 of job 0x7040CEA21A27A841BD8201E4D9C15E12 (reason: Access is denied)
I'm not sure what this Process is ? 21c8, I do not see any SPID with this
May 19, 2014 at 6:03 pm
nimalatissa (5/19/2014)
I spoke to may manager about creating SSIS packages for handling this situation. His answer was why all other 50+ servers are working fine and not this one. He knows I am good at SSIS and I guess he needs to give me a challenge;-) ? NOT SURE.But here is the Error I noticed today on SQLAGENT.OUT(C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log)
2014-05-19 15:38:46 - ! [136] Job MaintenancePlan.Subplan_1 reported: Unable to terminate process 21c8 launched by step 1 of job 0x7040CEA21A27A841BD8201E4D9C15E12 (reason: Access is denied)
I'm not sure what this Process is ? 21c8, I do not see any SPID with this
When you create the plan and schedule it, it creates it under your account unless you change it, I believe. Make sure the job owner is sa and that your service account running the agent has permissions to write to the drive. I assume it does, since this worked before. I have seen this fail when someone who used to work for the company no longer does, and their account gets inactivated. Than all of the sudden, jobs start failing.
Jared
CE - Microsoft
May 20, 2014 at 1:24 am
There'll be some history somewhere. Have you thought of logging the job step output to a text file?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 20, 2014 at 3:40 am
Perry Whittle (5/20/2014)
There'll be some history somewhere. Have you thought of logging the job step output to a text file?
Taking another opportunity to steer the user (and every SQL Server DBA out there that may read this in the future) Ola.Hallengren.com's maintenance suite includes several options for logging. Just sayin' ... 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2014 at 4:13 am
TheSQLGuru (5/20/2014)
Perry Whittle (5/20/2014)
There'll be some history somewhere. Have you thought of logging the job step output to a text file?Taking another opportunity to steer the user (and every SQL Server DBA out there that may read this in the future) Ola.Hallengren.com's maintenance suite includes several options for logging. Just sayin' ... 😎
I do agree with TheSQLGuru, I use Ola.Hallengren's script for our SQL estate, for backing up the databases, reindexing, consistency checks etc, I thoroughly recommend it as a fully documented and supported solution.
I am curious as to the cause of the user's problem here though, and it does look like permissions issue from the earlier post.
May 20, 2014 at 7:46 am
TheSQLGuru (5/20/2014)
Perry Whittle (5/20/2014)
There'll be some history somewhere. Have you thought of logging the job step output to a text file?Taking another opportunity to steer the user (and every SQL Server DBA out there that may read this in the future) Ola.Hallengren.com's maintenance suite includes several options for logging. Just sayin' ... 😎
Exactly, furthermore there'll be some history somewhere 😉
Just sayin 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 20, 2014 at 8:00 am
nimalatissa (5/19/2014)
I spoke to may manager about creating SSIS packages for handling this situation. His answer was why all other 50+ servers are working fine and not this one. He knows I am good at SSIS and I guess he needs to give me a challenge;-) ? NOT SURE.But here is the Error I noticed today on SQLAGENT.OUT(C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log)
2014-05-19 15:38:46 - ! [136] Job MaintenancePlan.Subplan_1 reported: Unable to terminate process 21c8 launched by step 1 of job 0x7040CEA21A27A841BD8201E4D9C15E12 (reason: Access is denied)
I'm not sure what this Process is ? 21c8, I do not see any SPID with this
It's a permission problem, plan and simple as it says access denied. Check the user permissions under the maint plan. Change them to sa or to an account that has sa permissions. The plan should work after that. I highly suggest that you have a serious heart to heart with your boss and review all of your plans and talk about getting the backups into scripts and separating out plans that do to need to be lumped together.
MCSE SQL Server 2012\2014\2016
May 20, 2014 at 1:20 pm
lkennedy76 (5/20/2014)
nimalatissa (5/19/2014)
I spoke to may manager about creating SSIS packages for handling this situation. His answer was why all other 50+ servers are working fine and not this one. He knows I am good at SSIS and I guess he needs to give me a challenge;-) ? NOT SURE.But here is the Error I noticed today on SQLAGENT.OUT(C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log)
2014-05-19 15:38:46 - ! [136] Job MaintenancePlan.Subplan_1 reported: Unable to terminate process 21c8 launched by step 1 of job 0x7040CEA21A27A841BD8201E4D9C15E12 (reason: Access is denied)
I'm not sure what this Process is ? 21c8, I do not see any SPID with this
It's a permission problem, plan and simple as it says access denied. Check the user permissions under the maint plan. Change them to sa or to an account that has sa permissions. The plan should work after that. I highly suggest that you have a serious heart to heart with your boss and review all of your plans and talk about getting the backups into scripts and separating out plans that do to need to be lumped together.
Great googly moogly, that's what I said on the fourth post on this thread. It's almost always security settings.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2014 at 3:29 pm
Thanks for all the Reply. I had tried the underline Job of this maintenance plan running with "sa" before I post it.
Even now I tried the same thing. it is still the same result. the Job runs forever and the same kind of error with different process name appears on the Error Log.
Also there is no member fired\laid off or did not even resign from our team 😀
BTW. I really Like this forum and this is very active comparing to the ones I had posted before. Great Job guys. please keep the community informed...!:-)
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply