October 11, 2012 at 5:30 am
Hi,
I have some problem when I'm trying to restore a Database...
We have an Database which is in full recovery mode. And when I'm trying to restore it, it will take a long time....
The Database is about 10 Gb, and to restore the Full backup, and differential takes about 1 hour each, but then I have a lot of transaction log-files, which is around 100-150 MB each. Every transaction log takes about 1 hour to restore....
Is that normal, or do I have done something wrong?
With regards
Patrik
October 11, 2012 at 5:39 am
Its not normal.
From where you are trying to restore ? From network share or local drive? What wait type your restore operation waits for?
October 11, 2012 at 5:46 am
I restore from the local disc (which is an Iscsi-lun) from our Netapp.
Can I see what it is waiting for?
I'm running this to see how far it has come,
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
Where the percentage_complete is 100 % really fast, then it's just waiting...
October 12, 2012 at 7:46 am
After some research, I guess that it is in the REDO phase of the restore that takes long time...
Anda what I don't understand is why it should take long time...
The Backup schedule is a full backup once a week, Diferrential backup every day and Transact backup every 15 min.
It shouldn't be that much happened under th 15 mins...
Is there anything wrong with that Maintence Plan, or is it in the design of the database?
With Regards
Patrik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply