Check the sysservers table in master.  srvid = 0 is the local server instance.

To change it should be able to use sp_dropserver and sp_addserver, but make sure to check this out...

"Cascading Referential Integrity Constraints" in Books Online should be a starting point.

Have you lookd at the front end code that calls the offending stored procedure?  At various times I have found front end developers having put transaction around a call to...

You said it parses fine, but does it actually run?  There are a few things that will let the query parse fine, but not run.

Under the job create two schedules, one for each day.  The option is when you select Monthly under the dropdown for Frequency.

I use DDL triggers to log changes to the database, with no noticed performance degradation.  Yesterday it wrote 10196 changes to my logging table. 

Don't ask why we have that...

Try/catch should pick that up.  I'm thinking there is something else causing it to leave the transaction open.  If you look under try...catch in BOL there are some examples of...

Is the mail server set up correctly?  With the setup here the mailserver has to be set up to allow requests from the IP address of our SQL Servers.

At my previous company I had a couple of databases in the 150-250 GB range, always just used SQL to back them up to disk and never had a problem. ...

In 12 years of being a DBA, I think maybe for a year out of that have I only been one or the other.  The rest I do both the...

4-6 hours a day?  Seems a tad much if the system is not having a particularly bad day, as in maybe 4 - 5.95 hours too much.

Now, on bad days,...

Not sure this is what you are looking for, but DBCC Inputbuffer will at least give you something...