What type of query executed via the job .

what is the Wait type in the SPID . Do you observe any blocking , do you see I/O changes .

It can be captured. SQL:batchcompleted / SQLBatchstarting should be checked . :-). No need of any trigger for that .

Thanks Luis ,

However , I realized extracting xml column to .txt/.sql I lose the formatting even If I extract in .xml .:crazy:

For now I am going...

What is the version of SQL you are running and if you can post the query you ran .

Can you try something like below to see if it works for you .

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)

select <PrimarykeyCOL>,(SELECT *

FROM <Table>

for xml path('row'), elements xsinil, type

...

Thanks for question Mitesh , hope you doing good.

Which link you referred here , please elaborate .

Firstly never get Demotivated due to the kind interviewer's perception of your answers , its possible he/she may even have no idea that something that you answered even exists ....

Its advisable to install with CU updates as it contains all the hotfixes from previous issues for SQL 2008 R2 . Why to wait till issue hits and then fix...

Please ensure that you have setup the mirroring correctly , restore the Full backup with NORECOVERY .

Before proceeding with this make sure you have abundant information regarding checkdb as it comes with useful switches which can significantly cause server stress .

Go step by step for...

In my opinion it cant be due to Server upgrade , verify if you have any new code release too after upgrade .

Analyze the type of blocking happening and...

A convenient way to do is using MERGE as that works based on target server condition and you can insert , update and delete in the destination table .

Put...

Firstly how often you need to replicate and why you want to replicate . Is the data in required up to date all the time .

If not opt for...

I understand you are doing snapshots , if in your case its the change is only data not schemas . what about opting some ETL process or import/export job ....