SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Explicit Transactional Scope
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 07/10/2013 :  13:07:41  Show Profile  Reply with Quote
[Code]

Declare @i int
Begin Transaction
WHILE (@i <=10)
Begin
	Insert into dbo.Employee1(EmpNo,EmpName,Period)
	select @i,'empname' +@i,1
	Set @i=@i + 1	
END	
Commit Transaction


Normally sql server by default it will be implicit Transaction.I've seen in some of the stored procedure in our
company they have given explicitly..can i know what would be the reason behind this. I guess it would be realated to log file.

Edited by - sent_sara on 07/10/2013 13:09:45

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  13:14:27  Show Profile  Reply with Quote
the reason is they want to make all the activities inside it atomic.
ie in above case since the while loop is inside transaction, only if entire loop suceeds, the data will be commited. Any intermediate error in any of iteration will cause transaction to fail.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  13:38:34  Show Profile  Reply with Quote
quote:
Originally posted by sent_sara [Code] Declare @i int Begin Transaction WHILE (@i <=10) Begin Insert into dbo.Employee1(EmpNo,EmpName,Period) select @i,'empname' +@i,1 Set @i=@i + 1 END Commit Transaction


Normally sql server by default it will be implicit Transaction.I've seen in some of the stored procedure in our
company they have given explicitly..can i know what would be the reason behind this. I guess it would be realated to log file.


I know you are showing the code as an example, but the way it is written, it won't insert anything into the table because @i has not bee initialized.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  13:49:32  Show Profile  Reply with Quote
One other thing to keep in mind is that if you run into any batch terminating errors, the behavior may not be as you expect. For example, how many rows if any would be inserted into the table in the query below? And, it will leave around an open transaction.
CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
	Insert into AX (id) 
		select @i
	IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
	Set @i=@i + 1	
END	
Commit TRANSACTION

DROP TABLE AX;
If you want a set of statements to be executed atomically, use the pattern on this MSDN page: http://msdn.microsoft.com/en-us/library/ms175976.aspx
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 07/10/2013 :  15:15:24  Show Profile  Reply with Quote
Thanks James and visakh16

I got it.

how the log file will behave.
My thoughts on this is by giving explicit transaction ,it will enter the log file at final stage (ie) during commit trans

if explicit transaction not given then for each and every insert within the loop log entry will be there.

Correct me if I'm wrong..


quote:
Originally posted by James K

One other thing to keep in mind is that if you run into any batch terminating errors, the behavior may not be as you expect. For example, how many rows if any would be inserted into the table in the query below? And, it will leave around an open transaction.
CREATE TABLE AX(id INT NOT NULL);

Declare @i INT = 1;
Begin Transaction
WHILE (@i <=10)
BEGIN
	Insert into AX (id) 
		select @i
	IF (@i >= 5) INSERT INTO AX VALUES (@i,'abcd');
	Set @i=@i + 1	
END	
Commit TRANSACTION

DROP TABLE AX;
If you want a set of statements to be executed atomically, use the pattern on this MSDN page: http://msdn.microsoft.com/en-us/library/ms175976.aspx


Edited by - sent_sara on 07/10/2013 15:16:54
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  15:41:13  Show Profile  Reply with Quote
The internals of the log file are really not of much use from a logical perspective. Nonetheless: the log file records the begin transaction, each of the updates and the commit transaction. For implicit transactions, there will be a set of begin tran, update and commit for each update.
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 07/10/2013 :  15:53:15  Show Profile  Reply with Quote
the reason why i raised this question is: during the execution one of our stored procedure we have got "Insufficient diskspace in tempdb" this is due to log file grow.

to avoid the log file growth,im in bit confusion to be with implicit
or explicit transaction.

so from your answer what i understood is:
Implicit transaction will have begin Trans and commit tran for each updates
Explicit transaction will have group of updates within the scope

so now my question is whether if begin transaction is given at beginning and do some several updates,nested stored procedures etc and finally commited (explicit transaction).. do the log file is grown on this?

quote:
Originally posted by James K

The internals of the log file are really not of much use from a logical perspective. Nonetheless: the log file records the begin transaction, each of the updates and the commit transaction. For implicit transactions, there will be a set of begin tran, update and commit for each update.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/10/2013 :  16:15:42  Show Profile  Reply with Quote
If you are running out of diskspace on tempdb, before looking at specific queries and trying to optimize them to use less disk space, you should investigate why it is growing. There is a lot of useful information here that will help you with that. http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

There are some settings in SQL Server that can cause heavier usage of tempdb - for example, version store (which comes into play if you have enabled read committed snap shot isolation). Long running transactions (rather than many smaller transactions) cause version store to grow.

Also, tempdb is shared by all the databases on the server instance, so other databases and queries also will play into it.

In any case, take a look at the guidance on that page and see if that helps you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000