Time Outs saving to SQL 2000 server

Time Outs saving to SQL 2000 server - Where do I start?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jordan - 10 Jul 2008 15:36 GMT

We are running SQL 2000 for the back end. We have created a DB on the
server called MFGData. In MFGData there are a series of tables where we
dump our test results from our products. The sofware we use to test was
developed with National Instruments Labview which connects to MFGData via
ODBC. Here is a typical example of the tables.

EventID nvarchar 20
TestType nvarchar 30
SequenceID nvarchar 30
Serial nvarchar 20
Application nvarchar 20
Axis nvarchar 1
TestDate sm/dt/tm 4
Pass_Fail tinyint 1
UUTParams image 16
TestParams image 16
Data image 16
Username nvarchar 50
Station nvarchar 50
AppLink char 38
(Allow Null OK for All and no default values for any)

These test stations run several hundred tests per day per machine where each
can add 5-20 records per test so there are thousands of writes to the DB
daily. About once per week/month totally randomly none of the 20 or so test
stations are able to save data to any of the tables in MFGData. Not just
one of the systems accessing one of the tables. It is all of the systems
written in Labview accessing any of the tables in this DB which is strange.

I have a couple of other databases on this server where I use MSAccess as a
front end and connect via ODBC as well and they are able to still write to
their DB tables without a problem.

I have tried restarting the clients - sometimes all and that did not help
ever. Restarting the server did not help as well. I have the server setup
to run integrity checks nightly and optimizations as well and running these
and even Chkdsk on the drives still does not help. After restarts, chkdsk
and other checks the clients still will not write - none of them.

What does appear to help sometimes is for me to open the SQL Manager and
open a table and hand enter a record and delete it from within the SQL
Manager query window. If for some reason I am not available and cannot do
it when it is happening in an hour or two everything just starts working
again.

I have checked for scheduled tasks on the server and in the SQL agent and
none run during the day. I just don't even know where to begin on this one.

Reply to this Message

thejamie - 10 Jul 2008 16:02 GMT

It sounds as if you are getting collisions on your database that result in a
lockout. Are you receiving any helpdesk indications that there are timeouts
or lockouts?

http://technet2.microsoft.com/windowsserver/en/library/f3abc878-3eab-4eaf-9bff-9
f0d058d4fc31033.mspx?mfr=true

I don't know for sure why it would alleviate the lockout when you open the
database and add and delete a record by hand ...

There are some diagnostics you can run with the dmv is you are on SQL 2005
if you have access to 2005 and it can look at the 2000 server.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

For 2000 there is a proc called sp_now which you can use to look at
split-second activity on your server.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104259

Hope it helps.

Signature

Regards,
Jamie

> We are running SQL 2000 for the back end. We have created a DB on the
> server called MFGData. In MFGData there are a series of tables where we
[quoted text clipped - 43 lines]
> I have checked for scheduled tasks on the server and in the SQL agent and
> none run during the day. I just don't even know where to begin on this one.

Reply to this Message

Jordan - 10 Jul 2008 16:17 GMT

I should have clarified the lock out. The users accounts on the domain and
the server are fine. By locked out I mean that the tables are preventing
any user (even myself) to write from any client. The user gets a timeout
error every time they try to write while the problem is occuring.

> It sounds as if you are getting collisions on your database that result in
> a
[quoted text clipped - 76 lines]
>> none run during the day. I just don't even know where to begin on this
>> one.

Reply to this Message

thejamie - 10 Jul 2008 18:15 GMT

http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx

You will need to figure out the specific transaction is causing the lockout.
It could easily be one that is used consistently over and over but it
reaches a timeout situation because conditions cause the record to be updated
when another process has the lock on the record. You could run the profiler
and have it check for lockouts. It may be that the server running the
database is lacking in RAM. Adding RAM will help. Upgrading to a new
server will help.

Here is a how to on creating a trace log:
http://reddevnews.com/features/article.aspx?editorialsid=210

Signature

Regards,
Jamie

> I should have clarified the lock out. The users accounts on the domain and
> the server are fine. By locked out I mean that the tables are preventing
[quoted text clipped - 81 lines]
> >> none run during the day. I just don't even know where to begin on this
> >> one.

Reply to this Message

thejamie - 10 Jul 2008 20:04 GMT

There is something else I just know remembered and that is that if you
suspect that a given transaction is responsible, and if for some reason that
transaction is using a begin tran commit rollback, you may want to change the
code around to use an uncommitted read instead:

For example
da.SelectCommand.Transaction =
objConnect.BeginTransaction(IsolationLevel.ReadUncommitted)
da.Fill(dtSearchResults)
da.SelectCommand.Transaction.Commit()

Signature

Regards,
Jamie

Reply to this Message