Should not the be an issue unless you are generating hundreds of deadlocks.

Is there a foreign key constraint, where this table is referenced?

It looks like it may be doing a cascading delete on another large table.

CXPACKET wait indicates paralellism.

How are you coming up with $4k vs $10k?

Does not sound right.

Are you trying to gather info on databases or database instances?

Is this login a user in the mirrored database(s)?

If not, then it does not need to be synced.

look at Paul Randal's note on this subject:

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

select distinct OrderNum as ngn_order_num

from Lab_result_obr_p o

join Lab_rsults_obx r on o.LabNum = r.LabNem and r.Flag = 'N' and Value <> 'TNP'

where OrderNum not in

...

poratips (7/15/2013)

---

Thanks Alan.

Somehow we have every few minutes replication is running but sometimes it's locking and users have to wait and wait so sometimes we have to kill the session...

The source of the problem could be on the SQL server side ( bad queries, stale statistics or fragmented indexes, unexpected data growth, etc.)

or on the OS/hardware side (disk/IO issues,...

Below is code my colleagues have used to check status of mirroring:

SELECT distinct d.name,

m.mirroring_role_desc,

m.mirroring_state_desc, m.mirroring_safety_level_desc--,

--m.mirroring_partner_name, m.mirroring_partner_instance,

--m.mirroring_witness_name, m.mirroring_witness_state_desc

FROM...

SELECT distinct volume_mount_point

,cast(available_bytes as decimal)/1073741824 as [Space_in_GB]

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

from http://connect.microsoft.com/SQLServer/feedback/details/642004/denali-xp-fixeddrives-does-not-display-mounted-volume-information-on-a-cluster

There maybe useful info in the SQL and Windows error logs on the remote server.

it may give you an idea of where in the stack the issues was.

After dropping all indexes and successfully bulk copying the data in, are the index rebuilds successful?

That stops transactions from being sent to the distribution database.

Instead of stopping the distribution agent first, you should stop the log reader agent on the published database.

Wait for replication to quiesce, stop the distribution agent, recreate the new subscription...