December 2, 2015 at 9:08 am
All, I am wondering if someone can help me understand because I feel just a little lost. I am actually doing some experiment to better understand how SQL Server works.
Test:
1 database (TestDB)
Initial Log file size is 5 MB, auto-growth is set to 5MB
I inserted 100000 rows into a table, ran dbcc loginfo and it shows 191 VLFs.
I inserted 100000 rows again into the same table and when I ran ran dbcc loginfo, it still shows 191 VLFs and there are tons of reusable VLFs (Status 0). I know it's a silly question but there is a little confusion in my mind and I just want to make sure that I understand it properly. Anyways, for some reason I thought there would be more VLFs since I didn't take the tlog backup.
December 2, 2015 at 9:13 am
Let me guess
You created the database, set the recovery model and then started to test?
Until a database has had a full backup, it runs in pseudo-simple recovery because there's nothing to base log backups on. If you didn't take a full backup before starting the inserts, then the database is still behaving as if it's in simple recovery.
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
December 2, 2015 at 9:30 am
GilaMonster (12/2/2015)
Let me guessYou created the database, set the recovery model and then started to test?
Until a database has had a full backup, it runs in pseudo-simple recovery because there's nothing to base log backups on. If you didn't take a full backup before starting the inserts, then the database is still behaving as if it's in simple recovery.
I couldn't have figured that out. Thank you so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply