I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases. I want to know how much data is transfered when operation is doing. How can I do it? My local database is Sql server 2008 and remote database is SQlserver2ksp3
This sounds like a job for PERFMON, which is included with the latest windows server offerings.
Basically you're going to setup a counter to watch the MSSQL data transfer between the servers.
You may be able to use a counter inside of MSSQL to track this as well, but I'm not familiar with any functionality that provides that.
You're pretty much going to have to use networking tools to do the monitoring. Another option is to query the size of the tables involved directly on the linked server and that will give you a rough estimate. If you're joining across linked servers, all the data must be brought over, then filtered down, so you're probably transferring a lot more data than you think.