See details of old sessions

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.

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

StackExchange Database Administrators Q#245983, answer score: 8