General discussion

Locked

LOCK TIME OUT ON SQL 6.5

By PRS ·
I am running on NT4 platform with service pack4, SQL6.5 with service pack 3.
Most of the time when more users on line & making a quary from tables which contains more 1.6 million records they end up saying lock time out. Please specify is there areany remedy for such problems. I have allocated 1GB Memory for sql where my server is having 1GB + 128MB RAM.

Thanking you

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

LOCK TIME OUT ON SQL 6.5

by quahsays In reply to LOCK TIME OUT ON SQL 6.5

1. not sure if SP5a would resolve the issue
2. maybe the query they perform is huge, and server not strong enough to handle the requests?
3. maybe you could review the kind of query they perform and fine tune the query?

Collapse -

LOCK TIME OUT ON SQL 6.5

by PRS In reply to LOCK TIME OUT ON SQL 6.5

The question was auto-closed by TechRepublic

Collapse -

LOCK TIME OUT ON SQL 6.5

by chrandrob In reply to LOCK TIME OUT ON SQL 6.5

Fine-tuning your queries is always a good idea, but that's probably not the source of your problem. When a query is executed, it locks the table while it executes. Any queries executed are held in the queue until they can be executed.

The longer it takes for a query to run and the more queries that get run, they longer they wait until they can start executing. This results in timeouts. There is a fix for this.

The locks on the tables are not necessary for select clauses. Every select clause should specify the nolock hint. Then the server will be able to process many queries at once instead of one at a time.

Here's an example of what I mean:

Select Field1, Field2
From myTable with(nolock)

If you alias the table name, the hint goes after the alias. Like this:

Select Field1, Field2
From myTable AS mT with(nolock)

This will help a great deal.

Collapse -

LOCK TIME OUT ON SQL 6.5

by PRS In reply to LOCK TIME OUT ON SQL 6.5

The question was auto-closed by TechRepublic

Collapse -

LOCK TIME OUT ON SQL 6.5

by donq In reply to LOCK TIME OUT ON SQL 6.5

'CONSTANTS FOR A MsgBox.
Global Const MB_OK = 0
Global Const MB_CRITICALSTOP = 16
Global Const MB_BTN1DEFAULT = 0

'RETRY NETWORK ACCESS WHEN WHEN LOCKING CONFLICTS BLOCK ENTRY.
Global Const MAX RETRIES = 3

You can then build an error trap to quit when the server is REALLY busy or increase "3"???

Good Luck

Collapse -

LOCK TIME OUT ON SQL 6.5

by PRS In reply to LOCK TIME OUT ON SQL 6.5

The question was auto-closed by TechRepublic

Collapse -

LOCK TIME OUT ON SQL 6.5

by PRS In reply to LOCK TIME OUT ON SQL 6.5

This question was auto closed due to inactivity

Back to Desktop Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums