Restore stalls around 97% done with PREEMPTIVE_OS_WRITEFILEGATHER

Question

Post reply

Restore stalls around 97% done with PREEMPTIVE_OS_WRITEFILEGATHER

webrunner

SSC-Dedicated

Points: 31862
More actions
June 11, 2022 at 3:24 pm
Go to Answer
#4048001

Hello experts,
I'm having trouble getting around this issue. I am trying to restore a database and every time (via T-SQL or GUI), it stalls out at around 97%. I check sp_WhoIsActive and keep seeing this wait type:
PREEMPTIVE_OS_WRITEFILEGATHER
I tried the solution here but to no avail:
https://sqlserverscribbles.com/2013/11/17/cannot-bring-the-windows-server-failover-clustering-wsfc-resource-id-online-error-code-5018/
Does anyone have an idea of how to fix this? I did a restore yesterday without issue. It was a smaller db, but this current one, at around 30 GB, doesn't seem like it should be having this trouble.
Thanks for any help.
-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Jeff Moden

SSC Guru

Points: 1004324
More actions
June 11, 2022 at 6:44 pm
Answer
#4048081

First thing I'd check for is the size of the log file being restored and then I'd check the original database to see the size and how many VLFs are in the file. Even if "Instant File Initialization" is enabled, the log file ends up going through a formatting process to create the VLFs.. If they number of VLFs had gotten out of hand because of poor growth settings, it can take a huge amount of time to restore the database.
Please see the following links for more reasons why proper growth patterns and VLFs are important.
http://3.209.169.194/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

webrunner SSC-Dedicated Points: 31862 More actions · Answer 1

Thanks so much, Jeff, I will review those articles.

Thanks again!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

webrunner wrote:

Thanks so much, Jeff, I will review those articles.
Thanks again!
-- webrunner

My disclaimer is that I don't know if that's the actual problem. It's just one that people seem to forget about and it doesn't take much time to explore. Just go to the original database and type DBCC LogInfo WITH TABLERESULTS and see how many VLFs you have and what their sizes are. As of 2016 sp2 (IIRC), there's also a system DMV you can use...

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webrunner SSC-Dedicated Points: 31862 More actions · Answer 3

Gotcha, Jeff, thanks for the disclaimer. At the very least I can use the info you provided to rule in or rule out the issue. Much appreciated.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Sounds like the log file issue might be spot on according to the following link...

https://www.sqlskills.com/help/waits/preemptive_os_writefilegather/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 5

And make sure it is the log file. That is, make sure IFI is enabled so that SQL doesn't have to initialize all the data files too.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".