Can't contact the SQL server, so let's log it in the SQL server!



  • The projects I'm working on right now all use a single .NET WCF/SQL Server backend to manage web services and data. For debugging and error tracking, I have a table in the application's database (hosted in SQL Server 2008) which simply logs exceptions. If any of the WCF methods fails out it logs the exception to this table.

    Today I found an error in that log. The error? System.Data.SqlClient.SqlException with a message of "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections." So evidently the web service wasn't able to contact the server to retrieve application data, but it was able to contact the server to log the error.

    The code to create a database connection is all in a single method so we can easily modify the connection for production use. The code that errored and the error logger both use that method.

    This reminds me of those naive error loggers we've seen on TDWTF before that recursively call themselves if the error logger fails. Except this time it worked.



  •  Might wanna check if someone else wrote some incorrect connection code elsewhere.


  • Trolleybus Mechanic

    So there was a hiccup which killed the connection. The logged restablished the connection and logged the error. Maybe I'm missing something, but is that not a good thing?

     



  • @Lorne Kates said:

    So there was a hiccup which killed the connection. The logged restablished the connection and logged the error. Maybe I'm missing something, but is that not a good thing?

     

    This is what I assumed too, though our network has been rock-solid and I've never seen a hiccup in it before (well, excluding our Internet connection which is from an ISP with a horrible reputation, but this was all LAN traffic at this point). It was just ironic and one of those "shouldn't be possible" things so I thought I'd share.

     



  • This typically happens because you aren't properly closing your connections in the code. So the main code fails but the failure gives the connection pool time to close out a connection and open a new one up for your logging.

    It's .Net, so I'm going to take a guess here that you don't have the connection / command objects wrapped in using() statements.

    Usually this isn't handled correctly when devs decide that creating their own DAL class which wraps the ado.net connection/command objects is a good idea. It's not.



  • We're using LINQ to SQL Classes for all database connections. I've never had to deal with transactions or closing connections and such using that framework before, it seems to take care of it all automatically. Oh, and I just remembered the SQL Server is also the web server in this case (it's a dev server) so I think it uses named pipes for the connection instead of TCP/IP over a LAN. I could be wrong though.



  • I'm not a Windows person, but it seems to me like logging exceptions to the DB is bad. I would always log to a local file.



  • @morbiuswilters said:

    I'm not a Windows person, but it seems to me like logging exceptions to the DB is bad. I would always log to a local file.

    A couple years ago I'd say to use the built-in event logging functionality, but now the Event Viewer is so awful...



  •  usually with these errors its a one time chance sort of thing.  Its not uncommon to check for the error and simply retry the query.  Same process as handling 1205 errors (deadlock).  They can result from many things not just unavailable connections in the pool.  Its rare though for it to last more than a few ms though.

     Logging to the DB is fine.  Its nice to have a central repository for all your applications.  You can then turn around and analyze that data.  Always have a backup log though (event log, file, etc) in case the insert fails.



  • @morbiuswilters said:

    I'm not a Windows person, but it seems to me like logging exceptions to the DB is bad. I would always log to a local file.
    I'd much rather have all my servers in a cluster log to one spot, so that I don't have to go to every machine to look for something.



  • @Sutherlands said:

    @morbiuswilters said:

    I'm not a Windows person, but it seems to me like logging exceptions to the DB is bad. I would always log to a local file.
    I'd much rather have all my servers in a cluster log to one spot, so that I don't have to go to every machine to look for something.

    syslog? Or just write a script to aggregate all of the logs together? The problems with logging to the database are: 1) Still have to fall back to a file log if the DB connection fails. Why have 2 places for logs?; 2) Adds unnecessary write load to DB. I'd rather keep the DB as lean as possible because it's the hardest part to scale out. Also, if there's some failure that generates a lot of log entries it will quickly overwhelm the database and can take it down as well.; 3) Adds unnecessary size to the DB. More to back up. Of course, this may not be a huge problem if you prune religiously, but then you can't keep as much historical information.

    Logs can get huge. I've had the pleasure of refactoring applications which logged to the database and ended up in a situation where there were hundreds of gigs of log entries cluttering up the DB and slowing everything down. If you absolutely need your log entries in a database, then load them to an offline database.



  •  I agree that it's a connection closing issue. I've seen that a few times where connections are not being closed timely.  It's probably one of two things.

    1) connections not being closed because of application crashe, since unless the connection is wrapped in a try block dispose function won't get called.

    2) not implementing using statements to make sure the object is properly disposed.

     

    I'd check the error log to see if you have timeout exceptions being thrown before that one.



  • @morbiuswilters said:

    Stuff
    My response to that can be boiled down to: Logging has its own DB server.



  • @Sutherlands said:

    @morbiuswilters said:

    Stuff
    My response to that can be boiled down to: Logging has its own DB server.

    Better, but still has two issues:

    1. Failed connections need their own separate log, so you still have logs in 2 places.
    2. I'm assuming you're doing a lazy load connection to the logging DB so you don't have to have an extra DB connection for every page load/request/etc. However, it seems like it can introduce a single point of failure if your logging DB goes down and your clients have to sit there and wait for the connection to timeout.


  • @morbiuswilters said:

    @Sutherlands said:

    @morbiuswilters said:

    Stuff
    My response to that can be boiled down to: Logging has its own DB server.

    Better, but still has two issues:

    1) Failed connections need their own separate log, so you still have logs in 2 places.
    2) I'm assuming you're doing a lazy load connection to the logging DB so you don't have to have an extra DB connection for every page load/request/etc. However, it seems like it can introduce a single point of failure if your logging DB goes down and your clients have to sit there and wait for the connection to timeout.
    1) We don't log in 2 places*

    2) We drop to MSMQ which handles writing to the DB**



  • @Sutherlands said:

    1) We don't log in 2 places*

    Then how do you log failed log events?

    @Sutherlands said:

    ** Actually we drop to MSMQ which calls a WS which drops to MSMQ which logs to the DB

    That sounds extraordinarily enterprise-y. Why can't MSMQ log directly to the DB?



  • @morbiuswilters said:

    @Sutherlands said:
    1) We don't log in 2 places*

    Then how do you log failed log events?

    Yes... well... about that...@morbiuswilters said:
    @Sutherlands said:
    ** Actually we drop to MSMQ which calls a WS which drops to MSMQ which logs to the DB

    That sounds extraordinarily enterprise-y. Why can't MSMQ log directly to the DB?

    There's a cluster dedicated to logging things... you just call the webservice and it handles it... but then you want to drop to a queue so you can call that WS async...



  • @morbiuswilters said:

    I'm not a Windows person, but it seems to me like logging exceptions to the DB is bad. I would always log to a local file.
     

    I do both. "application logging" (only to be read by engineers) go to the file, like they should. "Human readable" or functional logging goes to the database and can be read out through some web interface. The technical logging might include the error that occured, the functional logging might tell "It didn't go so well while doing this and that" - any information that might give the person managing the client processes the idea what needs to be done to resolve the situation. The most interesting logs I store in the database are "I did or did not do this, BECAUSE" - especially the BECAUSE part keeps people away from constantly asking me questions they should be able to answer themselves 2 years after I last touched the stuff.



  • @morbiuswilters said:

    I'd rather keep the DB as lean as possible because it's the hardest part to scale out..

    I would say that DB is the easiest thing to scale up or out or both....not cheap, but easy.



  • @blakeyrat said:

    but now the Event Viewer is so awful...

     

    What's so bad about it?  I've used it a couple times and all I noticed was "oh, the old sections are now one level further down".  (Not a rhetorical question; I'm an application dev, not a sysadmin, and have never had occasion to use any version of Event Viewer on a regular basis.)

     




  • @emurphy said:

    What's so bad about it? I've used it a couple times and all I noticed was "oh, the old sections are now one level further down". (Not a rhetorical question; I'm an application dev, not a sysadmin, and have never had occasion to use any version of Event Viewer on a regular basis.)

    I don't know, maybe THIS STUFF we're talking about 3 threads down in the list?

    That said, I was being facetious, of course you use the OS' event logging facility. It was a joke, that I figured people would get, because we were discussing it in the other thread.



  • @blakeyrat said:

    @emurphy said:
    What's so bad about it? I've used it a couple times and all I noticed was "oh, the old sections are now one level further down". (Not a rhetorical question; I'm an application dev, not a sysadmin, and have never had occasion to use any version of Event Viewer on a regular basis.)

    I don't know, maybe THIS STUFF we're talking about 3 threads down in the list?

    That said, I was being facetious, of course you use the OS' event logging facility. It was a joke, that I figured people would get, because we were discussing it in the other thread.

     

    There is no app to cross-reference threads yet, so don't expect too much.

     


  • Trolleybus Mechanic

    @morbiuswilters said:

    Logs can get huge. I've had the pleasure of refactoring applications which logged to the database and ended up in a situation where there were hundreds of gigs of log entries cluttering up the DB and slowing everything down.
     

    I have the pleasure of working on a system that logs all DB queries to the db (thankfully avoiding an infinite loop). It does this by saving the full text of the query to an unindexed table, with a half-assed attempt to only log changing queries.

    The system is used infrequently, then in spurts by ~2000 users rushing to meet a deadline. The system is a series of forms, each with 10-20 questions. The system saves these forms each time the users navigate between pages. The system is not smart enough to do any sort of stale-data checking. The system runs one query for each question, rather than each form.

    2000 users-- 50 questions-- multiple navigations. Once it hit about half a million rows, it hit its max allocation, and MSSQL server died. The rest of the space was being taken up by the database transaction logs.

    I did mention a while ago, when this "database audit" scheme was being cooked up, that it was a real shame there wasn't any built in way of the DATABASE to LOG all the TRANSACTIONS that go through it. A real, huge, sarcastic shame.


  • Trolleybus Mechanic

    @erikal said:

    @blakeyrat said:

    I don't know, maybe THIS STUFF we're talking about 3 threads down in the list?

     

    There is no app to cross-reference threads yet, so don't expect too much.

     

    There is, but it's written in Java. So there effectively isn't.

     



  • @Lorne Kates said:

    @morbiuswilters said:

    Logs can get huge. I've had the pleasure of refactoring applications which logged to the database and ended up in a situation where there were hundreds of gigs of log entries cluttering up the DB and slowing everything down.
     

    I have the pleasure of working on a system that logs all DB queries to the db (thankfully avoiding an infinite loop). It does this by saving the full text of the query to an unindexed table, with a half-assed attempt to only log changing queries.

    The system is used infrequently, then in spurts by ~2000 users rushing to meet a deadline. The system is a series of forms, each with 10-20 questions. The system saves these forms each time the users navigate between pages. The system is not smart enough to do any sort of stale-data checking. The system runs one query for each question, rather than each form.

    2000 users-- 50 questions-- multiple navigations. Once it hit about half a million rows, it hit its max allocation, and MSSQL server died. The rest of the space was being taken up by the database transaction logs.

    I did mention a while ago, when this "database audit" scheme was being cooked up, that it was a real shame there wasn't any built in way of the DATABASE to LOG all the TRANSACTIONS that go through it. A real, huge, sarcastic shame.

    SQL Server has much better options available.  It sounds like someone didn't know what they were doing. Change Data Capture, etc

     Also i'm assuming your in full revery model when you mention your transaction logs being huge.  If you are in full you need to make sure to backup you log at regular intervals or else it will keep on growing till you run out of space.

     



  • @Lorne Kates said:

    @erikal said:

    @blakeyrat said:

    I don't know, maybe THIS STUFF we're talking about 3 threads down in the list?

     

    There is no app to cross-reference threads yet, so don't expect too much.

     

    There is, but it's written in Java. So there effectively isn't.

     

     

    Hmm, language barrier? In the NL the term "app" automatically refers to some simplistic thing you run on your phone. Perhaps I should have said mobile app; my failed attempt at making a wise-crack about people breathing through their phone nowadays.

     


  • ♿ (Parody)

    @erikal said:

    Hmm, language barrier? In the NL the term "app" automatically refers to some simplistic thing you run on your phone. Perhaps I should have said mobile app; my failed attempt at making a wise-crack about people breathing through their phone nowadays.

    Hmm...humor barrier? Sometimes jokes require deliberate misunderstandings or inappropriate context switches. Perhaps he should have said, "These are the jokes."



  • @erikal said:

    @Lorne Kates said:

    @erikal said:

    @blakeyrat said:

    I don't know, maybe THIS STUFF we're talking about 3 threads down in the list?

     

    There is no app to cross-reference threads yet, so don't expect too much.

     

    There is, but it's written in Java. So there effectively isn't.

     

     

    Hmm, language barrier? In the NL the term "app" automatically refers to some simplistic thing you run on your phone. Perhaps I should have said mobile app; my failed attempt at making a wise-crack about people breathing through their phone nowadays.

     

    Android apps are written in Java.



  • @TheCPUWizard said:

    @morbiuswilters said:

    I'd rather keep the DB as lean as possible because it's the hardest part to scale out..

    I would say that DB is the easiest thing to scale up or out or both....not cheap, but easy.

    What the fuck are you talking about? For one thing, database scaling is expensive because it is hard. If it was easy to scale databases horizontally, it wouldn't cost an arm and a leg. Second, this statement is just wrong. You think the DB is easier to scale than app servers, which can be scaled infinitely horizontally with commodity hardware? What are you smoking?


Log in to reply