I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.
On SQL Server 2005, we currently log certain table changes via triggers using the inserted/deleted tables. Currently, our log tables exists in the same database as the primary tables and I was thinking that from a management perspective, moving the log tables to a different database has advantages. In the very least, log tables tend to grow at a factor of 10 for the source table, so managing the different types of tables is along different paths and separating them by db may be helpful.
If we go this route, the trigger has to log across db boundaries (same server though). What problems would this create that I don't have now?
So far, the main one seems to be that someone could take down a db independently of another (rare), and then the logging would be lost and insert would be kept, or inserts would fail b/c the trigger fails, depending on how the trigger was written.
Are there other risks?
Another solution that was offered was to log to the same db and have a job move (delete and copy) the records over to the other db. However, I still don't know why that's a better solution.