SQL Server 2000 Troubleshooting


Even though SQL Server 2000 is no longer supported by Microsoft, you might still have to support an instance of this version in production somewhere in your travels. You can’t use as many tools in your arsenal to troubleshoot these older versions as you might use today, but there are some basic tools you can use to provide a little help.

Who is Connected

EXEC sp_who


SELECT * FROM master.dbo.sysprocesses WHERE dbid = DB_ID('databasename')

Running Queries

declare @s table(spid smallint,login_time datetime,last_batch datetime,[status] nchar(30),loginame nchar(128),[text] text)

declare @sql_handle binary(20),@spid smallint;
declare c1 cursor for select sql_handle,spid from master..sysprocesses where spid >50;
open c1;
fetch next from c1 into @sql_handle,@spid; 
while (@@FETCH_STATUS =0) 
insert into @s
select spid,login_time,last_batch,[status],loginame,a.text
from ::fn_get_sql(@sql_handle) a, master..sysprocesses b
where b.spid = @spid
fetch next from c1 into @sql_handle,@spid
close c1
deallocate c1;

select * from @s

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s