SQL Server 2008 rename, jobs not running

Question

Post reply

SQL Server 2008 rename, jobs not running

Mischa E.J. Hoogendoorn

Mr or Mrs. 500

Points: 558
More actions
November 8, 2010 at 4:10 am

#242689

Hello,
I have a question:
We needed to rename our sql2008 enterprise server on our production site. Everything did go well and it seems that sql server is running verry succesfull.
But I see that the jobs are not running. Looking on the internet did give me the following query:
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv
That query seems to be the solution for sql2000 and sql2005, but not for 2008 the field originating_server is renamed to originating_server_id, and that id should be 0, and the funny thing is, it is 0!
Can anyone help me?
Best regards,
Mischa

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may find the script posted under "Maintenance Plans" section in this link useful.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sturner SSC-Insane Points: 22411 More actions · Answer 2

did you run:

sp_dropserver 'oldServerName'

sp_addserver 'newServerName', 'local'

if not, please do that then bounce the service.

The probability of survival is inversely proportional to the angle of arrival.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Script out a drop/create of all your jobs and recreate them all..that will fix it. Whether or not there's a more elegant solution I'm not really sure.

Mischa E.J. Hoogendoorn Mr or Mrs. 500 Points: 558 More actions · Answer 4

Thanks for all of your help, I did run the script as mentioned above, and most problems where fixed, I just needed to recreate one script!

Best regards,

Mischa

george sibbald SSC Guru Points: 104210 More actions · Answer 5

Mischa E.J. Hoogendoorn (11/8/2010)
. Looking on the internet did give me the following query:
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv
That query seems to be the solution for sql2000 and sql2005, but not for 2008 the field originating_server is renamed to originating_server_id, and that id should be 0, and the funny thing is, it is 0!

that was the fix pre 2005 only (SQL2000 and probably SQL7)

I always thought it a shame MS did not provide a fix for this situation. Must have mucked up a lot of peoples DR plans!.

---------------------------------------------------------------------

Adiga One Orange Chip Points: 27284 More actions · Answer 6

Like mentioned earlier this article has a script for fixing Maintenance Plans.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

george sibbald SSC Guru Points: 104210 More actions · Answer 7

yes I saw that but its not an MS supplied script.

---------------------------------------------------------------------