osql timeout

[second714@hotmail.com]

Hi,
I have several big tables with rows more than 25 mil rows
and to update/delete/insert data in these tables,
it can take minutes.
I use BULK Insert/DELETE/Update with osql.
While I run one of these updates,
if I try to select, it seems like both read and write get locked.
Shouldn't SQL resolve this kind of locking?

I left these to see if it gets resolved but both never returned.
So I need to kill these processes.
Does anyone have any scripts to find how long queries are running?

Also I need to make osql timeout and tried -t but it didn't work.
I used -t 1200 with DELETE in osql but it was running for more than 40
minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
it and it worked.
Shouldn't the query get killed after 10 minutes?
What is exactly -t option for ?

thanks,

[Erland Sommarskog]

Re: osql timeout

(second714@hotm ail.com) writes:[color=blue]
> I have several big tables with rows more than 25 mil rows
> and to update/delete/insert data in these tables,
> it can take minutes.
> I use BULK Insert/DELETE/Update with osql.
> While I run one of these updates,
> if I try to select, it seems like both read and write get locked.
> Shouldn't SQL resolve this kind of locking?[/color]

If they are deadlocked, that is waiting for each other, SQL Server should
indeed detect this situation, and select one of them as a deadlock
victim.

But it may be the case that the first process is blocked by something
else, and when you try to select, the SELECT statement is blocked by
the update.

I have a tool on my web site, aba_lockinfo, which is good for looking
at locking chains. You can get it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
[color=blue]
> Does anyone have any scripts to find how long queries are running?[/color]

You cannot really get the time how long a certain query has been
running, but you can see when a process last submitted a batch. The
latter is included in the information returned by aba_lockinfo.
[color=blue]
> Also I need to make osql timeout and tried -t but it didn't work.
> I used -t 1200 with DELETE in osql but it was running for more than 40
> minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
> it and it worked.
> Shouldn't the query get killed after 10 minutes?
> What is exactly -t option for ?[/color]

I didn't have the patience to try -t 1200, but I did try this:

E:\temp>osql -E -t 10
1> WAITFOR DELAY '00:00:20'
2> go
Timeout expired

So it does seem to work. I can't say why your DELETE did not timeout, but
it might be that it has to rollback the deletion that far, and rolling
back takes longer time than executing the command.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[second714@hotmail.com]

Re: osql timeout

Thanks for the info!
I'll try aba_lockinfo and test timeout more with select.

thanks again,