Background:
I have a DB which is used by multiple software/apps.
The number of connections on my DB spiked from 1000 to 1500 yesterday for 30 mins and then came back to 1000. The average no. of connections remain around 1000 at any given day. Same happened today at same time as yesterday.
Question:
How may I know now the details of connections that were established at that time yesterday and today so that I may know which software/app caused the spike?
SQL doesn't keep a connections log . However you should be able to set up auditing to log them going forward.
Can you please guide how can I setup auditing to log them going forward?
I want to do that so that if same happens tomorrow then I may know what's causing that?.
If you want to log them (e.g. into a table for subsequent analysis) then a LOGON TRIGGER
https://msdn.microsoft.com/en-us/library/bb326598%28v=sql.110%29.aspx
You can turn on Successful and/or failed logon logging in SQL Error Log, but you'd probably have a hard time analysing it from there
You could also spy on the logins using SQL Profiler - again, not easy to analyse that but would enable you to see them in real time which might cause you to spot what the cause is.
Auditing is easy:
in SSMS under Security/Audits, define a new Server audit
then, define a new Server Audit Specification, and link it to your new server audit
Define your output (can be to a file)
Specify Successful_Login_Group.
Enable the Server Audit and the Server Audit specificaion
To view the file as a table use function fn_get_audit_file
Thank you Kristen and gbritton. Going to try these.