patternMinor
Impact of deleting 100GB table on Mirroring
Viewed 0 times
deletingimpact100gbmirroringtable
Problem
I'm about to delete a large table which is approximately 100GB in size. I'd like to understand the impact on the mirroring that's in place on this db. Can anyone confirm?
Solution
What is type of mirroring Sync or Async
Yes there would be impact and impact would be in terms of amount of transaction log being generated. Delete logs each row which is being deleted so logging will be heavy. Your approach should be to delete in batches this would minimize lock escalation, blocking and thus would not hamper concurrency much. You should also consider performing this activity when load is relatively less
How strong is your network connecting principal and mirror. You are going to put heavy load on it if you delete 100G table so becareful with that aspect also its good to ask network team to have a look on network during this period of time you would not like your mirror to lag behind principal.
Can you truncate the tables or selectively truncate tables, it is super quick and logging is very very less as compared to delete but NOTE there are limitations with truncate table and before proceeding please read below Microsoft documentation
http://msdn.microsoft.com/en-gb/library/ms177570.aspx
Yes there would be impact and impact would be in terms of amount of transaction log being generated. Delete logs each row which is being deleted so logging will be heavy. Your approach should be to delete in batches this would minimize lock escalation, blocking and thus would not hamper concurrency much. You should also consider performing this activity when load is relatively less
How strong is your network connecting principal and mirror. You are going to put heavy load on it if you delete 100G table so becareful with that aspect also its good to ask network team to have a look on network during this period of time you would not like your mirror to lag behind principal.
Can you truncate the tables or selectively truncate tables, it is super quick and logging is very very less as compared to delete but NOTE there are limitations with truncate table and before proceeding please read below Microsoft documentation
http://msdn.microsoft.com/en-gb/library/ms177570.aspx
Context
StackExchange Database Administrators Q#71958, answer score: 2
Revisions (0)
No revisions yet.