See details of old sessions
No, SQL Server doesn’t keep a history of session activity, imagine how fast that would grow! Your only hopes for retrieving this information from the past, as your question asks, I think, are:
- That the app does something that gets logged in the default trace (and those events haven’t rolled away);
- Errors that the app might have generated that clearly indicate the server it connected to ended up in the application machine’s event log; or,
- Errors that the app might have generated (like deadlocks, I/O warnings, or failed logins) have been captured in the SQL Server error log or the system_health session.
Now, as mentioned in the comments, you could of course set up something to capture this activity in the future, so you don't have to sit around and wait to repeatedly hammer F5 when the app is running. You can use a variety of things, or even a combination, depending on whether you care only about connections, only about write activity, only about access to specific tables or databases, etc.:
- Extended Events
- logon triggers
- DML triggers
- SQL Server Audit
- server-side trace
- Wireshark
- maybe even from the plan cache
You cannot unless you have a third-party monitoring tool or some other kind of activity collector configured.
As mentioned in this answer, you might find something helpfull in the plan cache
:
SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%'
ORDER BY s.last_execution_time DESC;
But it seems you are looking for other information.
For the future you definitely should consider some tool like SSMS Tools Pack
(it is not free) or have a look at this post by Brent Ozar - it is a completely free way to log your server's activity with free procedure by Adam Machanic sp_whoisactive
so you can answer the kind of questions you have just asked.