3

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.

smdrager
  • 7,327
  • 6
  • 39
  • 49
  • 1
    Biggest drawback i see is, if there are problems due to...say...being unable to connect to the database, you might never know it (cause the app couldn't connect to the database to log anything). A log file has fewer moving parts, and thus less chance of being unable to do its job. – cHao Jun 14 '12 at 19:50

5 Answers5

3

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).

usr
  • 168,620
  • 35
  • 240
  • 369
  • Yeah, that's why I'm confused about his hesitation to have it in SQL. I have used similar structures in the past for other sites with no issues. – smdrager Jun 14 '12 at 17:49
  • 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. – usr Jun 14 '12 at 17:52
  • Good point. Didn't think about that. I thought this type of logging was incredibly common, and wanted to make sure. – smdrager Jun 14 '12 at 18:02
  • I think it is *not* common. I have never seen it, only done it myself. I think flat file logging is an anti-pattern under many circumstances. – usr Jun 14 '12 at 18:04
  • I meant SQL logging being common. I have never seen anyone do flat file logging of user activity either. – smdrager Jun 14 '12 at 18:07
3

Perhaps he was referring to using triggers for logging as bad practice. Triggers can cause a lot of unexpected effects, and can be considered bad practice.

Other than that SQL is commonly used as storage for logs, so (providing you have enough storage) there is nothing wrong with this.

Community
  • 1
  • 1
m.edmondson
  • 30,382
  • 27
  • 123
  • 206
  • He made this comment after I requested tables associated with user actions as I described above (no triggers, just simple inserts with a stored proc). – smdrager Jun 14 '12 at 17:46
  • It still adds to weight of the user transaction. Instead of one record the DBMS has to process two. Or more. These will all need buffer space and locks (and I/O bandwith) – wildplasser Jun 14 '12 at 18:11
3

There is the problem of transactions. Logging will be part of the transaction, so if it is rolled back, the log is gone, too.

For logging in SQL to work, you will need to set up a separate DBMS session which will complicate things more. Flatfiles don't have transactions. They also fail (reasonably) gracefully on disk-full conditions. And you can manage (purge) them without interfering with transactions.

And, since logging is basically instrumentation, it is good not to interfere more than necessary with the process being monitored.

Finally: if you really would want the ease-of-use of SQL, you could always choose to re-import the flatfiles into a DBMS table. In a separate transaction.

BTW: the above answer is not about maintaining history. That is a totally different ballgame which should be considered part of the DBMS+application.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • In this case, the logging is done by the application as a separate SQL command, after the event. It does increase load on the SQL server slightly, but does not add load time for the end-users, as it is asynchronous. – smdrager Jun 14 '12 at 18:03
  • In that case, it is even worse, because it has to perform "polling", looking for new updates, compared to some watermark timestamp that the logcollector maintains. – wildplasser Jun 14 '12 at 18:06
  • Maybe polling is not the correct term, but in essense it is polling, if it is scavenging updates after the fact. How does it recognise touched records? – wildplasser Jun 14 '12 at 18:13
  • It's not logging SQL, it's logging the user's activity... in SQL. So for example if they try to update an address they have on file... it will update it in SQL via a stored proc. If that is successful (no SQL errors and the sproc returns a successful indicator), it (the web server) will call a sproc to log the action and success. If it fails (SQL error failure response code), it will callthe sproc to log the action as an attempt (and failure). It gives a sort of story of the user's important actions around the site. – smdrager Jun 14 '12 at 18:17
  • It still puts a load on the users transaction. Writing two records instead of one. And rolled-backed actions will have the log rolled back, too, so you won't see them. If you fail to realise that, I'm afraid your DBA is right... – wildplasser Jun 14 '12 at 18:22
  • 2
    They are two separate calls to DB (separate transactions)... I thought I made that clear in the explanation above. And of course it is two records. – smdrager Jun 14 '12 at 18:48
  • No, you did not make that clear. Separate transactions? You mean the logging process has to connect to the DBMS as a separate process? How does it detect and catch the events it is intersted in, than? – wildplasser Jun 14 '12 at 18:52
  • Logging does not HAVE to be part of the transaction. Yes that is an option but not a requirement. Could even use the log to record a failed insert or update. – paparazzo Jun 14 '12 at 20:11
3

It looks like your DBA would rather have all logging (SQL or application) go to a flat file to use grep. I disagree entirely. I personally find SQL a heck of a lot easier to search than using grep. The clause

  WHERE event_time BETWEEN '1/1/2012 1:00AM' AND '1/1/2012 1:10AM'

is a lot easier to write and faster to retrieve (assuming indexing) than anything you're going to get from grep.

Microsoft doesn't believe that logging is bad to a DB, their Enterprise Library supports it: http://msdn.microsoft.com/en-us/library/ff664543(v=pandp.50).aspx

And IIS supports it as well, and most major logging libraries include database support.

The storage issue isn't necessarily an issue per se, you're either going to log to a file in your app, or log to a DB file in your DB. The main issues I see are whether it becomes prohibitively expensive while you're doing your transactions, and as mentioned above, the issue that a rolled back transaction causes rolled back logging.

Triple Gilaman
  • 463
  • 3
  • 10
0

I've not heard it described as 'bad practice' before, and you can always set up a job to clear out records over a certain age (eg. we only keep the last month's worth of logs). I guess it also depends who's going to be viewing the log as not all Application Support staff know SQL, whereas they could easily read through a text file.

Marcus K
  • 779
  • 1
  • 10
  • 22