log file growth - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

log file growth

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-10 : 05:37:21

Hi All,

Want to know the behaviour of INSERT statement if i execute the below code snippet.

if i insert 10000000 records. i can see only ldf growing but not mdf.
Can anybody explain the behaviour?

use db2
go
create table #tmp2
(id int,
name varchar(100)
)
go

begin tran
insert into #tmp2
select 101,'Raider'
go 10000000

Essentially, if am inserting 1000000 records in a temp table, then my mdf and ldf file size should match right?
Why there is a huge difference in size ? Atleast during the checkpoints the data has to be written back to disk, right?

use master
go
sp_configure 'show advanced options','1'
RECONFIGURE
USE master;
GO
EXEC sp_configure 'recovery interval'
GO
name minimum maximum config_value run_value
recovery interval (min) 0 32767 0 0

Adding to this, this is only txn am running in tempdb and for that matter, it is only query which is being run on the machine.

Also, i started my tempdb with 2mb mdf and 1 mb ldf

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 2)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 2048KB )
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1024KB )
GO

And auto growth is 10% for tempdb

Thanks in Advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-10 : 09:57:33

No reason to expect the mdf and ldf file sizes to match.

here's some suggested reading:

http://www.sqlskills.com/BLOGS/PAUL/post/What-does-checkpoint-do-for-tempdb.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Understanding-TempDB-table-variables-v-temp-tables-and-Improving-throughput-for-TempDB.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Comprehensive-tempdb-blog-post-series.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources