Is logging in SQL a bad practice? [closed]

Question

I work at an eCommerce company. Our DBA recently told me that using SQL for logging is a bad practice, and recommended a flat file and grepping instead. I had never heard that logging in SQL was a bad practice before and I don't find anything online that confirms this.

When I say logging, I mean logging user actions like signing in, changing account information, etc, and data like their user agent, ip address, account id, and event information.

While it would lead to a lot of rows over time, it makes it extremely easy to search if there is an issue with a customer.

Is logging in SQL a bad practice, and is it preferable to log to a file?

Thanks.

Answer 1

The only problem with doing that is increased load on the database and increased disk space. Apart from that there is nothing preventing you from doing that. Actually, it is very convenient to be able to query your logs and to have them consistent and easy to backup.

You can put a little more structure into your logs with SQL compared to flat files. For example you could have the following columns:

• ID
• DateTime
• EventCode
• Message
• MachineName
• HttpRequestID
• UserName
• UserID
• AdditionalDataXML: a queryable XML column with structured data about the event

Very convenient. I recommend it.

Why does your DBA resist? Well, he has no downside talking you out of it. He doesn't get the benefit of having a nice logging table. But he'd have to maintain it. The incentives are asymmetrical.

As a mitigation you could have a nightly TRUNCATE TABLE clear out the log items. Maybe export them before doing that (bcp.exe).