SQL DB Restore showing 0 %

Question

SQL DB Restore showing 0 %

pols

SSC Eights!

Points: 935
More actions
June 16, 2022 at 5:05 pm
Go to Answer
#4050554

Hi,
Initiated 2.4 TB restoration freshly in the sql instance using GUI.. From 2 hours it is showing as 0 %.
In SQL_Data files drive occupied with 2.4 tb. But when i open the drive inside everything empty.
I feel the data allocation is taking time. But I am confused whether restoration is in progress or stuck in between?
Please suggest how to fasten the db restoration.
For info total 8 data files in the db. Two Split backups each 450 gb of each backup file.
please help.
Thanks,
Pols.
ScottPletcher

SSC Guru

Points: 101126
More actions
June 16, 2022 at 6:46 pm
Answer
#4050594

Disk speeds on systems vary greatly. But you can always cancel the restore after (almost) 4 more hours. Cancelling a restore shouldn't take long at all, so you can wait until you have to cancel it. I would let it run for now.
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".

Viewing 10 posts - 1 through 9 (of 9 total)

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

ScottPletcher SSC Guru Points: 101126 More actions · Answer 1

How large is the log file? SQL must restore the log file too, and, worse, it must preformat the entire log file before it restores to it. In essence, the log file must be written twice for any restore.

If somehow you do not have IFI enabled, then the data files would also have to be preformatted first.

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".

pols SSC Eights! Points: 935 More actions · Answer 2

Log file size is 238 gb.

So shall i stop the restoration?

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

No. 236GB is not bad for a 2.4TB db. Once the log has been preformatted, you'll be back to the regular restore of the data.

The only way to make the restore faster would be to shrink the log before backing up the original db. This will make the log file that needs to be restored very small. You would immediately grow the original log back to its original size. And, after restoring the new db, immediately grow that log file to a more reasonable size as well.

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".

pols SSC Eights! Points: 935 More actions · Answer 4

Now i can not shrink the log of the source db and take backup again.

I am restoring the backup which i have taken 1 week before. Now i do not have access for source server as well.

Small correction. I can see log file is occupied and four data files also occupied. (By mistaken i mentioned 8 data files-pls neglect).

Disks are filled and space is occupied. In activity monitor as well i can see restore is in suspended mode.

SELECT

session_id as SPID, command, a.text AS Query, start_time, percent_complete,

dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

This is the output restoration command i got below with 0 %. Which means still it is running right? It is more than 3 hours now. 🙁

RESTORE DATABASE [A8] FROM DISK = N'J:\A8_9_06_2022_1.BAK', DISK = N'K:\A8_9_09_2022_2.BAK' WITH FILE = 1, MOVE N'A8DATA1' TO N'F:\Data\A8DATA1.mdf', MOVE N'A8DATA2' TO N'F:\Data\A8DATA2.ndf', MOVE N'A8DATA3' TO N'F:\Data\A8DATA3.ndf', MOVE N'A8DATA4' TO N'F:\Data\A8DATA4.ndf', MOVE N'A8LOG1' TO N'G:\Log\A8LOG1.ldf', NOUNLOAD, STATS = 5

Can you please suggest like let it continue or stop n run the restoration with new command instead of GUI for freshly?

I have only 4 more hours window to complete the restoration.

Thanks,

Pols.

pols SSC Eights! Points: 935 More actions · Answer 5

Thank you so much. I am not cancelling it. Luckily the status showing 3%. I am saved. 🙂

But what could be the reason for that much slow ness?

2 weeks back other 1.2 TB db backup of 480 GB took 1/2 hr for the restoration in the same server. Now this time it is acting strangely. Please share your knowledge on this. So will take the precautions next time.

Regards

Pols

ratbak Ten Centuries Points: 1382 More actions · Answer 6

What percent_complete does the progress query return? (know that it's not necessarily linear/precise)

What reasons do you have to believe restore will suddenly become faster and complete in a small fraction of the current duration if you started over? Unless you identify specific very significant issues that if addressed would give you massive improvements, I would not recommend starting over.

When you say "I have only 4 more hours window to complete the restoration", do you mean there is a commitment (SLA) that the database will be available by that point? Have you communicated to the affected parties that may not be achieved? If so, it might be a good idea to give them time to prepare for whatever the consequences are for not being complete.

Is the server a VM? If so, have you talked to those in charge of virtual machines to see if they identify problems (competing traffic) or can increase resources/priority?

Is F: drive on a SAN? If so, have you talked to those in charge of the SAN to see if they identify problems (competing traffic) or can increase resources/priority?

Or is the server in the cloud? If cloud, which one and what type volumes are you using? If AWS EC2/EBS, for example, you might be able to temporarily throw a lot of provisioned I/O at it, though I'm not sure that would help at this point.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

pols wrote:

Thank you so much. I am not cancelling it. Luckily the status showing 3%. I am saved. 🙂
But what could be the reason for that much slow ness?
2 weeks back other 1.2 TB db backup of 480 GB took 1/2 hr for the restoration in the same server. Now this time it is acting strangely. Please share your knowledge on this. So will take the precautions next time.
Regards
Pols

Two primary reasons...

Instant file initialization is NOT enabled and so it has to "zero out" the 2.4TB mdf file.
Instant file initialization won't help with the log file. It has to format the log file with the same VLFs as the original file. If that's a mess of tiny VLFs, then it's going to take substantially longer to do all that VLF formatting.

--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)

pols SSC Eights! Points: 935 More actions · Answer 8

Hello,

Thank you very much for providing the information.

Restoration got failed.

able to see the error below in restore window. Leave the database ready to use by rolling back uncommited transactions.Additional transactional logs can not be restored.

I am trying to restore full backup freshly. Why i am not able to restore the backup?

Please help.

Thanks,

Pols.