May 6, 2011 at 1:23 am
Dear Sir,
I am using SQL Server 2005 Enterprise Edition. I have a backup of 40 GB Database. which was restored it. But my question is when I restoring the database then from 0 to 90 % restoring approxmiate 15 to 30 mnts. But from 90 % to 100 % job completion It took around 4 hrs. why it took the so long time after 90 %. ?
do you have any valuable idea please share me
thanks
😉
May 6, 2011 at 5:07 am
It could be blocking, other processes running on the server causing the restore to slow down. Or, it could be that open transactions on your backup are being rolled forward or rolled back, which occurs at the end of the restore process. Or, it could be both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2011 at 6:27 am
If you fancy going through it again, try running the following at intervals througout the restore process. It will tell you approx. how long's left, and what command it's currently processing. Might help identify the culprit...
SELECT
percent_complete AS 'PctComplete',
start_time AS 'StartTime',
command AS 'Command',
b.name AS 'DatabaseName',
DATEADD(ms,estimated_completion_time,GETDATE()) AS 'EstimatedEndTime',
(estimated_completion_time/1000/60) AS 'EstimatedMinutesToEnd'
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
--WHERE command like '%restore%’
where estimated_completion_time > 0
May 6, 2011 at 8:38 am
Or run
DBCC TRACEON(3004, 3605,-1)
to get detailed progress about the restore process in your SQL Server log file.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
May 6, 2011 at 9:14 am
Backup file and Restore path is Same ?
otherwise check Activity Monitor on SSMS and query with sys.sysprocesses to check the wait resource at the time of Restore
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply