The problems that I was experiencing that I listed in my previous post are still occurring after trying so many options, I still could not get the servers running properly, it is a really strange one as there is nothing to indicate what is causing it apart from the events appearing in the Event Logs.
So after running a Windows update on the servers last night and the problem showing itself 13 hrs after the servers had been re-booted we decided to go with the option of moving SQL from one server to an other. But it is not a straight forward move as the new server is windows 2003 server. The other server that is having the same problem only has a share on it that users run files from and also provides the Web Menus that we use to access the Databases, again this is a Windows Server 2000 box and is being moved to a Windows 2003 Server box.
We have in the excess of 11 or 12 10.6GB databases on the SQL Server and of course the migration needs to be done out of hours, which means 30 min to back up each DB so potentially 6 hrs to back up all databases, switch the old server off, rename the new server to the name of the old server then install SQL and service pack as SQL will complain and not run properly if you install and then rename the server, but alas! I had only really experienced this in the days of SQL Server 7.0 (if any of you can remember what that is), SQL 2000 picks up the new server name when you rename your server, Excellent! so this means I can restore the Databases as soon as they have finished being backed up (ie one DB finished being backed up can be restored to the new server whilst the next DB in the list on the old server is being backed up), thus saving several hours! and then once all the DB's are on the new server switch the old server off delete the computer account from Active Directory and then rename the new server to that of old server.
Although it picks up the new name there are two stored procedures that you will need to run from with in a Query Analyzer:
EXEC sp_dropserver 'Your_OLD_Computer_Name'
GO
and:
EXEC sp_addserver 'Your_NEW_Computer_Name', 'local'
GO
Once you have run those re-start your SQL Services, connect using Query Analyzer again and run the following command (it should display the new SQL Server name)
That's it your server is now the same same as your original SQL Server.
