First full backup clears tlog - 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 2000 Forums

SQL Server Administration (2000)

First full backup clears tlog

Author

Topic

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-18 : 12:34:04

Why does the first full backup of a db clears out the virtual logs?

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-18 : 22:15:44

Full backup contains committed transactions but will not truncate them from log file, that's log backup's job.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-19 : 12:28:17

True.

But I tried this before.
1. create a new db
2. create table, populate it
3. check log space using dbcc sqlperf(logspace), take note of the log space used..
4. do a full backup of the db
5. again, check log space using dbcc sqlperf(logspace), then the log space used get trimmed down?

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 15:21:53

Is the db in simple recovery mode? If so, you can't backup log and sql will truncate committed transactions at checkpoint.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-19 : 16:18:20

quote:
Originally posted by rmiao

Is the db in simple recovery mode? If so, you can't backup log and sql will truncate committed transactions at checkpoint.

It is in full mode.

What confused me was that why the very first full backup truncates the log...
the succeeding full backups won't truncate the logs tho. (w/c should be the case).
You may wanna try that steps scenario, don't know if service pack has something to do w/ it.

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 20:48:10

Which sp does the server have?

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-28 : 15:41:45

sql 2000 ent, this has sp4

Donn Policarpio

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-28 : 15:48:33

Below is a sample test and the results in commented lines.

-- use master
-- go
-- drop database test
-- go

create database test
go

use test
go
create table t1(
f1 varchar(100),
f2 varchar(100),
f3 varchar(100),
f4 varchar(100),
f5 varchar(100),
f6 varchar(100),
f7 varchar(100),
f8 varchar(100)
)

insert t1
select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'

insert t1 select * from t1 --run 15x

dbcc sqlperf(logspace)
-- Db Name Log Size (mb) Log Space Used (%) Status
-- test 19.617188 82.494522 0

-- FIRST FULL BACKUP TRIMS THE LOG SPACE USED...
backup database test to disk='f:\test.bak' with init
dbcc sqlperf(logspace)
-- test 19.617188 22.476105 0

-- POPULATE AGAIN...
insert t1 select * from t1

-- LOG SPACE FILLED...
dbcc sqlperf(logspace)
-- test 38.367188 81.486717 0

-- NEXT FULL BACKUP DIDN'T TRIM THE LOG (NORMAL)...
backup database test to disk='f:\test.bak' with init
dbcc sqlperf(logspace)
-- test 38.367188 81.49308 0

-- LOG BACKUP TRIMS THE LOG AS USUAL...
backup log test to disk='f:\test.trn' with init
dbcc sqlperf(logspace)
-- test 38.367188 17.269905 0

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-28 : 23:48:12

Didn't see reducing log file size from your results.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-30 : 14:07:32

dbcc sqlperf(logspace)
-- Db Name Log Size (mb) Log Space Used (%) Status
-- test 19.617188 82.494522 0

-- FIRST FULL BACKUP TRIMS THE LOG SPACE USED...
backup database test to disk='f:\test.bak' with init
dbcc sqlperf(logspace)
-- test 19.617188 22.476105 0

I was pointing out the log space used was down to 22.47 after the first full backup.
Thanks.

Donn Policarpio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources