Transaction log written for select /into as well

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transaction log written for select /into as well

Author

Topic

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-11-26 : 16:51:37

Version 2005
SP2

1. Changed the database recovery option to simple
2. Ran "select * into saad2 from a_table"
3. Ran dbcc log (test)
4. Here I saw every insert getting logged (not what I was expecting)

Then truncate the log (I did a backup). Then run dbcc log (test) again to make sure the log is empty. It should return a few rows.

Then I did the test again as follows:
1. Changed the database recovery option to "bulk"
2. Ran same select into
3. Ran dbcc log (test)
4. Here I saw every insert getting logged (not what I was expecting)

So whats the deal? I was assuming that select/into will not write the log entrues

--
Saad Ahmad
saad.ahmad@gmail.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-11-26 : 17:47:45

Obviously your assumptions are wrong.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-11-27 : 15:32:01

Can you elaborate? How can I reduce the transaction log activity. As per documentation "select into" is not something where transaction log should have only one entry.

What does it mean when documentation says "minimum logging for select into". The above test does same logging.

--
Saad Ahmad
saad.ahmad@gmail.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-28 : 02:08:37

Try switch recovery model to bulk-logged.

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-11-29 : 14:11:21

So "bulk logged" will write less than "simple" when using "select into"?

--
Saad Ahmad
saad.ahmad@gmail.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-30 : 02:14:06

Yes.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-30 : 14:05:24

I am interested in this post as well. I tested the same "select into" with 1mil records using the 3 different recovery models. Simple recovery had the least amount of writes to the tran log examining with dbcc log and compareable end log size. Aren't bulk operations minimally logged in simple recovery mode as well?


USE Master

GO

CREATE DATABASE LogTest

go

alter database LogTest 
	set recovery full


GO

Use LogTest

select name,(size / 128.0) 
from logtest.sys.sysfiles


dbcc log('LogTest')

GO

SELECT TOP(1000000) NEWID() as Col1
INTO InsertEx 
FROM DevToolBox.dbo.NumberSequence

dbcc log('LogTest')


select name,(size / 128.0) 
from logtest.sys.sysfiles



USE Master

GO

drop DATABASE LogTest

go

CREATE DATABASE LogTest

go

alter database LogTest 
	set recovery bulk_logged

GO

Use LogTest

select name,(size / 128.0) 
from logtest.sys.sysfiles


dbcc log('LogTest')

GO

SELECT TOP(1000000) NEWID() as Col1
INTO InsertEx 
FROM DevToolBox.dbo.NumberSequence

dbcc log('LogTest')


select name,(size / 128.0) 
from logtest.sys.sysfiles

USE Master

GO

drop DATABASE LogTest

go

CREATE DATABASE LogTest

go

alter database LogTest 
	set recovery SIMPLE

GO

Use LogTest

select name,(size / 128.0) 
from logtest.sys.sysfiles


dbcc log('LogTest')

GO

SELECT TOP(1000000) NEWID() as Col1
INTO InsertEx 
FROM DevToolBox.dbo.NumberSequence

dbcc log('LogTest')


select name,(size / 128.0) 
from logtest.sys.sysfiles

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-02 : 00:34:56

Sql will truncate log in checkpoint if the db is in simple recovery model.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-03 : 13:09:09

Ok, does that mean simple recovery handles minimally logged operations differently than bulk-logged recovery mode? That doesn't seem to make sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources