Remove ROWLOCK hint and index in a TABLE A TableAStatusTypeID column

http://msdn.microsoft.com/en-us/library/ms186385.aspx

in this link

Look at the theese sample you can use theese sample to send e-mail graph of xml

only you have to write send e-mail

In here

@command= N'INSERT INTO DeadlockEvents

...

You can write deadlock events in a table it gives you a xml

SQL SERVER 2012 File Table Solution

http://msdn.microsoft.com/tr-tr/library/ff929144.aspx

Is connections come from SqlProd1?

Task Manager -> Resource Monitor ->Disk-> Filter the sql server.exe and then look at the ratio can you see mdf and ldf file of mirror db....

for windows 2008 server

I have 700 gb db

I think it is related your encreptyted procedures

I think triger is best solution in sql server 2005 i used it about 20 million record table logging

but you can look at this

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx

if i were you Firtly i make maxdop 1 and i set automaticly set processor affinity

and automaticly set i/o affinity

Thanks after i wrote post i think the same if log file need to growth it affect the performance

I think No because .net 4.0 is support 3.5

Restart the engine tempdb drop and recreate it self

You can give persmion temp folder SQLServerSqlAgentUser$InstanceName