When was SQL Server database Last used??

If we want to remove the databases from the server which are not getting used from a long time. In this case first we need the name of databases which are not used from a long time. We can use below script to find the same.  

SELECT DBName, MAX(LastUsedDate) LastUsedDate

FROM

    (SELECT

        DB_NAME(database_id) DBName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PvtTable

UNPIVOT 

    (LastUsedDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpvtTable

GROUP BY DBName

ORDER BY LastUsedDate

Thanks!!

Basic Queries to troubleshoot Database Mirroring

Check the state of the DB Mirroring using DMV sys.tcp_endpoints

select name,type_desc,state_desc,port,is_dynamic_port,ip_address 
FROM sys.tcp_endpoints

Check the DB Mirror information using DMV sys.database_mirroring:

select database_id,mirroring_state_desc,mirroring_role_desc,
mirroring_partner_name,mirroring_partner_instance 
from sys.database_mirroring

Thanks!!!

How To Identify Sessions With Context Switching In SQL Server??

Context switching is the act of executing T-SQL code under the guise of another user connection, in order to utilize their credentials and level of rights.

By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement.  

We can use our DMV "sys.dm_exec_sessions" To identify the sessions with context switching as below:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

To explain this I have a sample database TestVK. I have logged in with credentials of "vimal". But Now I want to access the rights of user "sa1". T-SQL code for same are as below:

 EXECUTE AS LOGIN ='SA1';
 SELECT TOP 1 * FROM Item_Name

 Now lets execute the DMV "sys.dm_exec_sessions" to see the context switching sessions:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

Output:
session_id login_name original_login_name
80             sa1             vimal

Reference: Microsoft BOL, Red Gate

Thanks For Reading this Post!!!