I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?Mike,
Perhaps the database space filled up as one table attempted to grow again.
Tables that still had some space left in them could take a few more rows,
then they were filled up as well and would no longer take any more rows.
Etc. Etc.
If this is the case, either allocate more space or turn on autogrow for the
database in question.
With regard to your closing comment, checkpoint is not equal to a commit.
Without knowing the period covered by the 18,000 records and something more
about your server it is not possible to offer details. However, I would
think that checkpointing was working just fine.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
> I have a service that writes a lot of records to many
> tables in SQL server. Recently, the service failed in
> writing record to any of the tables. But, the retrieving
> of the records is still OK. By examining the application
> log file, it seems that the SQL server started to
> deteriorate by not allowing insertion in one table at a
> time and eventually all tables are rejected for insertion.
> I ended up re-booting the system and everything went back
> to normal. After the reboot, I checked the SQL log file
> and noticed that about 18,000 records were rolled forward
> during SQL Server start-up.
> Does anybody have any idea as why the insertion would
> fail? And why the checkpoint was not executed even there
> are about 18,000 committed transactions?
>|||Russell - Thank you for your reply.
I was also thinking about the possible disk space problem
but I don't think it's the case here. There was about 10G
left in the disk when this happened. At the same time, the
database files (data & transaction) are set to auto-grow.
If disk space was the problem, I would think rebooting the
server couldn't solve the problem. Based on my
calculation, in 5 mins period, properly 3500 record would
have been inserted to the table.
>--Original Message--
>Mike,
>Perhaps the database space filled up as one table
attempted to grow again.
>Tables that still had some space left in them could take
a few more rows,
>then they were filled up as well and would no longer take
any more rows.
>Etc. Etc.
>If this is the case, either allocate more space or turn
on autogrow for the
>database in question.
>With regard to your closing comment, checkpoint is not
equal to a commit.
>Without knowing the period covered by the 18,000 records
and something more
>about your server it is not possible to offer details.
However, I would
>think that checkpointing was working just fine.
>Russell Fields
>"mike" <mike@.ftsl.com> wrote in message
>news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
>> I have a service that writes a lot of records to many
>> tables in SQL server. Recently, the service failed in
>> writing record to any of the tables. But, the retrieving
>> of the records is still OK. By examining the application
>> log file, it seems that the SQL server started to
>> deteriorate by not allowing insertion in one table at a
>> time and eventually all tables are rejected for
insertion.
>> I ended up re-booting the system and everything went
back
>> to normal. After the reboot, I checked the SQL log file
>> and noticed that about 18,000 records were rolled
forward
>> during SQL Server start-up.
>> Does anybody have any idea as why the insertion would
>> fail? And why the checkpoint was not executed even there
>> are about 18,000 committed transactions?
>
>.
>|||Mike,
Sorry that I do not have a better idea.
We had problems at one time when another process besides SQL Server was
using the same disk. It wrote LOTS of data into an operating system file
eating up the disk, then either deleted it again or ran out of space itself,
causing the file write to abort. Since you have 10 GB free, then it is hard
to believe that a similar thing is happening to you.
Regarding checkpoints, this is set using the "recovery interval" option.
You might read up on this and discover that the timing of checkpoints is
more complicated than you would have thought.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:635a01c3e5ef$46160ae0$a501280a@.phx.gbl...
> Russell - Thank you for your reply.
> I was also thinking about the possible disk space problem
> but I don't think it's the case here. There was about 10G
> left in the disk when this happened. At the same time, the
> database files (data & transaction) are set to auto-grow.
> If disk space was the problem, I would think rebooting the
> server couldn't solve the problem. Based on my
> calculation, in 5 mins period, properly 3500 record would
> have been inserted to the table.
> >--Original Message--
> >Mike,
> >
> >Perhaps the database space filled up as one table
> attempted to grow again.
> >Tables that still had some space left in them could take
> a few more rows,
> >then they were filled up as well and would no longer take
> any more rows.
> >Etc. Etc.
> >
> >If this is the case, either allocate more space or turn
> on autogrow for the
> >database in question.
> >
> >With regard to your closing comment, checkpoint is not
> equal to a commit.
> >Without knowing the period covered by the 18,000 records
> and something more
> >about your server it is not possible to offer details.
> However, I would
> >think that checkpointing was working just fine.
> >
> >Russell Fields
> >"mike" <mike@.ftsl.com> wrote in message
> >news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
> >> I have a service that writes a lot of records to many
> >> tables in SQL server. Recently, the service failed in
> >> writing record to any of the tables. But, the retrieving
> >> of the records is still OK. By examining the application
> >> log file, it seems that the SQL server started to
> >> deteriorate by not allowing insertion in one table at a
> >> time and eventually all tables are rejected for
> insertion.
> >> I ended up re-booting the system and everything went
> back
> >> to normal. After the reboot, I checked the SQL log file
> >> and noticed that about 18,000 records were rolled
> forward
> >> during SQL Server start-up.
> >>
> >> Does anybody have any idea as why the insertion would
> >> fail? And why the checkpoint was not executed even there
> >> are about 18,000 committed transactions?
> >>
> >
> >
> >.
> >|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database. no roll forward or roll back is
needed.
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?|||From: v-baiwei@.online.microsoft.com (Baisong Wei[MSFT])
Date: Fri, 30 Jan 2004 05:10:41 GMT
Subject: RE: Insertion failed
Newsgroups: microsoft.public.sqlserver.server
Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong,
Thank you for your reply and I understand the description
provided. Here is more background regarding to my problem:
1. We use ADO command object to perform single T-SQL
statement with a time-out value set to one minute.
2. On average, we perform approximately 36,960 T-SQL
statements per hour.
3. Database and transaction files are set to auto grew at
a rate of (10%)
4. Unfortunately, we don't know what the error message was
generated by SQL Server when the records failed to be
written.
5. All the non-SQL processes appeared to be running
normally at the time of the restart. This information was
gathered using Windows Task Manager.
6. No rolled-forward information was logged in SQL Server
log files in previous restarts.
7. Prior to the restart, new information was unable to
insert into the database, but old records can be retrieved.
8. The largest table in the database had 25,000,000
records at the time of the problem.
Question:
What could be happening on the server that would result in
1) failed to insert, and 2) rolled forward of 18000
records in restart.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
In you first post, you mentioned that 'By examining the application log
file, it seems that the SQL server started to deteriorate by not allowing
insertion in one table at a time and eventually all tables are rejected for
insertion.' and in your last post, you mentioned that 'we don't know what
the error message was generated by SQL Server when the records failed to be
written'. I wonder if you could provide the content of this part of the log
that you made this judgement, what made you think that the insertion is
rejected while no error message indicating any abnormal. Any blocking of
locks or any information from you application side? This information is
helpful for our analysis. Second, as I mentioned in my last post, when an
operation is executed, this operation will be write to log but not
necessary to write to .MDB file, when checkpoint come, they will be write
to the MDB file. I think that it is because the 18,000 records' insert have
been written into the log file, but not to the .MDB file, so, when SQL
Server restart, it will compare with the LSN of page and log to decide
roll-forward or roll-back, no difference between, in the SQL Server start
process, no this action is taken, as in your past restart. Besides the log
message, could you please provide the recovery mode of you database?
As the ADO time-out option, I suggest you to set to zero, which prepresent
no limit for time-out.
Looking for your reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong,
What I meant by 'By examining the application log
file,...' is checking the SQL server tables that the
application is writing to. All the written records
contain a time stamp; therefore I can tell when the
insertion started to fail on these tables.
'we don't know what the error message was generated by SQL
Server when the records failed' simply means that the
application did not log any error returned by the ADO
command object.
The recovery mode that the database uses is 'Full Recovery
Mode'
Mike|||Hi Mike,
Thank you for your update.
Yes, from the time stamps in your table that indicating when the insertion
was inserted, we could figure out that the inserting is stopped. However,
we could this can just provide the information about when the inserting
failure happened. From the error log and other informations collected,
could we figure out the cause of the problem. From the information now I
could have, I doubt that there were blocks and later deadlocks that cause
the insertion stopped and then cause all the insert failed. As I mentioned
in my previous reply, before the deadlock happen, some of the insert is
succeeded and this inserting is logged in the transactional log file, but
not in the data file. Before the checkpoint came, you re-booted the system,
and the SQL Server will also restart. SQL Server will compare the LSN of
data page and the log, then will decide which to roll forward and which to
rolled back. The operations already in the transactional log file will be
rolled forward, such as the 18,000 record in your case.
However, this is just a assumption based on my experience. To judge what
happend at that time, we need the corresponding error log for analysis.
Also, when this problem happened again, we could collect the useful
information by the following command. They are:
1) sp_lock: displaying the active locking information
2) sp_who, sp_who2: displaying the current users and processes information
You could also refer to this part in the SQL Server Books Online:
Troubleshooting Deadlocks
Hope this helps!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
douglas.reilly:
