Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem having restoring database backup file on secondary server Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 1:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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
Post #1432881
Posted Tuesday, March 19, 2013 1:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 417, Visits: 2,558
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.
Post #1432888
Posted Wednesday, March 20, 2013 6:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 2:48 AM
Points: 64, Visits: 367
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..?
Post #1433157
Posted Wednesday, March 20, 2013 9:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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)
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..?


There is enough space on dive on secondary server
Post #1433570
Posted Thursday, March 21, 2013 5:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 417, Visits: 2,558
Is this problem resolved?....I am curious.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1433723
Posted Friday, March 22, 2013 11:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 1,280, Visits: 2,815
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%'



Post #1434448
Posted Saturday, March 30, 2013 9:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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
Post #1437267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse