Home » Posts tagged 'TSQL'

Tag Archives: TSQL

DMV-7 : Find Queries waiting for memory ?……..sys.dm_exec_query_memory_grants


sys.dm_exec_query_memory_grants DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-IN/library/ms365393.aspx

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

This DMV helps in finding queries that are waiting (or recently had to wait) for a memory grant. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. There were some new columns added for SQL Server 2008 and above.

You should periodically run this query multiple times in regular intervals and need to look for rows returned each time. If you do see a lot of rows returned each time, then it could be an indication of internal memory pressure. It will help you to identify queries that are requesting relatively large memory grants, perhaps because they are poorly written or they’re missing indexes that make the query more expensive.

Query 1 : Details of queries required memory to execute

SELECT DB_NAME(ST.DBID) AS [DATABASENAME],

MG.REQUESTED_MEMORY_KB ,

MG.IDEAL_MEMORY_KB ,

MG.REQUEST_TIME ,

MG.GRANT_TIME ,

MG.QUERY_COST ,

MG.DOP ,

ST.[TEXT],

QP.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 ;

Remarks

1. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-6 : How well my store procedure doing ?……..sys.dm_exec_procedure_stats


sys.dm_exec_procedure_stats DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/cc280701.aspx

Returns aggregate performance statistics for cached stored procedures. The view con­tains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the cor­responding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

This DMV is new to SQL Server 2008 so you can use it only in SQL Server 2008 onwards. You can get similar data out of sys.dm_exec_cached_plans, which will work on SQL Server 2005. This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.

Query 1 : Details of cached procedures

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,*

FROM SYS.DM_EXEC_PROCEDURE_STATS

Query 2 : Details of procedure with total & average CPU, logical reads , logical writes & physical reads

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,CACHED_TIME

,LAST_EXECUTION_TIME

,EXECUTION_COUNT

,TOTAL_WORKER_TIME / EXECUTION_COUNT AS AVG_CPU

,TOTAL_ELAPSED_TIME / EXECUTION_COUNT AS AVG_ELAPSED

,TOTAL_LOGICAL_READS

,TOTAL_LOGICAL_READS / EXECUTION_COUNT AS AVG_LOGICAL_READS

,TOTAL_LOGICAL_WRITES

,TOTAL_LOGICAL_WRITES / EXECUTION_COUNT AS AVG_LOGICAL_WRITES

,TOTAL_PHYSICAL_READS

,TOTAL_PHYSICAL_READS / EXECUTION_COUNT AS AVG_PHYSICAL_READS

FROM SYS.DM_EXEC_PROCEDURE_STATS

ORDER BY AVG_LOGICAL_READS DESC

Remarks

1. User required VIEW SERVER STATE permission on the server.

2. This DMV will capture the details of 3 objects types :

a. SQL_STORED_PROCEDURE

b. CLR_STORED_PROCEDURE

c. EXTENDED_STORED_PROCEDURE

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-5 : Queries runing are adhoc or proc , single or multi use ?……..sys.dm_exec_cached_plans


sys.dm_exec_cached_plans DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/ms187404.aspx

Returns a row for each query plan that is cached by SQL Server for faster query execu­tion. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

sys.dm_exec_cached_plans provide execution related details like no. of queries comes under adhoc or proc section. It help is understanding the query type & performance improvisation by looking into heavily running adhoc queries.

Query 1 : Memory used by cache plan of each database

SELECT

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

SUM(CASE WHEN CP.USECOUNTS <= 1 THEN 1 ELSE 0 END) AS SINGLE_USE_COUNT,

SUM(CASE WHEN CP.USECOUNTS > 1 THEN 1 ELSE 0 END) AS MULTI_USE_COUNT,

AVG(CASE WHEN CP.USECOUNTS > 1 THEN CP.USECOUNTS ELSE NULL END) AS MULTI_USE_AVG_USE_COUNT,

(SUM(CASE WHEN CP.USECOUNTS <= 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS SINGLE_USE_SIZE_IN_MBYTES,

(SUM(CASE WHEN CP.USECOUNTS > 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS MULTI_USE_SIZE_IN_MBYTES,

(SUM(CP.SIZE_IN_BYTES) / (1024 * 1024)) AS TOTAL_SIZE_IN_MBYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

GROUP BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

ORDER BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

Query 2 : Find single-use, ad-hoc queries

SELECT ST.[TEXT] ,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CP.SIZE_IN_BYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

WHERE CP.CACHEOBJTYPE = ‘COMPILED PLAN’

AND CP.OBJTYPE = ‘ADHOC’

AND CP.USECOUNTS = 1

ORDER BY CP.SIZE_IN_BYTES DESC

Remarks

1. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-4 : Find top most expensive cached queries ?……..sys.dm_exec_query_stats


sys.dm_exec_query_stats DMV (Dynamic Management View), described in BOL as follows : http://msdn.microsoft.com/en-us/library/ms189741.aspx

Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

sys.dm_exec_query_stats provides a wealth of performance statistics for cached query plans. It’s a very useful DMV to get cached query details for performance & server load analysis.

Query 1 : Top 10 total CPU consuming queries

SELECT TOP 10

QT.TEXT AS STATEMENT_TEXT,

QP.QUERY_PLAN,

QS.TOTAL_WORKER_TIME AS CPU_TIME

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP

ORDER BY TOTAL_WORKER_TIME DESC

Query 2 : Top 10 average CPU consuming queries

SELECT TOP 10

TOTAL_WORKER_TIME ,

EXECUTION_COUNT ,

TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,

QT.TEXT AS QUERYTEXT

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT

ORDER BY QS.TOTAL_WORKER_TIME DESC ;

Query 3 : Top 10 I/O intensive queries

SELECT TOP 10

TOTAL_LOGICAL_READS,

TOTAL_LOGICAL_WRITES,

EXECUTION_COUNT,

TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

QT.TEXT AS QUERY_TEXT,

DB_NAME(QT.DBID) AS DATABASE_NAME,

QT.OBJECTID AS OBJECT_ID

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0

ORDER BY [IO_TOTAL] DESC

Query 4 : Execution count of each query

SELECT QS.EXECUTION_COUNT,

QT.TEXT AS QUERY_TEXT,

QT.DBID,

DBNAME= DB_NAME (QT.DBID),

QT.OBJECTID,

QS.TOTAL_ROWS,

QS.LAST_ROWS,

QS.MIN_ROWS,

QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

ORDER BY QS.EXECUTION_COUNT DESC

Remarks

1. Statistics in the view are updated when a query is completed.

2. User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-3 : What is currently going on ?……..sys.dm_exec_requests


sys.dm_exec_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms177648.aspx

Returns information about each request that is executing within SQL Server.

sys.dm_exec_requests DMV is used to get details of currently running sessions. This DMV help us is getting details like used database, transaction isolation level, open transaction count, status, blocked session, percentage of task completed etc.

Query 1 : Complete details of currently running queries

SELECT

R.SESSION_ID,

R.REQUEST_ID AS SESSION_REQUEST_ID,

R.STATUS,

S.HOST_NAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ + S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,

R.COMMAND,

ST.TEXT AS QUERY_TEXT,

QP.QUERY_PLAN AS XML_QUERY_PLAN,

R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

R.LAST_WAIT_TYPE,

R.BLOCKING_SESSION_ID,

R.ROW_COUNT,

R.GRANTED_QUERY_MEMORY,

R.OPEN_TRANSACTION_COUNT,

R.USER_ID,

R.PERCENT_COMPLETE,

CASE R.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN ‘UNSPECIFIED’

WHEN 1 THEN ‘READUNCOMITTED’

WHEN 2 THEN ‘READCOMMITTED’

WHEN 3 THEN ‘REPEATABLE’

WHEN 4 THEN ‘SERIALIZABLE’

WHEN 5 THEN ‘SNAPSHOT’

ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME

FROM

SYS.DM_EXEC_REQUESTS R

LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID

LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP

WHERE

R.STATUS NOT IN (‘BACKGROUND’,’SLEEPING’)

Query 2 : How much task is completed in percentage? It’s my one of the favorite query while doing backup, restore check DB or index rebuild.

SELECT

A.SESSION_ID,

DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,

A.START_TIME,A.COMMAND,

A.CPU_TIME,

A.PERCENT_COMPLETE,

A.ESTIMATED_COMPLETION_TIME,

B.TEXT

FROM SYS.DM_EXEC_REQUESTS A

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B

WHERE SESSION_ID > 50

Remarks

1. All Possible status of sessions :

· Background

· Running

· Runnable

· Sleeping

· Suspended

2. Percentage of work completed can be viewed for the following commands:

· ALTER INDEX REORGANIZE

· AUTO_SHRINK option with ALTER DATABASE

· BACKUP DATABASE

· DBCC CHECKDB

· DBCC CHECKFILEGROUP

· DBCC CHECKTABLE

· DBCC INDEXDEFRAG

· DBCC SHRINKDATABASE

· DBCC SHRINKFILE

· RECOVERY

· RESTORE DATABASE,

· ROLLBACK

· TDE ENCRYPTION

3. Exceptions for Blocking Session ID :

· -2 = The blocking resource is owned by an orphaned distributed transaction.

· -3 = The blocking resource is owned by a deferred recovery transaction.

· -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions

4. All possible Transaction isolation level of the session, on basis on integer values :

· 0 = Unspecified

· 1 = ReadUncomitted

· 2 = ReadCommitted

· 3 = Repeatable

· 4 = Serializable

· 5 = Snapshot

5. Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-2 : Explore the secrets of session…… sys.dm_exec_sessions


sys.dm_exec_sessions DMV (Dynamic Management View), delineated by BOL as follows: http://msdn.microsoft.com/en-us/library/ms176013.aspx

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

sys.dm_exec_sessions is extremely useful DMV when you need to fetch details like login name, transaction isolation level, status, used databases, language etc.

Query 1 : Complete details of every SQL Session

SELECT

S.SESSION_ID,

S.STATUS,

S.HOST_NAME,

DB_NAME(S.DATABASE_ID) DBNAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ +S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

C.CONNECT_TIME, – DIFFRENT BETWEEN CONNECT & LOGIN TIME IS TIME TAKEN BY PRELOGON ACTIVITIES

S.LOGIN_TIME,

CASE S.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN ‘UNSPECIFIED’

WHEN 1 THEN ‘READUNCOMITTED’

WHEN 2 THEN ‘READCOMMITTED’

WHEN 3 THEN ‘REPEATABLE’

WHEN 4 THEN ‘SERIALIZABLE’

WHEN 5 THEN ‘SNAPSHOT’

ELSE CAST(S.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME,

–S.LAST_SUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.LAST_UNSUCCESSFUL_LOGON, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

–S.UNSUCCESSFUL_LOGONS, — REQUIRES ‘COMMON CRITERIA COMPLIANCE ENABLED’ OPTION VIA SP_CONFIGURE.

S.CPU_TIME AS CPU_TIME_MS,

S.MEMORY_USAGE AS MEMORY_USAGE_PAGES,

S.ROW_COUNT,

S.PREV_ERROR,

S.LAST_REQUEST_START_TIME,

S.LAST_REQUEST_END_TIME,

C.NET_TRANSPORT,

C.PROTOCOL_TYPE,

S.LANGUAGE,

S.DATE_FORMAT,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_SESSIONS S

FULL OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.SESSION_ID = S.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

WHERE

S.SESSION_ID IS NULL

OR S.SESSION_ID > 50

ORDER BY

S.SESSION_ID

Query 2 :  What number session every login have?

SELECT LOGIN_NAME , COUNT(SESSION_ID) AS [SESSION_COUNT]

FROM SYS.DM_EXEC_SESSIONS

GROUP BY LOGIN_NAME ORDER BY COUNT(SESSION_ID) DESC ;

Remarks

1.       All Possible status of sessions :

·         Running - Currently running one or more requests

·         Sleeping - Currently running no requests

·         Dormant – Session has been reset because of connection pooling and is now in prelogin state.

·         Preconnect - Session is in the Resource Governor classifier.

2.       All possible Transaction isolation level of the session, on basis on integer values :

·         0 = Unspecified

·         1 = ReadUncomitted

·         2 = ReadCommitted

·         3 = Repeatable

·         4 = Serializable

·         5 = Snapshot

3.       Common Criteria Compliance Enabled : If this option in server configuration is enabled, logon statistics are displayed in the following columns. If this option is not enabled, these columns will return null values.

·         last_successful_logon

·         last_unsuccessful_logon

·         unsuccessful_logons

4.       Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-1 : Who All Are connected ? …. sys.dm_exec_connections


The sys.dm_exec_connections DMV (Dynamic Management View) is represented by Books Online (BOL) as follows : http://msdn.microsoft.com/en-us/library/ms181509.aspx

Returns information about the connections established to this instance of SQL Server and the details of each connection.

sys.dm_exec_connections is the most common DMV used to get connection details. We will get details info regarding every connection like protocol user, last read time, last write time, last executed SQL query, connection time etc.

Query 1 : Complete details of every SQL Connection

SELECT

C.SESSION_ID,

C.MOST_RECENT_SESSION_ID,

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,

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,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_CONNECTIONS C

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

Query 2 : Sample Query Get a count of SQL connections by IP address

SELECT EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME ,

COUNT(EC.SESSION_ID) AS [CONNECTION COUNT]

FROM SYS.DM_EXEC_SESSIONS AS ES

INNER JOIN SYS.DM_EXEC_CONNECTIONS AS EC

ON ES.SESSION_ID = EC.SESSION_ID

GROUP BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME

ORDER BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ;

Permissions : User required VIEW SERVER STATE permission on the server, to use this DMV.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Backup not starting for Database with Full Text Catalog…………..Failed to change the status to RESUME for full-text catalog in database. Error: 0×80043607


Issue :-

One of our Database with Full Text Catalog is not getting backed up. 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.

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.).

Solution :-

You need to restart the Full Text service to resolve the issue.

I have restarted the FTS & try backup again and it worked successful.

Please share if you face any more issue in this regard or any other possible solution.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Refresh Intellisense in SSMS(SQL Server Management Studio) 2008 & above


Intellisense is a new feature that comes with SSMS(SQL Server Management Studio) 2008 onwards. Intellisense makes coding quite easy.

But Sometimes I find that intellisense becomes stale and you start getting the wavy red lines when writing code. Even though object exists in database but Intellisense is not able to recognize it.

Refreshing the cache is quite easy but not necessarily strictly required.

Go to Edit -> IntelliSense -> Refresh Local Cache OR you can use shortcut Ctrl + Shift + R

You can check in below image that database has 5 table but intellisense showing only 4 tables. Table “JKL” is missing from intellisense list. You can refresh the Intellisense to get all tables.

More details on Intellisense can be found over MSDN : http://msdn.microsoft.com/en-us/library/hh245114.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Microsoft SQL Server License Helpline


Microsoft SQL Server license has lots of flavors in terms of user based \ CAL or processor based license, license based on versions or license based on environment (physical or virtual).

Although, Microsoft release license guidelines for each & every SQL server version. But there are more complex scenarios for licensing then we think.

Best person to answer your all queries over licensing is MICROSOFT itself.

You can call Microsoft Licensing at 1-800-426-9400, Monday to Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Follow

Get every new post delivered to your Inbox.

Join 132 other followers