See details of old sessions

Question

Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing a certain database. I know the application runs at some point on a Friday evening so I was hoping that SQL server maintains a session history so I could just look up sp_who-like details for the hours in question.

Like I say, I don't expect this to be possible but right now I am absolutely clutching at straws.

Thanks in advance.

EDIT: I found the server in question by other means in the end; the job executed a SQL statement to create a file which was then FTPd to another site - that site has FTP logging that could give me the name and IP address of the source server. Thanks for the answers - I only wish I could accept them both as they both make very good points and give very good suggestions.

Answer 1

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:

1. That the app does something that gets logged in the default trace (and those events haven’t rolled away);
2. 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,
3. 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

Answer 2

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.