Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've searched for this online and had mixed or unclear responses.

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.

share|improve this question
    
You may also want to check out dba.stackexchange.com lots of good advice there. –  Brad Patton May 1 '13 at 0:57

1 Answer 1

Triggers have all of the drawbacks you describe. Specifically, if the database intended to receive the logs happens to be down, not only do you lose logging, but any query which would generate a log entry will fail unless the trigger is carefully written to avoid this. Using a periodic job to copy the log data across to the receiving database doesn't have that drawback; if the receiving database is down, then you don't lose logs (because they're still on the originating database, waiting to be copied), and you also don't have to worry about failed logging causing queries to fail.

Have you done any benchmarking to confirm whether or not such a solution is really necessary? Are you short of disk, or somehow otherwise constrained in such a way that the extra effort and complexity of logging to a separate database is worthwhile?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.