Rename a SQL Server

Working with VMs a server can be cloned with SQL Server installed. The server is renamed but SQL server maintains its original name.

Run this script in SSMS:

sp_dropserver 'old server name'

sp_addserver 'new server name', 'local'

Restart the SQL Server service then test the rename worked:

Select @@servername;

Make sure the SQL Agent is running – it will stop when the sql service stops. If you restart sql server service through the SQL configuration control panel it should restart the sql agent for you.

Properties and other dialogs failing in SSMS

Error Message in SSMS



TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.
------------------------------

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------
There is no row at position 0. (System.Data)



Problem is to do with system table spt_values being deleted.

Run this script to repair - the location may be different on your server.
C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\install\u_tables.sql

SSMS short cuts

Although I have been using SSMS for a number of years now I didn't realise you could create your own short-cuts until I saw it mentioned on the UK SQL Server User Group: http://sqlserverfaq.com/
SSMS allows you to create your own short cut keys:

Under the Menu Tools Select Options

Under Environment >> Keyboard there is a list of shortcut commands.

A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;

I have added:
Ctrl+F1: master..xp_fixedDrives; Shows how much space is available on the fixed drives

Ctrl+3: Select * from sys.databases; Lists all databases

Ctrl+4: SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB' FROM sys.database_files; Shows how much space is available in SQL Data files

Ctrl + 5: DBCC SQLPERF(logspace); shows log files, the size of the file and % free space.
I have since added a couple more. One shows server waits and another shows server activity in a bit more details than sp_who2. I think I'll keep these as floating short cuts and change them depending on what I am working on or investigating.

An error occurred while executing batch

I ran a query which resulted in this:
An error occurred while executing batch. Error message is: The directory name is invalid.

The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.