SQL Server maintenance solution

I've become a big fan of Ola Hallengren's SQL Server 2005 and 2008 database maintenance solution since I discovered it a few months ago. As anyone that has delved very far into the Reorganize and Rebuild Indexes tasks in SQL 2005/2008 maintenance plans knows, they couldn't exactly be described as 'sophisticated'. The tasks do not take the existing fragmentation level in the indexes into account - they simply fire off a rebuild or reorganize of all indexes. The general recommendation for maintaining indexes is to reorganize if fragmentation is between 10% and 20%, and to do a rebuild if fragmentation is greater than 20%. Doing rebuilds of indexes with little to no fragmentation is a waste of CPU cycles. Those who want a more intelligent method of maintaining their indexes are forced to go at it programmatically. I had been mulling this exact same thing over when I stumbled across Ola's free solution. It includes solutions for index maintenance, backups, and integrity checking. Definitely check it out here.

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)'

GO;

sp_addserver (newname), local

GO;

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.