By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

osql timeout

P: n/a
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,

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(se*******@hotmail.com) writes:
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?
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.
Does anyone have any scripts to find how long queries are running?
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.
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 ?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
Thanks for the info!
I'll try aba_lockinfo and test timeout more with select.

thanks again,

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.