0

This question is about SQL Server replication.

I am running SQL Server 2019 v15.0.4420.2 and I get a large number of replicated tables from outside the organisation.

What I am looking for is a SQL query, if possible, on the system tables, that can tell me what tables exist and when they were last refreshed/updated/synchronised. They have a specific naming convention, being prefixed with an underscore.

I asked a DBA colleague and they gave me a list, based on the indexes. I'm no expert, and I don't have system level access, but I don't think indexes are a rock solid proxy for this query. Not all tables are indexed.

SELECT DISTINCT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    o.create_date, 
    o.modify_date,
    DATEDIFF(DAY, o.modify_date, GETDATE()) AS Age,
    --CAST(DATEDIFF(DAY, o.modify_date, GETDATE()) / 7 AS VARCHAR) AS Weeks,
    CASE 
        WHEN DATEDIFF(DAY, o.modify_date, GETDATE()) > 7 
        THEN 'Out of Synch by ' + CAST(DATEDIFF(DAY, o.modify_date, GETDATE()) / 7 AS VARCHAR) + ' Weeks'
        ELSE 'Synched'
    END AS Evaluation
FROM 
    sys.objects o
JOIN
    INFORMATION_SCHEMA.COLUMNS c ON o.name = c.TABLE_NAME 
                                 AND SCHEMA_NAME(o.schema_id) = c.TABLE_SCHEMA
WHERE 
    SCHEMA_NAME(o.schema_id) = 'dbo' 
    AND o.name LIKE '[_]%'
ORDER BY  
    Age DESC;

Is there an easy solution to this?

One key part of the problem is that I do not know what "modified date" means when applied to replication.

EDIT: Discovered this reference: modify_date datetime Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.

EDIT 2: It would seem that @CharlieFace recommendation sp_replmonitorhelpsubscription would be exactly what I would be looking for. Is there any way that I as a subscriber, and only a business user (not a sys Admin) could make use of that? Or would I have to ask the publisher for access of some sort to access this sp? Is it a admin level only? OR would it be possible to assign permissions to a read only user to do this?

7
  • Aside... the _ character is a wildcard in LIKE expressions. If your names start with underscore you probably want LIKE '[_]%' Commented Jun 5 at 2:48
  • What sort of "replication" are you using? SQL Server Replication? Or something else? Commented Jun 5 at 11:11
  • sys.objects only tracks the metadata changes, so you won't get anything with actual data modification. You probably want one of MS_*-tables residing in distribution database. There's quite good monitoring tool in ssms itself, you might wanna check it out too Commented Jun 5 at 18:28
  • 1
    I think @siggemannen is referring to learn.microsoft.com/en-us/sql/relational-databases/… note that these tables are in the msdb database. Distribution refers to the go-between DB, you should learn up the terminology and then the table setup might be clear to you. Commented Jun 5 at 19:04
  • 1
    To be honest you probably want sp_replmonitorhelpsubscription rather than querying those tables yourself learn.microsoft.com/en-us/sql/relational-databases/… Commented Jun 5 at 19:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.