SQL Server Configuration Manager Aliases

I don’t do too much with SQL, but this one got me for a while, so here’s my story on SQL Server Configuration Manager Aliases.

I had a particular server that couldn’t connect to a specific instance on SQL on another box. Other servers appeared to be fine, but each time SQL Server Management Studio was run and attempted to connect to servername\instancename, it would instead connect to the default instance.

It didn’t matter who logged onto the server either. It would never connect to that secondary instance and I couldn’t work out why.

After much digging and testing, I resorted to reading through forum threads on Google searches, hoping for an idea. What I eventually found was the existence of SQL Client Aliases. These are like hosts file records – hard coded results for connecting to a specific server:

In Sql Server Configuration Manager, you can define an alias for what you’re connecting to. Servername\instancename could map to serverb\instancename or servername\instance2 – this is great when doing testing and wanting to point a server at a different SQL database or instance without changing a bunch of settings.

However, the other catch is the port specified. In the above example, the default SQL port 1433 is used. Makes sense, but each instance uses it’s own port, or uses a dynamic port. I soon discovered that if you try to connect to a SQL instance and have a port defined, the SQL instance you actually connect to is whatever is listening on that defined port.

An easy thing to find if you know where to look for it, and now I do. Hopefully this helps others who come across a similar scenario!

Shriking your SQL log file


I ran into this issue today on a Microsoft SQL 2008 R2 server. A new server I’d built hadn’t had the SQL backups set yet, so the SQL log file had blown out in size (100gb in less than a month). It had used up all the disk space allocated, so doing a normal backup wasn’t shrinking the log file. The solution was to run the following query. To do this, in Microsoft SQL Server Management Studio, select the culprit database and then the ‘New Query’ button. This will bring up an area to type your query:



 Replace ‘dbname’ with your database name, and click ‘! Execute’ to run. It should look like this:


Once successfully run, your log file size should be much more reasonable.

To stop this occurring in the first place, make sure you’ve set up regular backups to your databases. Just running a backup won’t truncate the logs though, and here’s a great article on Technet explaining it:



Good luck!