SQL Server txlog - Fixed VLF Size – Conversation in The Heap™

Conversation started Nov 16, 2023 at 9:19.

Paul White

Nov 16, 2023 09:19

StartVlfStateTransition[10]: vifSize(MB) passed 64 spaceLeft in current VLF 16646656 bytes, number of drain log used 0
Skip zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf from page 8192 to 16384 (Offset 0x4000000 to 0x8000000) 64 mb
TryTransitionToFixedVlfState[10]: in mixed VLF state,hold up by ACTIVE_TRANSACTION at VLF 39 activeVlf 39, startFixedVLF 0
Skip zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf from page 16384 to 24576 (Offset 0x8000000 to 0xc000000) 64 mb
StartVlfStateTransition[10]: extra LogNoop [2000] start VLF [39], end VLF [40], fixed VLF offset 671…

My database is now using fixed size VLFs

Paul White

Nov 16, 2023 10:00

vlf_status = 8!

> ClearVLFDeprecateStatus at LSN 2b:7080:1 Start Log 2b:7086:77

Zikato

I'm not sure what I'm looking at

Paul White

So status 8 is a deprecated VLF

@Zikato There's a new procedure called sys.sp_start_fixed_vlf in 2022, which I was trying to get to work

Zikato

new new vlf status?
https://www.sqlskills.com/blogs/paul/new-vlf-status-value/

Paul White

Yes, even newer than 4

8 is the new new hotness

Also sys.sp_get_migration_vlf_state

Zikato

can't wait for 16

Paul White

Nov 16, 2023 10:04

I guess I'm all ready for migration to HyperScale

Zikato

Genuine questions: are VLFs that important? I remember reading this sqlskills.com/blogs/paul/… once a year, but I've never done any VLF maintenance.

Paul White

People like to obsess about them

It's an issue if you have an awful lot of them

Oh, growing the log didn't go well

Zeroing D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf from page 65536 to 89600 (Offset 0x20000000 to 0x2bc00000) 188 mb
Zeroing completed on D:\Databases\MSSQL16.SQL2022\MSSQL\DATA\LogTest.ldf (elapsed = 97 ms) 188 mb
Location:	 "sql\\ntdbms\\storeng\\dfs\\trans\\logmgr.cpp":24079
Expression:	 fcb->GetFixedVlfSizeHSMigration() > 0 && logFileSize == (fcb->GetFixedVlfSizeHSMigration() * 1024 * 1024)
SPID:		 85
Process ID:	 4708
Thread ID:	 28788

> Msg 3624, Level 20, State 1, Line 90
A system assertion check has failed. Check the SQL Server error log for details.
Typically, an assertion failure is caused by a software bug or data corruption.
To check for database corruption, consider running DBCC CHECKDB.
If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft.
An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

Zikato

I love sending mini dumps to Microsoft

Paul White

I should submit that one to Sean's site

Growing the log completed successfully when I specified a multiple of the fixed VLF size

Still not going to migrate to the cloud tho

Zikato

Shocking conclusion!

Paul White

Nov 16, 2023 10:14

It's not AI-enabled enough yet

Zikato

I've suspected that might be the main reason

Paul White

No way I'm running a cloud db alone, I need a copilot at least

Zikato

Some of the AI stuff is useful, but it's way over-hyped

Paul White

Shocking

Zikato

I'm just tired of people selling all the time. Can't anyone be just normal?

Paul White

Nov 16, 2023 10:17

Seems not

Zikato

ok, rant over

Paul White

It's funny that there's a wide conception that the majority of the people in the world are normal

Despite all the evidence

Zikato

Wishful thinking I guess. People are mostly optimistic

Paul White

Some people are, I don't know if they're a majority

Zikato

perhaps I was being overly optimistic

2 hours later…

ConcernedOfTunbridgeWells

Nov 16, 2023 12:32

@PaulWhite If you set them all to bomb, they do. Some of them make staircases instead.

1 hour later…

Hannah Vernon

Nov 16, 2023 13:56

@Zikato once when I was young I had a database take 20 hours to run recovery due to VLF mismanagement under default settings of 10% growth.

200,000 VLFs

Needless to say, I've learned that managing VLFs sensibly is a good thing. sqlserverscience.com/?s=vlf&submit=Search

Wordle 880 4/6*

🟨⬛⬛🟨⬛
⬛⬛⬛🟩🟩
⬛🟩🟩🟩🟩
🟩🟩🟩🟩🟩

@PeterVandivier hahahah guid one

That wordl is a couple of UFOs looking for someone to abduct for "tests"

Paul White

Nov 16, 2023 14:12

When did SQL Server start reusing VLFs in sequence number order, rather than physical order?

ConcernedOfTunbridgeWells

QOTD: It's called a data vault because it's very hard to break into and get the data back out.

Hannah Vernon

Nov 16, 2023 15:08

@PaulWhite that's a good question - I remember thinking to myself a while back, that doesn't look like it used to.

@ConcernedOfTunbridgeWells 🙂

Hannah Vernon

Nov 16, 2023 15:43

who thinks it's a bad idea to do a weekend-long conversion from on-premises to in-cloud over the December 30th and 31st weekend? I mean, aside from me.

Zikato

Gary Oldman - "Leon the Professional" "everyone" scene (HD)

►

Hannah Vernon

hahahahahah truth

> our bonus will be impacted if we don't migrate in 2023.

thought so

oh and for clarity, that's not my bonus. I don't get one.

ConcernedOfTunbridgeWells

Nov 16, 2023 16:48

@HannahVernon Urgent rates. I'm on holiday and have to come back from Indonesia to do this.

Is their bonus worth £2100/day?

Hannah Vernon

Nov 16, 2023 17:22

@ConcernedOfTunbridgeWells it would be double-time all day, so probably only about $1,000 per day since I live in the frozen north where rates are low because property is cheap. Also, management bonuses are way higher than anything the lowly people actually doing the work make.

for comparison purposes from a cost-of-living perspective, that rate is about $2,500 per day in NY City Freedom Dollars

Peter Vandivier

Nov 16, 2023 17:54

Patriot Credits

Erik Darling

you can use my address for that

2 hours later…

Hannah Vernon

Nov 16, 2023 19:47

@Zikato Oldman is incredible

10 hours later…

John K. N.

Nov 17, 2023 05:32

@HannahVernon You said "in-cloud"

Hannah Vernon

Nov 17, 2023 06:08

Wordle 881 4/6*

⬛⬛🟨⬛⬛
🟨⬛⬛🟨⬛
🟨🟩⬛⬛🟩
🟩🟩🟩🟩🟩

Starry starry night?

2 hours later…

John K. N.

Nov 17, 2023 08:06

Wordle 881 3/6*

⬛🟨⬛🟨🟨
🟩🟨🟨⬛⬛
🟩🟩🟩🟩🟩

1 hour later…

Zikato

Nov 17, 2023 09:35

sql.kiwi/2023/11/fixed-size-vlfs.html

Conversation ended Nov 17, 2023 at 9:35.

View all rooms

SQL Server txlog - Fixed VLF Size

Nov '2316

Nov '2317

General on- and off-site discussion for dba.stackexchange.com....

context of the conversation
join this room
about this room

Participants

Paul White 40%
Hannah Vernon 26%
Zikato 20%
ConcernedOfTunbridgeWells 5%
John K. N. 2%
Peter Vandivier 1%
Erik Darling 1%

all times are UTC

SQL Server txlog - Fixed VLF Size

The Heap™ – Consultancy ©®

Participants