7

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.

8

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.:

  • Hi Aaron - I'm afraid there are no errors and no traceable events; it's a really simple select script, unfortunately. I knew deep down it wouldn't be possible, for exactly the reason you state, but it was worth a try, I suppose. I could always sit here until 20:30 Friday night and just run sp_who but I suspect the job only takes a few seconds. I'll have to see if the infrastructure guys can come up with anything suitably clever. Thanks for the reply. – Spratty Aug 22 at 15:36
  • You could run Wireshark during the time that the application is believed to be running. Then search the Wireshark trace to see which application server is connecting to your database server at that time. – Doug Deden Aug 22 at 15:51
  • @DougDeden You could also run your own server-side trace, or Extended Events, or auditing. The OP is trying to determine information about sessions that ran in the past, not sessions that may run in the future. – Aaron Bertrand Aug 22 at 15:54
  • 1
    @AaronBertrand Agreed. I was riffing on the OP's idea of "I could always sit here until 20:30 Friday night..." as a way to catch it in the act, and giving them another option. But yeah, keeping it within SQL is probably better, presuming the OP has full access to SQL, but possibly only limited access at the OS level. – Doug Deden Aug 22 at 18:15
  • 1
    @DougDeden Ah, I see, I thought you meant as a solution to the original question. Cheers. – Aaron Bertrand Aug 22 at 18:17
5

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.

  • Hi George - thank you for that; it certainly gives me some options to consider. I don't know if I can get the boss to spring for one-use software but it may be worth a try. sp_whoisactive looks very interesting - I'm sure I can find a use for that :-) – Spratty Aug 22 at 15:39
  • @Spratty all the proper monitoring tools offer lengthy trials. A trial could help you pinpoint this issue but it might also give you a chance to discover all the other things it can do for you that will save you time in the long run (and hence justify a purchase). People buy monitoring tools because they provide benefits and help automate tedious and time-consuming tasks, not because they have a bunch of money to burn. – Aaron Bertrand Aug 22 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.