SQL Server Express: too heavy workload - how to measure when to stop/continue

What kind of recovery mode do you have (Full, Simple) ?

My recovery mode is Full. In any case, the database comes back online automatically after a certain period. The data quantity is heavy I am using at the moment. I use a stored procedure which updates state information in an other table, uses a small lookup table for a key starting from a string and finally inserts the data (not that much, 4 numeric fields), but potentially 3 millions of times.

I suspect SQL Server writes all the updates first in the transaction log, and afterwards it writes it in the database tables. I saw that I could easily insert 400'000 records in less than 2-3 seconds, but afterwards the database goes in recovery mode and is not available anymore for some 30 minutes.
Any idea how to throttle the insertion, e.g. insert 100 records, check a certain state information from SLQ Server and continue when it is fully available again?
Thanks
0pl0

>> My recovery mode is Full.

And do you need full? Full is only useful when you do log backups. I think it's not your case. Use simple mode.

>> I suspect SQL Server writes all the updates first in the transaction log, and afterwards it writes
>> it in the database tables.
Don't suspect. That _is_ how it works.

>>Any idea how to throttle the insertion, e.g. insert 100 records
You can always insert a sort of delay either in your SQL script or in your program.
In SQL the instruction is, for example:
Waitfor delay '00:00:00.050'
It will stop for 50 miliseconds. I'm assuming your have a sort of loop when you can put this line.


>>the database goes in recovery mode
I don't get this part. Recovery mode is just after a failure or an unexpected shutdown in the middle of a big transaction. May be is it running out of memory for your transaction?

I'm almost sure all will be fixed by switching to simple mode.

>>After a certain size (ca. 3 GB) of the database, my application stops after some 200'000 records
I don't get this math either.
You say you have 4 numeric fields, say all are Int64, that's 8bytes x 4 = 32 bytes x 200,000 records = 6MB of data.
How did you get 3GB used ?

The size of 3GB comes from weeks of 24h/7d of data collection...
Indeed, the data quantity is not that much, but the update number lets the transaction log size increase very quickly (in one database I have just state information updates - there I have about 1GB/week of transaction log with just 5MB of database). I need the full recovery model since I'll upgrade to SLQ Server 2005 Standard or Enterprise for high availability reasons. I guess, however, that this problem will remain like this even with the Enterprise version.

I was looking around in the meantime in the SQL Server Help Online. I strongly suspect that after the 200'000 records the log is automatically flushed to the database (-> because SQL Server is issuing a CHECKPOINT command after a certain data quantity, before the changes are just written to the log, which is very lean).

I guess a possible solution could be to make a Log Backup after a maintenance stop in order to flush the transaction log, set the recovery interval to e.g. 2 hours and after succeeding in this phase put it back to 0 (automatic). Alternatively, I could issue a CHECKPOINT command after each 100'000 records in order to flush the data to the database.
Any idea?
Thanks
0pl0

By the way, I have already tried with a sleep statement in the writing thread, no way...