Hi,

Thank for your advise.
Please kindly explain why the tempdb is giving the problem ?
Actually, my tempdb is already set to 50M, simple mode, no limit
on the expansion.


>V,
>
>The problem is in tempdb. Make sure that the recovery model is set to Simple. Check if there is a limit on the file expansion for tempdb and set it to unlimited. You can also try DBCC SHRINKDATABASE, DBCC SHRINKFILE on tempdb. Or if all else fails, stop SQL Server, delete the tempdb files, and restart SQL Server (it will create a new tempdb).
>
>>Hi,
>>
>>Recently, our client database give problem of query that lock up
>>tables, causing our web-based application to halt. We found that
>>simple query like (this is a make up query with similar code like
>>my actually query as I can't use my actual query here, my actual
>>query have 4-5 joins)
>>
>> select a.name, b.add, c.tel
>> from name a inner join address b
>> on a.person_id = b.person_id and a.person_id = @i_person_id
>> and b.version = (select max(version) from address where person_id =
>> a.person_id)
>> inner join tel c on c.person_id = b.person_id
>> and c.version = (select max(version) from tel where person_id = b.person_id)
>>
>>The above query work when our SQL server 2000 SP3 database size is around
>>1.5G. After the database size increase to around 2.4G, the above query
>>might not work(lock table) for different parameter person_id that
>>we pass it. Some of them work and some don't even though they
>>are the same query.
>>
>>After some debugging and tunning, When we change the query to query the max(version) separately, our query work without any problem.
>>
>>I am wondering why the query work when my DB size is small and
>>all the suddenly cannot work or halt my DB when the size is
>>big.
>>
>>Anyone with similar issue or problem, pls kindly advise.
>>Thank you