--Find active connections to the instance
USE master;
GO
EXEC sp_who2 'active';
GO

--Find number of active connections to database
USE master;
GO
SELECT DB_NAME(dbid) AS DBName,
spid,
COUNT(dbid) AS NumberOfConnections,
loginame,
login_time,
last_batch,
status
FROM    sys.sysprocesses
WHERE DB_NAME(dbid) = 'AdventureWorks2016'  --insert your database name here
GROUP BY dbid, spid, loginame, login_time, last_batch, status
ORDER BY DB_NAME(dbid)

--Active Connections to Database with connecting IP address
SELECT
s.host_name,
s.program_name,
s.login_name,
c.client_net_address,
db_name(s.database_id) as DBName,
s.login_time,
s.status,
GETDATE() as date_time
FROM sys.dm_exec_sessions as s
INNER JOIN sys.dm_exec_connections as c on s.session_id = c.session_id
INNER JOIN sys.sysprocesses as p on s.session_id = p.spid
WHERE DB_NAME(p.dbid) = 'AdventureWorks2016'  --insert your database name here

SELECT
DB_NAME(database_id) DatabaseName,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
WHERE db_name(database_id) = 'AdventureWorks2016'  --insert your database name here

SELECT name DatabaseName
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
EXCEPT
SELECT DISTINCT
DB_NAME(database_id) DatabaseName
FROM sys.dm_db_index_usage_stats
ORDER BY 1

--Transaction count increasing?
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Transactions/sec%'
AND instance_name LIKE 'AdventureWorks2016%' --insert your database name here
GO

SELECT  OBJECT_NAME (referencing_id) AS referencing_object,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
AND is_ambiguous = 0;

SELECT OBJECT_NAME (referencing_id) AS referencing_object,
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_server_name IS NOT NULL
AND is_ambiguous = 0;

source: http://www.sqlservercentral.com/articles/Administration/164493

No Comments

You must log on to comment.