SQL server transaction timeout and stored procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mutley - 04 Jul 2008 17:18 GMT

Hi,
is there a default transactions timeout when running stored procedures?
Basically I need to run stored procedures that could involve 100,000s of
inserts\updates and I want to be able to let the procedure run until these
are completed. Is this feasible or are there some restrictions. Also is there
anything else that could prevent the stored procedure continuing to run as
too many operations are in the one transaction?

Thanks in advance for any help
JohnN

Reply to this Message

Tibor Karaszi - 04 Jul 2008 17:24 GMT

The default timeout at the server level is indefinite. You can configure a server level timeout for
lock waits using SET LOCK_TIMEOUT.

The client API, however, can impose a timeout, sending an "attention" signal to SQL Server to cancel
the running batch (procedure). This is out of control for SQL Server so you need to specify the
desired behavior at the client side. Some API's has 30 seconds as default, for instance.

Now, you want to think hard whether you want to bite it all in one chunk (transaction) or not.
Several, factors applies here, like duration, duration for locks, restartability etc. All in one
transaction is easy from dev perspective, but might not be as practical from operational standpoint.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
> is there a default transactions timeout when running stored procedures?
[quoted text clipped - 6 lines]
> Thanks in advance for any help
> JohnN

Reply to this Message

Mutley - 04 Jul 2008 17:35 GMT

Thanks Tibor,
so the default timeout is indefinite. Good. The second
part of your reply talks about whether or not I should be doing it all in one
transaction. if I did decide to go with one transaction is there something
that would prevent it all being done in one go? I heard about a transaction
log before that possibly could prevent too much being done in the one
transaction. Is that correct?
Thanks again for your previous reply
Regards
JohnN

> The default timeout at the server level is indefinite. You can configure a server level timeout for
> lock waits using SET LOCK_TIMEOUT.
[quoted text clipped - 17 lines]
> > Thanks in advance for any help
> > JohnN

Reply to this Message

Tibor Karaszi - 04 Jul 2008 17:48 GMT

Yes, the transaction log can possibly be an operational obstacle. It cannot be emptied past the
oldest open transaction. Talk to your operational DBAs about this, and indicate how much you aim to
do in one transaction. Like thousand, millions or tens of million of rows in one transaction.

Another is that exclusive locks are held until end of transaction. So doing a lot can result in
blocking...

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks Tibor,
> so the default timeout is indefinite. Good. The second
[quoted text clipped - 31 lines]
>> > Thanks in advance for any help
>> > JohnN

Reply to this Message