This morning, I 'ave been mostly deleting old SQL Agent Jobs

And these have been those annoying jobs that are, apparently, undeletable and unwanted maintenance jobs.

Job #1

A rather ancient backup job, or, rather, the remnants thereof, sitting in SQL 2005 Agent but with no maintenance plan behind it, no subplan schedule within it, and generally going 'Ha Ha' in a manner eerily reminiscent of Nelson Muntz. Assuming that the following error message can be translated as 'Ha Ha!'.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.The statement has been terminated. (Microsoft SQL Server, Error: 547)

First, I had a look in the sysjobs table


SELECT *
FROM msdb.dbo.sysjobs
WHERE name like 'plan name%'

This gave me the ID for the job, and then I was able to run the following against the sysmaintplan_subplans table


SELECT *
FROM sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

(to confirm that I was looking at the correct job)


DELETE sysmaintplan_subplans
WHERE job_id = '28B010B4-5FCD-4802-8405-808E2C9EA352'

Then I was able to delete the job via Job Activity Monitor.

Job #2

This was sitting on a SQL 2000 SP4 box, which had been created in the time honoured fashion: take an image of the original server and rename it. You get the most infuriating error

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

For this one to be fixed, you need to restart SQL. First, find out what the name of the original server was by running

SELECT @@SERVERNAME

Then, rename it with the correct name

sp_dropserver 'OldServerName'

sp_addserver 'CorrectServerName', 'local'

Then, restart SQL. I got lucky. No-one was using the Server.

Run the following


UPDATE msdb..sysjobs
SET originating_server = 'CorrectServerName'
WHERE originating_server = 'OldServerName'

Bob's your Uncle, Charlie's your Aunt, and it should now be possible to delete the recalcitrant job.

This is a known problem that was introduced in SQL 2000 SP3, and has apparently persisted. Grrrr.

(This week i 'ave mostly been.... ref)

Servernames

@@SERVERNAME. Sometimes, it isn't the same as the actual server's name. @@Servername is incorrect, it's wrong, it doesn't match. Argh!! It's easy enough to map though:


SELECT @@SERVERNAME AS[@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

The problem arises, when someone decides to rename the Server after SQL has been installed. The new Servername will get recognised by SQL during startup: and, default or instance, it will happily allow you to connect to SQL using the new Servername afterwards.

But....

The sys.servers (or sysservers, if you are in SQL 2000) table still holds the old information, hence the erroneous information from @@SERVERNAME (if you run SELECT SERVERPROPERTY('ServerName') you will get the correct information).

So. You can change the sys.servers table information thus (having disabled replication AND dropped any remote logins first AND switched off mirroring AND scripted out all your maintenance plans AND deleted them...):

sp_dropserver old_servername\instancename
GO
sp_addserver new_servername\instancename, local
GO

If you don't disable replication or drop the remote logins, then there will be errors. Don't say you weren't warned. You may need, if you have linked servers, to re-add those. Client aliases using named pipes will also need to be checked and corrected.

If you didn't script out and delete your maintenance plans before you got going, there is now (as of SQL 2008) a script to deal with the fact that they cannot be changed nor deleted....

Which all goes to show that, really, once you have decided what the Server is going to be called, you should stick with it. Avoid calling servers the same as the previous server, but with 'New' on the end. Or calling anything 'Temp'. Or using a default name. SQL2000Win2003 might be descriptive, but it won't help if you don't identify which one of the four servers with that name it really is.... Particularly, avoid having a server named, say, George, calling the new server, NewGeorge, and then creating a third server to replace NewGeorge which is, confusingly, called George, but isn't the same server as the Original George was. Also confusing is a server which was called Mabel, but is now called Sybil, while another server used to be called Sybil but is now called Fiona, while there still exists a server which has been called Mabel all along.

In other words. THINK.