It's a little weird for me. I have a database (AdventureWorks2014; SQL Server 2014) in full recovery model.
Executing this script 3 times:
declare @i int = 0
declare @date datetime
while @i <= 1000
begin
set @date = getdate()
begin tran
update Production.TransactionHistory
set TransactionDate = getdate(),
ActualCost = @i + TransactionID
where TransactionID % 3 = 0
commit tran
set @i = @i + 1
end
returns these results for the log file space usage:
select * from sys.dm_db_log_space_usage
12 4280279040 2110091264 49,29799 2101624832
12 4280279040 2120466432 49,54038 2101067776
12 4280279040 1261416448 29,47042 1238355968
I suspect each execution of script should increase log file usage / log file size in full recovery model... Why the first time I have 49% usage and the the third only 29% heaving the same total file size?
Of course there is no log backup...
September 23, 2020 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Have you taken a full backup of the database since it was placed in the Full Recovery Model? Until the first full backup completes, the transaction log acts as if the database were in the Simple Recovery Model. Transaction log backups have no value until there is at least one Full backup because those log backups can't be used to recover anything.
Take a Full backup of the database and try your test again.
Eddie Wuerch
MCM: SQL
September 24, 2020 at 8:57 pm
that was it, thx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply