SQL Server Performance Forum – Threads Archive
How many insert, update, delete commandsHow can know know how many insert, update and delete command have been applied to each subscriber table for given day in sql transactional replciation. I want to know the growth of each tables (rows) every day. pls help. Thanks in advance.
You can try using Profiler!!! AKTHAR
Growth in terms of MB then refer to the BOL about calculating the row size, if not you can simply take a rowcount from the table. FOllow Akthar’s suggestion to see the activities you required, fyi this will have bit problem with performance if the replication process is resource extensive. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks Akthar and satya. I understand both of your ideas. I am looking for some system table stored information to get it quickly. If there is no information like that, then I can workout some idea to get it. My situation is this: There are 350 tables in the Transactional Replication. I want to know the actual transaction rate of each table every day. Is it something wrong in what I want? Is it NOT an important peace of information to know? Profiler idea is OK. I will consider it if no other options as it will take resources.. plus I have to clean it up to get what I want. The row count option is also OK, but it will not give me the UPDATE information. More over it may not be more accurate even in case of INSERT and DELETE. Am I right? There are some performance counters available but still they give information for the entire replication (total command/sec) etc.. They do not address individual tables. ———————————————————————————–
I am looking for some system table stored information to get it quickly. Some where stored in MSDB or distribution DB or in user DB etcâ€¦
———————————————————————————– Pls let me know your inputs, so that I can go ahead and use with your ideas.
Check sp_MSenum_logreader procedure and MSlogreader_history table about transaction info…but it will give insert, update and delete info… MohammedU.
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.
Thanks MohammedU for your reply. I will make use of your idea for now.