Backup not starting for Database with Full Text Catalog

Today when I was taking backup of a database got below error:

Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0×80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).

When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status. These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed. SQL server backup change status between PAUSE & RESUME before & after backup. 

Than I started Full text Service to resolve the error. And than try to backup and now it worked successfully.

Start, Stop and Disable the job in SQL Server 2008

1) Use below procedure to start the Job in SQL

Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'

Example:
Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'

OR

Exec msdb.dbo.sp_start_job  N'Job name'

2) Use below procedure to stop the Job in SQL

Exec msdb.dbo.sp_stop_job @job_name = 'Job_Name'

Example:
Exec msdb.dbo.sp_stop_job @job_name = N'Job_Name'

OR

Exec msdb.dbo.sp_stop_job  N'Job name'

3) Use below procedure to enable or disable the Job in SQL

To enable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 1

To disable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 0

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