Results 1 to 12 of 12
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Activity Monitor or logged data changes

    I am curious to know if there is a way that SQL Server (or actually SSMS) can show me who is connected to my database in SQL Server?




    Other questions I guess I have, can SSMS show me any sort of data logging activity?
    Meaning as something that is built into its features, not requiring me to set up tables and create anything.
    can it tell me if a record was changed, when it was change, who changed it.
    or can it even tell if objects like a new table was created, when and by who.


    I have read some conversations about the activity monitor (but the conversation was back and forth and it got beyond what I could understand)

    and I have a book "SQL Server 2019 A Beginners Guide" (first we should talk about properly defining a beginner)
    but the book talks about the system catalog and catalog views but quickly out paces me.

    As always thanks for any information or help

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Last edited by June7; 07-30-2021 at 11:13 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    to view who is connected and all running processes (SPID's) :

    Code:
    exec sp_who2
    You can find information about the database objects in the sys views, but easier to find and use are the build-in reports:
    Click image for larger version. 

Name:	SQL_standardReports.jpg 
Views:	32 
Size:	142.0 KB 
ID:	45866
    You can find these reports on server level (performance dashboard, configurations changes history, memory usage, ...) or on database level (see included screenshot). There is a lot of information there.

    To track the queries and activity that are actually running: use the SQL server profiler

    To track and store data changes: create history tables and use insert/update/delete triggers to fill them. Be aware that this creates a lot of overhead, so only track the most critical data.

    And then there are always third party tools, but before you spend your money: check first the tools that are already there.

    Kind regards
    Noëlla

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    SQL server has inbuilt row and data level change tracking depending on your requirements.
    These are SQL version dependent - article links https://docs.microsoft.com/en-us/sql...l-server-ver15

    Data level changes https://docs.microsoft.com/en-us/sql...l-server-ver15

    Row Level Changes https://docs.microsoft.com/en-us/sql...l-server-ver15
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    nice tip, thx Minty

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @NoellaG
    Thanks for the reminder about all the built in standard reports.
    Your screenshot includes Memory Usage By Memory Optimised Objects.
    I have SSMS v18.7.1 for SS Express and that item is missing. Is that an option I can enable or is it in paid versions of SS only?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    As thge SSMS is the same for SQL express and the full version, the option should be available in both, starting from SQL version 2014. The screenshot I showed are the standard reports for the database. Check if you're not looking at the standard reports for the server, there you find a bunch of different options.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @NoellaG
    Yes I realise that SSMS is the same whichever version of SS is in use but this is what I see when I right click on any of my databases....

    Click image for larger version. 

Name:	CaptureSSMS.PNG 
Views:	24 
Size:	59.1 KB 
ID:	45880

    That's why I wondered if the functionality of SSMS is dependant of the edition of SS used. In my case Express
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by isladogs View Post
    That's why I wondered if the functionality of SSMS is dependant of the edition of SS used. In my case Express
    Hi Colin,

    Yes SSMS adapts to the Server version, it's quite clever like that.
    I can be attached to an Azure data base and a clients v12 SQL Server, and SSMS seamlessly adjusts the features available as you hop between them.

    Hope that helps.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks Trevor that's clever...�� (sorry I couldn't resist!)

    Pity though as that menu item looked interesting.
    I wonder why I got two additional menu items with SSMS and SS Express.
    As those are both standard reports, perhaps it lists those I've used recently?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    sorry I didn't answer here, but my husband was in hospital, undergoing a liver transplant, so I was quite elsewhere with my attention. Hubby is coming home now, resistant as a cockroach that one .
    The recent used reports are shown below the line, a bit as recent used files in office.
    * Top = standard reports that give access to all Microsoft installed reports
    * below first divider line = custom reports: you can create some reports yourself (be sure all parameters have default parameters) and add them to the list
    * below the second divider line = list of most recently used reports

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problem. Hope the recovery goes well.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Logged On? How to show who's Logged On...
    By Kaloyanides in forum Programming
    Replies: 3
    Last Post: 02-24-2018, 04:51 PM
  2. Production Performance Monitor
    By tazui1982 in forum Database Design
    Replies: 0
    Last Post: 09-23-2017, 07:06 AM
  3. Updating Activity Records with additional data
    By acalkins1957 in forum Queries
    Replies: 1
    Last Post: 07-29-2014, 11:40 AM
  4. Replies: 6
    Last Post: 08-21-2012, 11:17 AM
  5. Monitor who does what
    By greggue in forum Security
    Replies: 2
    Last Post: 10-04-2010, 07:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums