0

Result Set of MSrepl_commands

I want to infer that whether its a insert/update/delete command . We need to check at day end the amount of data replicated per article by the transactional replication. In the internet I am getting belwo result:

The type column in the MSrepl_commands table indicates the type of operation that the command represents. Here's what the different values mean:

  • 1: Insert operation
  • 2: Delete operation
  • 4: Update operation

So, if a row in the MSrepl_commands table has a type value of 1, that means the command is an insert operation. Similarly, 2 represents a delete operation, and 4 represents an update operation.

Improve this question
5
  • From a quick Google 30 means "transactional replication command" - I assume this other list you have found is for some other replication type Commented Feb 2, 2024 at 9:44
  • I'll dare to be forward and say, "it's probably unknowable." Aside from little snippets sp_MSget_repl_commands (Transact-SQL MetaData) Definition mentioning that MSrepl_commands.type includes bit flags like 0x80000000 for snapshots, and 0x40000000 for post commands, there isn't much around to identify the > 100 commands that seem to have been used. Commented Feb 2, 2024 at 10:42
  • If your goal is "inserts/updates/deletes by article", couldn't you look at number of executions on the subscriber side for the stored procs associated with those operations? You currently have a tag of SQL 2005 on the question. Which, if true, I don't know what tools are even available (XE was introduced in SQL 2008 iirc). That said, what are you going to do once you have the counts you're looking for? "Hmm... inserts on table foobar look a little high". Then what? Commented Feb 2, 2024 at 17:04
  • @BenThul .. Its required by the management . Commented Feb 3, 2024 at 10:55
  • 1
    I'd encourage you to figure out what's behind the "requirement". If only for an opportunity to satisfy the requirement in another way. But, if you're fine with management telling you to pound sand and you just pound sand, it's your career. Commented Feb 3, 2024 at 17:35
Related questions
3327
How to add a column with a default value to an existing table in SQL Server?
2204
How to check if a column exists in a SQL Server table
1752
What is the difference between varchar and nvarchar?
Load 6 more related questions Show fewer related questions

0

Reset to default

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.