I am working on a project where logs of all kinds (e.g. errors, warnings, etc.) are stored in a database table, in the same transactional database used for the other business purposes. It is a MSSQL database.

A good practice is to use a NoSQL databases to store logs, stream them and query them (e.g. using the ELK stack). However, my team doesn't have that infrastructure available; only MSSQL.

Are there any benefits of placing the log table in a database by its own? That is, the resulting database would have only one table specifically to store the logs. The log table by itself is of about 10GB.

  • 3
    The problem with using a DB to store error logs is that it itself become a point of failure. What happens if the DB is unavailable? A better approach is a hybrid option as pretty much every method you might consider is a potential failure point (event logs are limited in size, file logs are limited by desk space etc). – Robbie Dee Jan 16 at 16:00

You either keep the logs as:

1-Separate files - Not practical for query and slow.

2-One big file - Concurrency issues may arise.

3-Table / Document - Very convenient since it is better than the other alternatives.

If you have RDBMS, well, it will act pretty much as a NoSQL database in this case since you need no associations or relationships.

You could either place the log table in a separate database (will cost you a connection time, but makes good sense see @Robbie Dee's comment) or in the same database as the transnational application (faster but taxes the db server more).

Isolating the error log to a separate db may also allow for easier control of backup since it could have its own backup schedule and it may not have to be replicated as often as the OLTP data. Your DBA could judge this better than I can.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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