Tuesday, February 16, 2010

Cloned server problem

I ran into an interesting problem the other day with a SQL Server on a virtual machine that was a clone of another machine. My co-workers had received a request for another virtual machine that had the same data on it as an older, test server for use in production. So, rather than build a new machine from scratch and backup/restore the test data to it, they simply cloned the virtual machine and off the users went with it. Finding this out, I first made a rather sour face and then logged onto the machine to see how it was functioning. Everything seemed well and good at first, until I noticed all of the Maintenance Plans were failing. I also was unable to delete the maintenance plans - receiving an error about the connection not being a trusted one. I posted the question to the SQLServerCentral forums and received a quick answer about what might be the issue. Since the machine was an exact copy of another one - even though the Windows computer name had been changed, the internal tables in SQL still reflected the old name. This article on BOL was also referenced: http://msdn.microsoft.com/en-us/library/ms143799(SQL.90).aspx. Running SELECT @@SERVERNAME verified the forum answer - it returned the old servername. The solution to this part of the problem was to run the following commands:

sp_dropserver '(oldname)'
sp_addserver (newname), local

Afterwards, running SELECT @@SERVERNAME returned the correct (new) name of the server.

I still, however, had no luck deleting the old maintenance plans. After some more digging, I found a workaround. Running this command returned some useful information:

USE msdb;
SELECT * FROM sysmaintplan_plans;

So, for each maintenance plan I wished to delete, I copied the ID returned above, and substituted it into the following three queries. After that, I deleted the associated SQL Agent jobs.

DELETE FROM sysmaintplan_log WHERE plan_id = '(plan_id)';
DELETE FROM sysmaintplan_subplans WHERE plan_id = '(plan_id)';
DELETE FROM sysmaintplan_plans WHERE id = '(plan_id)';

After that, I re-created the plans and jobs, and everything is functioning correctly again - at long last.

No comments:

Post a Comment