ODBC Write Failures

I've got an odd occasonal problem when writing to our server. In two
different applications, one an access app, and the other VB6 we will
experience a condition where we are able to read data, but when we attempt
to save the data we get ODBC timeouts. When this occurs, I can "fix" the
problem by going into one of the tables of the database in question and
modifying any value.
Configuration is a dual Xeon with 2gb of memory running SQL Server 2000.
Both databases are on this system and are accessed via different DSNs. One
is a web app, and is accessed by com dlls, the other is an app that was
created in MS Access and then modified so that it gets its data from linked
tables.
Any insite, or even better a good description of troublshooting techniques
would be appreciated.
Sam RobinsonHi Sam,
According to your description, I am not sure what your problem is. I would
like you to provide the following information so that I can narrow down
this issue.
1. Please provide me with the detailed error message. To do this, you can
take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
additional information regarding how to obtain the screen shot, please
review [Item 1].
2. How did you read and write data with Access to SQL Server, via a linked
table or programming? Can you describe it in detailed?
3. I would like to know when the error occurred. It seems that the error
occurred when you updated the table on SQL Server. Did the error message
occur when you connected to SQL Server?
4. When the error occurs again, please perform the problematic SQL
statements using Query Analyzer. Does the same error message occur again?
It helps us to identify the problem related to the SQL Server including SQL
statements and your VB code.
5. Please provide the SQL Server error log (usually under C:\Program
Files\Microsoft SQL Server\MSSQL\LOG)
On the SQL Server side, there is a query wait option. This query wait
option helps us specify the time in seconds (from 0 through 2147483647)
that a query waits for resources before timing out. We can increase the
value of this option trying to avoid the timeout error message. For
additional information regarding this query wait option, please refer to
the following article on SQL Server Books Online.
Topic: "query wait Option"
Also, such issues occurring randomly tend to be complex and take up
extensive research time. I'd like to set your expectations that it may take
a while for us to help you narrow down the problem and we may eventually
redirect you to PSS to continue working with a dedicated Support
Professional. If this is critical, I'd recommend contacting PSS and opening
a support incident troubleshoot this further. If you need any help in this
regard, please let me know.
[Item 1]
Try to obtain the screen shot of the error message.
When the error occurred please perform the following steps to capture the
screen shot of the error message.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start 'All programs 'Accessories ' Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Thank you for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:2tLJaornDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> According to your description, I am not sure what your problem is. I would
> like you to provide the following information so that I can narrow down
> this issue.
> 1. Please provide me with the detailed error message. To do this, you can
> take a screenshot. Please send it to me at v-yshao@.microsoft.com. For
> additional information regarding how to obtain the screen shot, please
> review [Item 1].
>
I assume that you're referring to the error message that is being seen in
access. These are not terribly informative, and do not provide error
numbers. The errors being returned to the web app (which seems to have
somewhat better error handling) are timeouts.
> 2. How did you read and write data with Access to SQL Server, via a
linked
> table or programming? Can you describe it in detailed?
>
Access is using linked tables. These were created using the standard table
linking wizard from the file menu.
> 3. I would like to know when the error occurred. It seems that the error
> occurred when you updated the table on SQL Server. Did the error message
> occur when you connected to SQL Server?
>
No, it didn't. The applications are still able to read data when this
happens, which is what has me foxed. The connection seems to be almost
healthy.
> 4. When the error occurs again, please perform the problematic SQL
> statements using Query Analyzer. Does the same error message occur again?
> It helps us to identify the problem related to the SQL Server including
SQL
> statements and your VB code.
>
Unfortunately, the same SQL statements succeed more often than not.
> 5. Please provide the SQL Server error log (usually under C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG)
>
I can certainly do this, but what I'm seeing there is just that the backups
ran correctly.
> On the SQL Server side, there is a query wait option. This query wait
> option helps us specify the time in seconds (from 0 through 2147483647)
> that a query waits for resources before timing out. We can increase the
> value of this option trying to avoid the timeout error message. For
> additional information regarding this query wait option, please refer to
> the following article on SQL Server Books Online.
> Topic: "query wait Option"
> Also, such issues occurring randomly tend to be complex and take up
> extensive research time. I'd like to set your expectations that it may
take
> a while for us to help you narrow down the problem and we may eventually
> redirect you to PSS to continue working with a dedicated Support
> Professional. If this is critical, I'd recommend contacting PSS and
opening
> a support incident troubleshoot this further. If you need any help in this
> regard, please let me know.
>
I did find that the transaction logs had grown to truly monumental sizes (18
and 15 gb respectively) I cleaned that up last night, and am watching the
system. Does this seem like a likely culprit? I know that the log is broken
into virtual segments, but if the segment count gets very high can this
cause long lookup/write times that might impact external timers?
I can certainly appreciate your comments regarding intermittent problems and
how difficult they can be to resolve... or even to know if they're resolved.
I'm mostly looking for an approach to troubleshooting, and your posting has
given me several ideas.
Thanks for the help. As I see the problem (or don't as I'm hoping for!) I'll
post more information.
Sam Robinson|||Hi Sam,
Thanks for your feedback. Based on my experience, when server is busy, such
as performing many jobs on the background, the timeout error will occur.
According to your description with the monumental sizes of transaction
logs, it is possible that there are many jobs working on the background,
such as backup jobs, which impact the performance of the server. Because
the error occurs randomly, it is hard to us to find out the root cause of
this problem. When the error occurs again on your side, please feel free to
post in the newsgroup, I will glad to work with you continously. Of course,
I also hope it do not occur again.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:%239I3130nDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi Sam,
> Thanks for your feedback. Based on my experience, when server is busy,
such
> as performing many jobs on the background, the timeout error will occur.
> According to your description with the monumental sizes of transaction
> logs, it is possible that there are many jobs working on the background,
> such as backup jobs, which impact the performance of the server. Because
> the error occurs randomly, it is hard to us to find out the root cause of
> this problem. When the error occurs again on your side, please feel free
to
> post in the newsgroup, I will glad to work with you continously. Of
course,
> I also hope it do not occur again.
> Thanks for using MSDN newsgroup.
>
Michael, et al
Well, it's been a week now and I think it's safe to say that truncating and
setting up a backup maintenance plan has resolved the problem. My
speculation is that the problem came from the necessity to log the
transaction in the increadably huge transaction log. This would seem to
follow as we didn't get a timeout when reading, only when trying to commit a
record.
Wow. Given the size the logs had gotten, I'm amazed that the thing was
working as well as it was.
Thanks for your help!
Sam Robinson