August 10, 2011 at 3:39 am
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
nameminimummaximumconfig_valuerun_value
recovery interval (min) 03276700
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.
August 10, 2011 at 5:26 am
tempdb does not work the same way as a user database. #tables may be created in memory if resource is available.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 10, 2011 at 8:53 am
Guess: There's enough free space in the data file, but not in the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 10:42 pm
Oracle_91 (8/10/2011)
if i insert 10000000 records. i can see only ldf growing but not mdf.Can anybody explain the behaviour?
begin tran
insert into #tmp2
select 101,'Raider'
go 10000000
The code you are using has problem. There is a begin tran but no commit.
Remove begin tran or add this:
commit
go 10000000
Now you will see proportionate growth in mdf file as well.
August 11, 2011 at 1:40 am
I intentionally have'nt provided the commit stmt. I wanted to keep that as a open tran.
I was expecting the CKPT to do that for me. I was basically trying to understand the INSERTions in tempdb.
August 11, 2011 at 1:43 am
Guess: There's enough free space in the data file, but not in the log
Gail, am sorry i didnt understand the above statement.
Infact my data file is having more space than the log file.
Correct me if am wrong.
August 11, 2011 at 2:40 am
If the data file has enough free space in it to accommodate the new data, it won't have to grow. Since you say the mdf is larger than the ldf, that's probably what's happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 3:37 am
Oracle_91 (8/11/2011)
I intentionally have'nt provided the commit stmt. I wanted to keep that as a open tran.I was expecting the CKPT to do that for me. I was basically trying to understand the INSERTions in tempdb.
I think CKPT does not write dirty pages to data file for tempdb as there is no undo/redo during service startup.
August 11, 2011 at 6:04 am
Suresh B. (8/11/2011)
Oracle_91 (8/11/2011)
I intentionally have'nt provided the commit stmt. I wanted to keep that as a open tran.I was expecting the CKPT to do that for me. I was basically trying to understand the INSERTions in tempdb.
I think CKPT does not write dirty pages to data file for tempdb as there is no undo/redo during service startup.
Correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply