Transaction Log Question

Question

Transaction Log Question

Jerry Lew

SSC Rookie

Points: 48
More actions
February 28, 2006 at 12:37 pm

#93428

How come my transaction log doesn't grow when I execute the following code. I verified that the database is in full recovery mode.
After running the below code my transaction log was still under 1mb and dbcc sqlperf (logspace) showed that less then 50% of the log was used.
Create Table tbl (
Col1 int identity,
Col2 char(1000)
)
Go
set nocount on
Declare @iCount int
Declare @vchChar Char(1000)
select @vchChar = 'rrrasdfadkfjas;lksjfjhuieqiuewry304987ryhdrftgyuhji34er5t6y78ucvgbhnjmk,4xe5rc6ftvg7yb4ed5
rcf6tvg7yh8uj9ikd4e5rf6tg7yhu8hd5rft6g7yhu8jhde45rf6tgyh78ujhrf5de45rf6tg7hyu8jhy7gt6fr5de4rf6tg7yhgt6r5fde4r
ftg67yh8ujhy7gt6fr5asea;kfja;pqeioruncndiuenwrfcjei9u3fner934eww345678hgtf6r5derf6tg7yh8uj9i;lkjhgfdsaqwsedrf
tgyhujikode45rf6tgyh78u98reerfufh8yuf8848hedwsjfjhuieqiuewry304987ryhdrftgyuhji34er5t6y78ucvgtyguhiojgf6tg7yh8
uj9i4567890rctvyuhnijmkoumjynhgtrvsadfasdfasdfasdfsdafaregtwergtyujhgtrvffbujynhtgrfedw8i7juh6yg5t4rf8kij7uh6y
g5tfr4d3eij7uhy6gt5fr4d3e8kij7uh6yg5tfr438kij7uh6yg5tdsfr4d3e8ikj7uh6ygt5juhygtfredsmnbvcxlkjhgfhnjmk,4x'
Set @icount = 1
while @icount < 15000
BEGIN
Insert tbl (col2) Values (@vchChar)
Select @icount = @icount + 1
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Never looked but that is very interesting. I tried myself by creating a database R with a default size of 1 MB and grow by 10%. However you are right, when I checked the log itself with

dbcc log ('R',3)

I got 783 records (many of which are related to page and identity allocation and other pieces you don't realize most times occurr and are logged but you kind of understand they are there). The output is somewhat cryptic to review but I suspect there is something to understand the data didn't change and possibly log only the allocated page chains so it knows which pages to worry about instead of what data and slots.

So to test I then created a database X and altered the statement like so

Create Table tbl (

Col1 int identity,

Col2 char(1000)

)

Go

set nocount on

Declare @iCount int

Declare @vchChar Char(1000)

Set @icount = 1

while @icount < 15000

BEGIN

set @vchChar = cast(@iCount as char(1000))

Insert tbl (col2) Values (@vchChar)

Select @icount = @icount + 1

END

And when I check the log with

dbcc log ('R',3)

I got a result of 14905 and my log file jumped to 5.05 MB with with 2.85 MB in use.

Don't have a lot of time to spend but I make the assumption the log has a mechanism for dealing with constant data in a more effeciant manner to conserve resources.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

How very curious. I got the same results and also used the undocumented command "dbcc log (logtest, 1 )" to view the transaction log entries for a database named "logteest". For dbcc log explanation, see http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp

When running the original SQL, dbcc log only reports 310 entries in the transaction log.

But, when a "begin transaction" is added before the "WHILE" and a "commit' is after the WHILE END, dbcc log then reports 33,271 transaction log entries, which is what I would expect.

Transction log sizes:

Before running the SQL, sp_helpfile reports 504 Kb and "dbcc sqlperf(logspace)" reports 0.484375 Mb and used % is 40.322582

Without the commit, after running, the log file is 768 Kb and logspace reports 0.7421875 Mb with 62.171051 % used.

With a BEGIN TRANSACTIOM/COMMIT, the resulting transaction log is 26,816 KB and logspace reports 26.179688 Mb with 71.819977 % used.

I wonder what MS is doing ?

SQL = Scarcely Qualifies as a Language

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Again I am fairly sure there is a mechanism to recognize paterns in a batch of inserts, as stated I see the same low number as original when all the data is te same. When I use an incrementing number it has more but not as many records so obviously recognized a growing pattern. But as you state when you put each into a transaction by itself it has a lot more (the rough expected amount), but that i bcause transactions are autonymous and therefore SQL cannot use the pattern to decipher what happens next. Will have to look I bet I hav something on this here somewhere.