SELECT d.name, d.database_id, m.mirroring_role_desc,
       m.mirroring_state_desc, m.mirroring_safety_level_desc, 
       m.mirroring_partner_name, m.mirroring_partner_instance,
       m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM   sys.database_mirroring m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
go

select *
from sys.dm_os_performance_counters
where 1 = 1
and object_name like '%Database Mirroring%'
and instance_name not in ('_Total')
and counter_name in ('Redo Queue KB','Log Send Queue KB')
go

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, 
       t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, 
       e.connection_auth_desc 
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id 
go

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p 
INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT'
AND ep.type_desc = 'DATABASE_MIRRORING'

USE [master]
GO

CREATE ENDPOINT [] 
       STATE=STARTED
       AS TCP (LISTENER_PORT = , LISTENER_IP = ALL)
       FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

select *
from sys.dm_os_performance_counters
where 1 = 1
and object_name like '%Database Mirroring%'
and instance_name not in ('_Total')
and counter_name in ('Redo Queue KB','Log Send Queue KB')
go

DECLARE @DB SYSNAME 
DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)
SET @DB = 'My_database'        -- Put the name of database here
DECLARE cCursor CURSOR
FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses
WHERE SPID > 50                -- To not try to Kill SQL Server Process
AND SPID != @@SPID             -- To not kill yourself
AND DBID = DB_ID(@DB)          -- To filter only the sessions connected in specific database

OPEN cCursor
FETCH NEXT FROM cCursor INTO @SPID

-- For each session connected in the database
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @cmdSQL = 'KILL ' + @SPID

-- Kill the session
        EXEC (@cmdSQL)
        FETCH NEXT FROM cCursor INTO @SPID 
        END
CLOSE cCURSOR
DEALLOCATE cCURSOR

SELECT
  r.session_id,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  DB_NAME(r.database_id) AS DatabaseName,
  st.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id <> 0;

Show Database Backup List :
D : database
I : differentielle
L : journaux de transactio

select convert(varchar(50),database_name),
       backup_start_date,
       backup_finish_date,
       type,
       backup_size,
       compressed_backup_size,
       physical_device_name,
       is_copy_only
from msdb..backupset set1
INNER JOIN msdb.dbo.backupmediafamily media1 
ON media1.media_set_id = set1.media_set_id
where 1=1
-- and database_name = 'Database_Name'
and type in ('D', 'I', 'L')
order by backup_start_date desc
go

SELECT
db.name AS [Database_Name],
ISNULL(STR(ABS(DATEDIFF(dd, getdate(), MAX(bsF.backup_finish_date)))), 'NEVER') AS [DaysSinceLastFULLBackup],
ISNULL(CONVERT(varchar(20), MAX(bsF.backup_finish_date), 126), 'NEVER') AS [LastFULLBackupDate]
FROM
sys.databases db
LEFT JOIN msdb.dbo.backupset bsF ON bsF.database_name = db.name 
AND bsF.type = 'D' /* I for Differential, L for Transaction Log */
GROUP BY db.name 
ORDER BY db.name
go

SELECT command,
       s.text,
       start_time,
       percent_complete,
       CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
             + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
             + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
       CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
             + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
             + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
       dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

SELECT
  r.session_id,
  r.status,
  r.cpu_time,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS DatabaseName,
  SUBSTRING(st.text,(r.statement_start_offset/2)+1,((CASE r.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE r.statement_end_offset
    END - r.statement_start_offset)/2)+1) AS RunningQuery
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50

SELECT
  r.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  r.status,
  r.command,
  r.wait_type,
  r.wait_resource,
  r.wait_time,
  r.cpu_time,
  r.logical_reads,
  r.writes,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS DatabaseName,
  SUBSTRING(st.text,(r.statement_start_offset / 2) + 1,((CASE r.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE r.statement_end_offset
    END - r.statement_start_offset) / 2) + 1) AS RunningQuery
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

SELECT TOP 20
  DB_NAME(st.dbid) AS DatabaseName,
  st.text AS QueryText,
  qs.execution_count,
  qs.total_worker_time AS TotalCPU,
  qs.total_elapsed_time AS TotalElapsedTime,
  qs.total_logical_reads AS TotalLogicalReads,
  qs.total_logical_writes AS TotalLogicalWrites
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

SELECT TOP 10
  DB_NAME(st.dbid) AS DatabaseName,
  SUBSTRING(st.text,(qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1)
  AS QueryText,
  qs.execution_count,
  (qs.total_worker_time / qs.execution_count) AS AvgCPU
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 0
ORDER BY AvgCPU DESC;

SELECT TOP 10
  DB_NAME(st.dbid) AS DatabaseName,
  SUBSTRING(st.text,(qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1) 
  AS QueryText,
  (qs.total_logical_reads + qs.total_logical_writes) AS TotalIO
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY TotalIO DESC;

SELECT AGS.name 				AS AG_Group_Name,
       AR.replica_server_name      		AS Instance_Name, 
       HARS.role_desc, 
       Db_name(DRS.database_id) 	        AS DB_Name, 
       DRS.database_id, 
       AR.availability_mode_desc      		AS Sync_Mode, 
       DRS.synchronization_state_desc 		AS Sync_State,
	   AR.failover_mode_desc        	AS Failover_Mode,
	   HARS.connected_state_desc,
	   HARS.synchronization_health_desc ,
	   
       -- DRS.last_hardened_lsn, 
       -- DRS.end_of_log_lsn, 
       -- DRS.last_redone_lsn, 
       -- DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
       -- DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
       DRS.log_send_queue_size, 
       DRS.redo_queue_size --,
                    --Time corresponding to the last commit record.
                    --On the secondary database, this time is the same as on the primary database.
                    --On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database 
                    --   has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database 
                    --   row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress 
                    --   has been reported back to the primary replica by the secondary replica.
        -- DRS.last_commit_time
FROM sys.dm_hadr_database_replica_states DRS LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id AND AR.replica_id = HARS.replica_id
order by AGS.name, Db_name(DRS.database_id), AR.replica_server_name, AR.availability_mode_desc
go

SELECT
  DB_NAME(drs.database_id) AS DatabaseName,
  drs.synchronization_state_desc,
  drs.synchronization_health_desc,
  drs.log_send_queue_size,
  drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs;

WITH DB_CPU AS (
  SELECT
    DB_NAME(pa.value) AS DatabaseName,
    SUM(qs.total_worker_time) AS TotalCPU
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
  WHERE pa.attribute = 'dbid'
  GROUP BY pa.value
)
SELECT DatabaseName, TotalCPU
FROM DB_CPU
ORDER BY TotalCPU DESC;

SELECT
  type AS MemoryClerk,
  SUM(virtual_memory_committed_kb) / 1024 AS MemoryCommittedMB,
  SUM(virtual_memory_reserved_kb) / 1024 AS MemoryReservedMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY MemoryCommittedMB DESC;

SELECT
  DB_NAME(database_id) AS DatabaseName,
  COUNT(*) * 8 / 1024 AS BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY BufferPoolMB DESC;

SELECT
  GETDATE() AS CaptureTime,
  cpu_count,
  scheduler_count,
  physical_memory_kb / 1024 AS PhysicalMemoryMB
FROM sys.dm_os_sys_info;