Problem having restoring database backup file on secondary server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Problem having restoring database backup file...

Problem having restoring database backup file on secondary server

Rate Topic

Display Mode

Topic Options

Author

Message

anoop.mig29

Posted Tuesday, March 19, 2013 1:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 12:48 AM
Points: 25, Visits: 46

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

SQLCrazyCertified

Posted Tuesday, March 19, 2013 1:51 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:23 PM
Points: 312, Visits: 1,856

anoop.mig29 (3/19/2013)

How big is this database?, are you getting any errors?, or it's just stuck at 98%?

SueTons.

Post #1432888

bhushan_juare

Posted Wednesday, March 20, 2013 6:08 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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

anoop.mig29

Posted Wednesday, March 20, 2013 9:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 12:48 AM
Points: 25, Visits: 46

SQLCrazyCertified (3/19/2013)

anoop.mig29 (3/19/2013)

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)

There is enough space on dive on secondary server

Post #1433570

SQLCrazyCertified

Posted Thursday, March 21, 2013 5:16 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:23 PM
Points: 312, Visits: 1,856

Is this problem resolved?....I am curious.

SueTons.

Post #1433723

Markus

Posted Friday, March 22, 2013 11:44 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:37 AM
Points: 1,046, Visits: 2,208

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

anoop.mig29

Posted Saturday, March 30, 2013 9:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 12:48 AM
Points: 25, Visits: 46

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 »

Permissions