March 19, 2013 at 1:41 pm
Hi for last two days at one of our client side we have been trying figure out a why its taking two days two restore database backup files on secondary server. restore process is till going on and is stuck at 98% and no other process are there on secondary server.
using sp_who2 i could find only two lastwaittypes as BACKUPTHREAD and BACKUPIO. how should i proceed ahead with analysis . i mean does disk has some bottleneck problem ?/
the database of which we are trying to restore has file group.does it had to anything with filegropu
March 19, 2013 at 1:51 pm
anoop.mig29 (3/19/2013)
Hi for last two days at one of our client side we have been trying figure out a why its taking two days two restore database backup files on secondary server. restore process is till going on and is stuck at 98% and no other process are there on secondary server.using sp_who2 i could find only two lastwaittypes as BACKUPTHREAD and BACKUPIO. how should i proceed ahead with analysis . i mean does disk has some bottleneck problem ?/
the database of which we are trying to restore has file group.does it had to anything with filegropu
How big is this database?, are you getting any errors?, or it's just stuck at 98%?
SueTons.
Regards,
SQLisAwe5oMe.
March 20, 2013 at 6:08 am
[font="Courier New"]Hi Anoop,
Kindly check disk space where ur database files are resides or another way is you can take primary sever database files restore that .bak on secondary server.. try dis and let me know what happen..? [/font]
March 20, 2013 at 9:28 pm
SQLCrazyCertified (3/19/2013)
anoop.mig29 (3/19/2013)
Hi for last two days at one of our client side we have been trying figure out a why its taking two days two restore database backup files on secondary server. restore process is till going on and is stuck at 98% and no other process are there on secondary server.using sp_who2 i could find only two lastwaittypes as BACKUPTHREAD and BACKUPIO. how should i proceed ahead with analysis . i mean does disk has some bottleneck problem ?/
the database of which we are trying to restore has file group.does it had to anything with filegropu
How big is this database?, are you getting any errors?, or it's just stuck at 98%?
SueTons.
database is something around 400GB and We are not getting error but just that two waittypes and it is just stuck at 98 % increase marginally. DiskIO changes but after some time interval
bhushan_juare (3/20/2013)
[font="Courier New"]Hi Anoop,Kindly check disk space where ur database files are resides or another way is you can take primary sever database files restore that .bak on secondary server.. try dis and let me know what happen..? [/font]
There is enough space on dive on secondary server
March 21, 2013 at 5:16 am
Is this problem resolved?....I am curious.
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 11:44 am
I had this type of issue what a 100gig db that I was trying to back up. It would get so far and then the backup seemed to just hand but never fail. I opened a ticket with microsoft and we ended up finding it to be a hardware error. I cannot remember what it was though as that was over 2 years ago.
Not sure if this is of any help but here are two queries to check on the status:
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%RESTORE%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
----------------------------------------------------------------------------------------------------
USE MASTER
--
SELECT SESSION_ID, '[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE],
PERCENT_COMPLETE, START_TIME, STATUS, COMMAND,
DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME, CPU_TIME
FROM SYS.DM_EXEC_REQUESTS
--Apply this Where Clause Filter if you need to check specific events such as Backups, Restores, Index et al.
WHERE COMMAND LIKE '%BACKUP%' OR COMMAND LIKE '%RESTORE%' OR COMMAND LIKE '%INDEX%' OR COMMAND LIKE '%DBCC%'
March 30, 2013 at 9:45 pm
Thanks everybody for reply and as for status , that backup file git restored on secondary server but we were not able to pin-point why it took lot of time for restore
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply