March 4, 2009 at 7:10 am
Hi all -
I'm having a problem getting my jobs to run correctly. It seems as if my agent is not connecting to the correct server and instance name for whatever reason. I tried using dns name/instance name and ip/instance name, but no such luck.
For example, I created a maintenance job and when I go and run it, I get an "local connection error", but I only see that in the SQL Server Agent\Job history portion. There is no history when I go to Maintenance Plans and right click on the maintenance plan name - which makes me believe that it is trying to run this job on another server.
This is really weird and I have no idea how I can fix it. Any help would be greatly appreciated.
Thanks in advance.
March 4, 2009 at 9:39 am
Check the service for Agent to be sure it's running under an account that has appropriate priveleges on your server.
"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
March 4, 2009 at 9:58 am
I checked and the account does seem to have appropriate permissions. I think it has something to do the the local area connection - the local area connection isn't the right server/instance name. How do I change that?
March 4, 2009 at 11:09 am
I don't know. I'm not sure you can modify it. If I look at the SQL Server Configuration Manager, I can see the settings within the Agent, but it's not editable. If you look at the Agent properties in SQL Server, it looks like you can alias the connection, but not edit it. Did you rename the server or something?
"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
March 4, 2009 at 11:24 am
The DNS changed. Is there anyway I can get things working without having to do a reinstall?
March 4, 2009 at 11:53 am
I'm not aware of one. Hang on a sec. Let me get help.
"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
March 4, 2009 at 12:11 pm
Stepping out of my area a bit, but when you say the DNS changed, could you elaborate on that some more please?
March 4, 2009 at 12:22 pm
Well, our developers use servername\instance name to connect to sql via their connection strings. And to not affect their work, we went ahead and created a new sql 2008 cluster separate of our 2005 one. So once that was built, we took the dns off of the original cluster and pointed it to the new cluster, so the developers never had to change their connection strings, even through the computer name was different. Problem with that was that the network got confused as to which computer it was supposed to point to. Now I have a problem with my maintenance plans pointing to original Local Area Connection and not the new one. I'm not sure how to fix this problem at all. I've tried creating new connections to the new computer by going "Manage Connection >> Add", but it still keeps erring out. I have two other instances within the cluster that I can run maintenance plans on, so I'm not sure what happened to this one.
Do I have to change something within the msdb database or something? I think changing the Local Area Connection will fix the problem.
Hope that explanation helps a bit...
March 4, 2009 at 12:28 pm
Does this mean the virtual servers on the 2008 cluster have the same name as on the 2005 cluster?
Also, are there any error messages in the error logs?
March 4, 2009 at 12:32 pm
When the DNS was changed, was the server name also changed?
This is what I read on another site.
affected areas:
1. linkedservers
2. replication
3. logshipping
4. applications connecting to the server
5. possibly firewall issues
basically, connection problem especially if the machinename was changed and the sqlserver name retained, so if you change the server name then change the sqlserver name too
There was a discussion kind of similar to this here
maybe that will help you
-Roy
March 4, 2009 at 12:35 pm
Someone else mentioned checking the Agent error log specifically, not just the SQL Server error log. Other than that, I'll let the reinforcements take over.
"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
March 4, 2009 at 12:41 pm
The instance names are the same, but the virtual server names are different than those of my 2005 Cluster.
And no, the server name was not changed along with the dns name. The dns is just being used as a pointer, I guess.
Basically, when I go create my 2008 maintenance plans, the local area connection is pointing to my old 2005 server and not my new 2008 one. Everything else on the instance seems fine and working. When I select @@servername, I see the correct computer name and instance name.
March 4, 2009 at 12:46 pm
I'm not seeing anything weird in the error logs, just some mail errors that don't really have to do with this.
Is it possible for my sql agent to be running these jobs on another server? As weird as that sounds, could that be what's happening?
There's no maintenance plan history on this computer and my databases indicate that they have not been backed up for a couple of days, even though my backup job supposedly ran successfully last night.
Am I crazy? LOL.
March 4, 2009 at 12:47 pm
Could it be a DNS cache issue? When you are creating the maintenance plans, are you "physically" on the servers or connecting from SSMS on your PC?
I know you hear this all the time, if on your PC, have you tried rebooting your PC? I do seem to remember that DNS mappings can get cached, but I don't know how to flush the cache offhand without rebooting, though I'm sure it can be done.
March 4, 2009 at 12:48 pm
I am thinking along the same lines as Roy here.
http://support.microsoft.com/kb/303774
You may need to do this to get things working correctly. It seems like this is an issue where "local" is not exactly what it needs to be.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply