Rename SQL on a cloned server

Cloning the server to make an exact copy of Prod for development and QA is a great time saver and ensures configuration is done correctly.  Unfortunately SQL still thinks it is the original server.  Luckily this is an easy fix.

Check what server SQL thinks it is:

select * from sys.servers

--or
--select * from sysservers
--if you are still running SQL 2000

Now rename the server:

USE master
GO
sp_dropserver [old_name]
GO
sp_addserver [new_name], local
GO

And finally reconfigure any local monitoring, setup backups, etc.

Remove Multiple Log Files

For a table compression project we added an additional log to the database to handle the increased utilization.  Now we need to drop that log.

--Drop the log
ALTER DATABASE ED1 REMOVE FILE ED1LOG2

But I still see the extra log file in database properties.  What is going on?  The file does not exist in

select * from sys.sysfiles

To fix the display of the extra log file that has been removed:

 Run the log backup for the database.

This removes the extra log from database properties and we are back to normal operations.

Cycle Error Log before enabling Alerts

Today I learned to always cycle the error log before enabling alerts after receiving 15,000 text and email messages.  When you enable alerts SQL will read through the current error log and alert you to all the problems you have had.  Since we already fixed those issues we are only concerned about future problems.  To give yourself a clean log run:

EXEC sp_Cycle_ErrorLog

If you forgot to do clear the log and the other DBAs you work with are throwing things at you:

Stop SQL Agent

--number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems  --remove all the unsent emails
delete from msdb.dbo.sysmail_unsentitems

Remember to turn SQL Agent back on when done.


   




  

Today we begin the journey of sharing what I have learned that makes people's eye's gloss over with a group that will be more interested.  I hope to give back to the community that has helped me so much and hopefully make your lives a bit easier.