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
    – Martin Smith
    Commented Feb 2 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.
    – AlwaysLearning
    Commented Feb 2 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?
    – Ben Thul
    Commented Feb 2 at 17:04
  • @BenThul .. Its required by the management .
    – BK Shailender
    Commented Feb 3 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.
    – Ben Thul
    Commented Feb 3 at 17:35
Related questions
3282
How to add a column with a default value to an existing table in SQL Server?
2182
How to check if a column exists in a SQL Server table
1719
What is the difference between varchar and nvarchar?
Load 6 more related questions Show fewer related questions

0

Reset to default