SQL server transaction timeout and stored procedures
in [Server Configuration]
|
Prev: New SQL Security problem coming next Tuesday
Next: Result Set from RESTORE FILELISTONLY into #table?
From: Mutley on 4 Jul 2008 12:18 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
From: Tibor Karaszi on 4 Jul 2008 12:24 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. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Mutley" <Mutley(a)discussions.microsoft.com> wrote in message news:D98FFCA7-5A8E-46B8-8F72-FA2D0F598ED4(a)microsoft.com... > 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
From: Mutley on 4 Jul 2008 12:35 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 "Tibor Karaszi" wrote: > 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. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Mutley" <Mutley(a)discussions.microsoft.com> wrote in message > news:D98FFCA7-5A8E-46B8-8F72-FA2D0F598ED4(a)microsoft.com... > > 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 >
From: Tibor Karaszi on 4 Jul 2008 12:48
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... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Mutley" <Mutley(a)discussions.microsoft.com> wrote in message news:423A4258-62A5-4A7C-A536-9136D92F5C09(a)microsoft.com... > 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 > > "Tibor Karaszi" wrote: > >> 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. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Mutley" <Mutley(a)discussions.microsoft.com> wrote in message >> news:D98FFCA7-5A8E-46B8-8F72-FA2D0F598ED4(a)microsoft.com... >> > 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 >> |