Clear recent server list from SSMS

Today someone has asked me a question "How to clear the Most Recently Used (MRU) server names from the connect screen in SSMS"? 
There is no feature in SSMS to clear the MRU from the Connect to Server screen. 

When I launched SSMS 2008 the Connect screen, It has a lot servers listed in it. This information is stored in a file "SQLStudio.bin". This file is located in the below folder.
C:\Documents and Settings\[UserName]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell

Now to clear the Server Names from the connect screen, First close the SSMS and than delete above file. If SSMS is left open while deleting file than again you will get the old Server name list. This is because SSMS update above file just before closing completely.

Queries waiting for memory

You can use below query to find out the queries that require a memory grant to execute or have acquired a memory grant. 
The queries that do not have to wait on a memory grant will not appear in the result. 
If this query returns a lot of rows than it could be a indication of internal memory pressure. This will help you to identify the queries which are requsting larger memory grants. There are various reason for this. The quiry might be poorly written. That may require some index for optimization. You should run this query periodically and check for the resource hungry queries.  The user who runs below query must have View SErver STate permission on the server.

SELECT DB_NAME(ST.DBID) AS [Database Name], MG.Requested_Memory_KB AS [Requested memoty in KB], MG.Ideal_Memory_KB AS [Ideal Memory in KB], MG.Request_Time AS [Request Time], MG.Grant_Time AS [Grant Time], MG.Query_Cost AS [Query Cost], MG.DOP, ST.[TEXT], QP.Query_Plan AS [Query Plan]
FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP 
ORDER BY MG.REQUESTED_MEMORY_KB DESC

Who are connected to SQL Server

Use below query to check who are connected to SQL Server

SELECT C.Session_ID AS [Session], C.Most_Recent_Session_ID [Recent Session], C.Connect_Time, C.Last_Read, C.Last_Write, C.Num_Reads, C.Num_Writes, C.Net_Transport, C.Encrypt_Option, C.Auth_Scheme, C.Protocol_Type, C.Protocol_Version, C.Net_Packet_Size, C.Endpoint_ID, C.Client_Net_Address, C.Client_TCP_Port, C.Local_Net_Address, C.Local_TCP_Port, C.Node_Affinity, C.Connection_ID, C.Parent_Connection_ID, C.Most_Recent_SQL_HANDLE, ST.Text AS Query_Text, 
CASE WHEN ST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_Name(ST.DBID) END AS DATABASE_Name,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE Object_Schema_Name(ST.ObjectID, ST.DBID) END AS Object_Schema_Name,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE Object_Name(ST.ObjectID, ST.DBID) END AS Object_Name
FROM SYS.DM_Exec_Connections C CROSS APPLY SYS.DM_Exec_SQL_Text(C.Most_Recent_SQL_HANDLE) ST

Modify / Find SQL Server Agent log file

Use below queries to find the location of of SQLServer Agent log file, the log file is called SQLAGENT.out

DECLARE @AGENT_ERRORLOG NVARCHAR(255)
EXECUTE MASTER.DBO.XP_INSTANCE_REGREAD N'HKEY_LOCAL_MACHINE', N'SOFTWAREMICROSOFTMSSQLSERVERSQLSERVERAGENT', N'ERRORLOGFILE', @AGENT_ERRORLOG OUTPUT, N'NO_OUTPUT'

SELECT @@SERVERNAME SERVERNAME, @AGENT_ERRORLOG AGENTERRORLOGLOCATION

Use below queries to modify the location and Name of SQLServer Agent log file

USE [MSDB]
GO
EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N'D:TEMPSQLAGENT.OUT'
GO

Use below queries to recycle the SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG