December 30, 2010 at 12:31 pm
Cloned PRD environment to DEV, and after renaming the server, etc. I'm seeing that the SQL Agent jobs are being run from/on PRD. Also, if I delete or rename a job it deletes/renames on PRD and not on DEV. I checked MSDB.SYSJobs on DEV and all jobs have server_id=0 which according to MASTER.SYSServers=DEV.
Environments are both running the same patched level of SQL 2008 R2 on identical hardware.
I did some searches and have more or less come up empty. Anyone experience this before?
January 6, 2011 at 5:08 am
was a time you had to update sysjobs to avoid this, but sql 2008 doesn't require this. I always make the owner of the jobs sa though - make sure no dependancies - I did have a thought, are you running the jobs as a specific user? - I move msdb from prod to dr as part of my DR recovery so I know the jobs work correctly when you do a restore - not done a rename for some time so not so sure.
In the days of renaming sevrers, back in sql 2000 you had to make sure you chnaged the sql servername @@servername and also the netname if there was one.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 9, 2011 at 3:38 am
Reefchief (12/30/2010)
Cloned PRD environment to DEV, and after renaming the server, etc. I'm seeing that the SQL Agent jobs are being run from/on PRD. Also, if I delete or rename a job it deletes/renames on PRD and not on DEV. I checked MSDB.SYSJobs on DEV and all jobs have server_id=0 which according to MASTER.SYSServers=DEV.Environments are both running the same patched level of SQL 2008 R2 on identical hardware.
I did some searches and have more or less come up empty. Anyone experience this before?
Is your dev system on the same physical network as production?
What does the following query return when run against your dev server
select @@servername, serverproperty('servername')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 9, 2011 at 3:54 am
are these maintenance plan jobs? these are created as SSIS packages and the server name is hardcoded within them. You will have to delete the plans and recreate them. (via code NOT the GUI).
copying msdb is not the piece of cake it used to be in SQL2000.:crying:
---------------------------------------------------------------------
January 10, 2011 at 4:20 am
This I believe is the code based way to delete maint plan after a msdb move. Never tried it myself as I tend to avoid copying system databases around.
-- steps to delete maintenance plans if server renamed or msdb moved to a different server
Step 1:
Execute the below query to obtain the Maintenance plan name and Id
SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS
Step 2:
Replace the Id obtained from Step 1 into the below query and delete the entry from log table
DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '
Step 3:
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,
DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4:
Finally delete the maintenance plan using the below query where ID is obtained from Step1
DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
---------------------------------------------------------------------
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply