| Author |
Topic |
|
Scout_Nige
Starting Member
4 Posts |
 Posted - 2010-01-27 : 05:28:24
|
| Hi,We have a very large SQL Server 2008 DB where I work and when we failover to the other node it takes 45 minutes+ for it to become available again - due to being "In Recovery".Last time it happened I found a Transact SQL script on the web which showed me the % complete so I could check it was going okay and estimate when it would be pack.However, I now can't find that SQL anywhere. Please help!Cheers,Nige |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-27 : 08:20:20
|
| are you talking about the WITH STATS option in the RESTORE LOG statement?RESTORE LOG dbname FROM logbackupfile WITH STATS |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 08:42:31
|
| Seems a long time for the failover to come upright?, but maybe that is the best that can be had.I'm just wondering if there is some log-shipping that is not very frequent, or somesuch, that could maybe be improved and thus shorten the time-to-recovery? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-27 : 09:38:58
|
| Is this a clustered SQL Server?Are you talking about restart-recovery (database in the state recovering...) and messages in the error log stating how long remains ?"SQL Server is recovering Database XYZ. Estimated time remaining 1221 seconds (Phase 2 of 3)?--Gail ShawSQL Server MVP |
|
|
|
Scout_Nige
Starting Member
4 Posts |
Posted - 2010-01-28 : 03:05:03
|
| Next to the DB name in SQL server management studio it says "(in recovery)". This can take quite some time due to the transaction log size and problems we've been having with Backup Exec.The SQL script I refer to returned a list of processes and one of the columns showed the percentage complete. This was handy to check that the process was progressing. Damn I wish I'd saved that script! |
|
|
|
Scout_Nige
Starting Member
4 Posts |
Posted - 2010-01-28 : 03:05:52
|
quote:
Originally posted by GilaMonster
Is this a clustered SQL Server?Are you talking about restart-recovery (database in the state recovering...) and messages in the error log stating how long remains ?"SQL Server is recovering Database XYZ. Estimated time remaining 1221 seconds (Phase 2 of 3)?--Gail ShawSQL Server MVP
Yep, it's a cluster. The DB goes into recovery mode when we move it to the other group. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 04:05:12
|
| "Backup Exec"So you are getting your standby server running by restoring database from Tape?I'm probably just interfering! but it seems like a slow process that could maybe be improved(I appreciate this doesn't help with the "How much time left", but if the recovery time was only a minute or two you wouldn't need the command!) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 04:06:51
|
| The script might be querying the perc_complete (I think) in sys.dm_exec_request. Otherwise you can open the error log and there will be regular entries in there as to how complete.Restart-recovery is not affected by the size of the tran log or any problems you're having with your backups (thought the latter needs fixing for obvious reasons). It's affected by the size of the active portion of the log (which in turn is affected by the length of transactions and the frequency of checkpoints) and the IO throughput.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 04:15:05
|
| And thus more frequent TLog backups, continuously restored, would solve that wouldn't they Gail?Or is this a completely different deal from Log Shipping? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-28 : 04:26:46
|
| If failover latency is a problem you should consider mirroring with a witness server. You would have failover within 2-3 seconds tops.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Scout_Nige
Starting Member
4 Posts |
Posted - 2010-01-28 : 04:47:09
|
| Hi, yep failover used to take 30 seconds max but this has increased due to problems we've been having with "backup exec" not successfully backing up the db/logs. Hopefully this is going to be fixed. My real query is that I need a SQL script to see the progress of the recovery mode. I did have one (so I know it's possible) but just can't find it now. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 06:38:00
|
quote:
Originally posted by Kristen
Or is this a completely different deal from Log Shipping?
Clustering, not log shipping. Single database shared between the two nodes (but used only by one).When a cluster failover is done, SQL is stopped on one node, started on the other and then restart-recovery happens on the databases.No backups or restores involved here.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 06:43:04
|
quote:
Originally posted by Scout_Nige
Hi, yep failover used to take 30 seconds max but this has increased due to problems we've been having with "backup exec" not successfully backing up the db/logs. Hopefully this is going to be fixed.
Cluster failover has nothing to do with backups of any form. The duration of the cluster failover and the SQL restart has to do with how fast SQL can be stopped on the one node, how fast the ownership of the drives can be transferred to the new node, how fast SQL can be started on the new node and how long the recovery of the databases takes.Recovery duration is based on how big the active portion of the log is. That it, the portion of the log that contains uncommitted transactions or transactions that have been committed but changes not yet hardened on disk.If you are having long-duration restart-recovery, I suggest that you take a look at your IO subsystem and your read and write throughput. quote:
My real query is that I need a SQL script to see the progress of the recovery mode. I did have one (so I know it's possible) but just can't find it now.
Did you try my suggestion of sys.dm_exec_requests? Does it not produce the information you want? Did you check the SQL error log? Are the recovering database messages not what you want?--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 06:55:33
|
| Sorry to hijack the thread"No backups or restores involved here."I took "backup exec" to be the Backup software (usually for Tape backup I think). Have I misunderstood that?(Or I'm misunderstanding how Backup Exec gets in the way of SQL doing its Recovery steps) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 07:00:26
|
quote:
Originally posted by Kristen
(Or I'm misunderstanding how Backup Exec gets in the way of SQL doing its Recovery steps)
It doesn't. Hence the point I'm trying to make. There may be a problem with backup exec but, unless it's causing long-running transactions, it's not the cause of the long restart-recovery.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:30:52
|
| OK, that's cleared up my confusion, thanks. |
|
|
|
|
Recovery mode - how long left?