Wednesday, April 27, 2016

SQL Service Aliases

SQL Service Aliases (example instance name INST01)

NT SERVICE\MSSQL$INST01
NT SERVICE\SQLAgent$INST01
NT SERVICE\MSOLAP$INST01

Monday, April 25, 2016

Who locked my files?

Find out who has shared folder files locked and even force close them.

Use Computer Management snap-in, navigate to Shared Folders and Open Files


Thursday, April 21, 2016

How to transfer logins and passwords between instances of SQL Server

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

The script in this article creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin.

https://support.microsoft.com/en-us/kb/918992


Having the SIDs of sql logins match across multiple instances is especially useful in various scenarios including:

1.  Where the production database is frequently restored onto development and test instances, no more needing to run sp_change_users_login after every refresh

2. Mirrored and High Availability Group databases so that sql logins on the secondary instance match the users in the database after a failover.

Tuesday, April 12, 2016

Troubleshooting Checklist

When I get a call about poor performance, these are the things I check first.  What do you check for?  Leave comments below...

Moving SQL Agent output file

If you move the location of the SQL Agent output file by changing the parameter in SQL Server Configuration Manager, you may also need to update the registry entry.

Check the value of key ErrorLogFile in the following registry hive:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent

Check the path in the ErrorLogFile entry