I am running SQL Server Express on a Windows XP Embedded box. It runs for a day or two, doing some transactional processing for a POS type system, and with another system pulling data out to an OLAP DB for processing. After a while, I see in the event viewer the sequence SQL Server puts out when it restarts, copy rights, command line parameters, and so on. It seems like that coincides with our OLAP process crashing. I then see that when it restarts our transaction DB, it does a recovery, pulling in 10K or so in transactions that need to be rolled forward. Does this mean SQL has crashed? I don't really see much to indicate what happened.
I noticed I have my memory limit set to 1MB per query and 2TB for the server. These are the defaults. I only have one GB in the box. We have seen SQL crash a whole box by just using all the system memory. In this case though the whole box is up when we get to it.
rollforward is part of the crash recovery. These are items that were written to the log, but didn't get to disk on the data files and the transaction needs to be completed.
It sounds like you are overloading the system and a large transaction might be causing it to fail.
answered Oct 16 '09 at 04:49 PM
Steve Jones - Editor ♦♦
Something sounds not right to me here. Steve is absolutely correct - but - 10,000? This sounds to me like you might unintentionally be leaving some transactions open somewhere - because I very much doubt that there would be 10,000 transactions in-flight at any given time on a SQL Server which is running on XP Embedded...
I would try and get a profiler running against it - and also try and regularly poll the server and perform sp_who2 and DBCC OPENTRAN to get some information about what might be going on...
answered Oct 16 '09 at 07:08 PM
Matt Whitfield ♦♦