You can monitor the locks on sometable by sp_lock.

Depending on the situation you could move EXEC master..xp_cmdshell 'start C:\FileBuilder.exe', NO_OUTPUT to an sqlserver-job and start it fom your trigger.

have you tried UNC? \\computername\....

have you tried UNC? \\computername\....

The data is available though windows performance counters or master.dbo.sysperfinfo

Thanks, I'll have a look into DBCC PAGE

You can use the sqliostress utility http://support.microsoft.com/kb/231619/.

Does normal copy works well?

Doublechecked SAN-configuration?

They both allow to run the server unpatched. Without sp3 or later you won't be able to connect to it with TCP/IP and perhaps other network protocols.

Some generics:

Select the least amount of data you need.

Check your query plan (sql query analyzer ->display estimated execution plan)

Try to avoid cursors and work out a set-based solution.

Try to normalize...

Are there +20 jobs active at the same time? Sql server agent has a limited pool of threads.

That is quite odd since it is a parameterless procedure. Does the executionplan changes when you execute it under sqlagent's permission? Is there additional locking? Are the connectionsettings the same...

What is the error you get? It looks fine at first sight (although you are not specifying the owner of PROD_MAST_SUBS and are not using stored procedures for abstraction)

an example

ALTER TABLE dbo.Table1

 DROP CONSTRAINT CK_Table1

Batches are a whole serie of sql-statements sent at once by the client.

Stored procedure can contain a whole serie of sql-statements stored on the server.

Pro batchs: some flexibility (like search-commands)...

Also try to monitor the locks on the table. Any transaction taking longer than required?

Drop constraint:

ALTER TABLE table DROP CONSTRAINT constraintname

Describe constraint:

sp_helptext [ @objname = ] 'constraintname'