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

log file for database tempdb is full

Hi

I am getting this common error once or twice a day:

Error: 9002, Severity: 17, State: 2
The log file for database 'tempdb' is full. Back up the transaction
log for the database to free up some log space.

provided.....

1. My log file drive has more than 20 GB free out of 30 GB
2. Both data file & log file has default setting on unrestricted file
growth by 10%
3. Currently we moved from SQL 7.0 to SQL 2000 & the load in the user
side also doubled
4. We can't do the temporary solution like restarting the server or
SQL service, because the application is a real time system with much
less manual interaction.

Thanks in advance.

Regards
SeniSenthuran (senthurs@.yahoo.com) writes:
> Error: 9002, Severity: 17, State: 2
> The log file for database 'tempdb' is full. Back up the transaction
> log for the database to free up some log space.
> provided.....
> 1. My log file drive has more than 20 GB free out of 30 GB

It appears that you have some operations that take a serious load in
tempdb. Could be worktables, could be temptables that grow a lot,
and which see a lot of updates. I am afraid that you need to track
down which operations this might be.

When you say that there is 20 GB free, is this just when you have
gotten this message, of after you have restarted SQL Server?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Log Entry - 7105, Severity: 22, State: 6

Following error on server running

SQL Server 2000 - SP3
No clustering or log sharing

From SQL Server Log

Error: 7105, Severity: 22, State: 6
Page (1:111315), slot 26 for text, ntext, or image node does not exist..

From Event Viewer - Application Log

The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Error: 7105, Severity: 22, State: 6
Page (1:111315), slot 26 for text, ntext, or image node does not exist..Run SQL Rebuild Registry from installation cd.

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
>> I get an error message:
>> "Msg 9002, Level 17, State 2, Line 1
>> The transaction log for database 'My_db' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases"
>> The collumn log_reuse_wait_desc says: LOG_BACKUP
>> There is not much free space on the file server. How can I force or
>> set the option so that I can run the query? I don't need any backup
>> (data or log) either.
>> Thanks.
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.
On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).
|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
Regards
Steen Schlter Persson
Database Administrator / System Administrator