Log file grows (Error 9002).

Hello Group,
We have a project where we store the state of object instances in Sql Server
tables. Primarely these tables consist of an id column and an IMAGE type
column. We use .NET binary serialization to create byte arrays and use
(ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
The size of a binary array is approximately 300-350k. The database is set to
automatically grow the data en log files. The recovery model is set to
SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
up. Backing up the log file helps, but... what is caution this error? Are we
using the wrong CRUD
statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
to see that the log file grows (why isn't Sql reclaiming the used (old)
space): am I missing the point of the SIMPLE recovery model?
(btw: I'm pretty sure there are no transactions 'hanging')
Many thanks in advance!
Kind regards,
Johan Bouwhuis.Try issuing Checkpoint through the application or whenever a heavy
transaction is applied...
"Johan Bouwhuis" wrote:
> Hello Group,
> We have a project where we store the state of object instances in Sql Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
> up. Backing up the log file helps, but... what is caution this error? Are we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>|||Hi,
Since the recover for your database is SIMPLE, the transction will be
cleared after each recovery interval. In your case looks like you
are doing a bulk DML operation. In this case the coomit will be done only
after completing the entire operation. To overcome this
instead of doing bulk DML operation do a batch by batch DML operation. THis
will ensure that your LDF will not grow to a higher
extend.
In SIMPLE recovery the log will be cleared automatically and you can not
perform a transaction log backup. If it is a production server then
it is recommened to go for FULL recovery model and schedule a Transaction
log backup. This will help you to recover the database fully/POINT IN TIME.
Thanks
Hari
SQL Server MVP
"Johan Bouwhuis" <JohanBouwhuis@.discussions.microsoft.com> wrote in message
news:E60F4B7A-430D-4E85-94AD-EC41A6187DE9@.microsoft.com...
> Hello Group,
> We have a project where we store the state of object instances in Sql
> Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set
> to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002
> shows
> up. Backing up the log file helps, but... what is caution this error? Are
> we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>