SQL Server: How to see historical transactions

You can determine if a SQL Server database is actively being used by any users, both currently and historically (to some extent).

---

✅ Part 1: Check Current Active Usage

🔹 1. Who Is Connected Right Now (to a DB)?

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    s.database_id,
    DB_NAME(s.database_id) AS database_name,
    r.command,
    r.start_time
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.database_id = DB_ID('YourDatabaseName');

🔍 Shows who is currently active in a specific database, with login, host, and command info.

---

🔹 2. See All Sessions Using Any DB (Group Count)

SELECT
    DB_NAME(database_id) AS database_name,
    COUNT(session_id) AS active_sessions
FROM sys.dm_exec_sessions
WHERE database_id IS NOT NULL
GROUP BY database_id;

🔍 Quick snapshot to see which databases have active sessions.

---

🔹 3. Check If Any Process Accessed DB Recently (Last X Minutes)

SELECT 
    DB_NAME(database_id) AS database_name,
    last_request_start_time,
    last_request_end_time,
    login_name,
    host_name,
    program_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName')
  AND last_request_end_time > DATEADD(MINUTE, -30, GETDATE());

🔍 See last request timestamps by user/session.

---

✅ Part 2: Check Recent/Historical Usage (Basic Auditing)

🔹 4. Use Default Trace (Lightweight Built-in Logging)

SELECT 
    te.name AS event_name,
    t.DatabaseName,
    t.LoginName,
    t.ApplicationName,
    t.HostName,
    t.StartTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
    (SELECT TOP 1 value FROM sys.fn_trace_getinfo(NULL) WHERE property = 2)), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE t.DatabaseName = 'YourDatabaseName'
ORDER BY t.StartTime DESC;

✅ Use this to see recent schema changes or logins involving the DB.

---

🔹 5. Use SQL Audit (if previously configured)

If SQL Audit was enabled on the database:

SELECT event_time, action_id, session_server_principal_name, statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE database_name = 'YourDatabaseName';

---

✅ Part 3: Monitor Long-Term Usage Trends

If you want to track over time whether a DB is being used:

🔹 Setup a Light Agent Job

-- Run every X minutes
INSERT INTO db_usage_log (db_name, username, login_time, last_activity)
SELECT 
    DB_NAME(s.database_id), 
    s.login_name,
    s.login_time,
    s.last_request_end_time
FROM sys.dm_exec_sessions s
WHERE s.database_id = DB_ID('YourDatabaseName');

Store in a table like db_usage_log, then query/report later.

---

🚨Is Database Totally Idle?

To see if no one has touched the database in days:

SELECT 
    name AS database_name,
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('YourDatabaseName');

If all columns are NULL, it’s likely the DB is not in use at all.

---

✅ Summary Table

Goal	Query / Method
Who’s using DB now?	sys.dm_exec_sessions + sys.dm_exec_requests
When was DB last used?	last_request_end_time, index_usage_stats
Recent access by user	default trace, fn_trace_gettable()
Monitor usage over time	Scheduled job inserting from dm_exec_sessions
Auditing access	SQL Audit (if configured)

---