SQL Server rolling forward lots of transactions, what should I look at?

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.

Update 1

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.

more ▼

asked Oct 16, 2009 at 04:25 PM in Default

avatar image

Anthony D
21 1 1 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 16, 2009 at 04:49 PM

avatar image

Steve Jones - Editor ♦♦
5.2k 79 94 87

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 16, 2009 at 07:08 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2009 at 04:25 PM

Seen: 1423 times

Last Updated: Oct 16, 2009 at 05:00 PM

Copyright 2018 Redgate Software. Privacy Policy