If I were you, I would drop the database and restore it again

select [name],cast(FILEPROPERTY(name,'SpaceUsed') as int)*8 as SpaceUsedKB

from sysfiles

FILEPROPERTY will return used space. Use the following code to get the total size (MB) and the used space (MB) for each file in the current db:

 DECLARE @bytesperpage dec(18,0)

 , @pagesperMB dec(18,3)

 -- get number...

It's correct. There will be exclusive locks on system tables e.g. objects in tempdb.

The following steps explain how you can create a new tempdb database. With this workaround, you are able to successfully start the SQL Server service.

1. If they exist, rename...

You can try to give index hint to the queried tables explicitly. In this scenario, your are not trusting sql optimizer.

Give no lock hint as well if the chance of dirty...

Agree with Ken. The best practice is to create a individual index for each column in this scenario. If you know that some exact combinations of columns will mostly be...

You can run the following code to trace dead lock. SQL server will log detail infos into error log including the command it's running in the involved two processes when...

Hans,

SQL Server 2000 logs trace information into event log from SQL 2000 PS2. Check KB307538 (http://support.microsoft.com/default.aspx?scid=kb;en-us;307538) maybe helpful.

To list all traces you have on the server, run

SELECT *...

You can check the execution plan.  Foucus on the step that takes most of the time and check indexes used or not. Add indexes if necessary to your tables.

If the IN...

Thanks for your input. It seems it's a MS bug. Just check @@Error should workaround. Thanks again.