December 22, 2014 at 6:21 am
Hi Experts,
Recently I faced a big challenge as the production SQL database (SQL Server 2008 R2) goes to recovery mode once we restart the server. This doesn't complete after and long wait. After googling I decide to drop the database and restore from backup. But it has also another challenge as the DB is configured for replication for the reporting purpose. Now I have to delete the replication and drop the database. Then when I restore from back it unexpectedly took around 12 hrs, and the company loses business for the day. To manage this I decide to implement something like DB mirroring or Log shipping, not Replication as it is not a 100% copy of DB as it depends on primary key. For me, a real-time automatic failover is not at all essential as we can manage with a latency of 30 mins to 2 hours. So what will be best for us considering the above situation faced, is it Log shipping or mirroring. Kindly suggest.
Thanks in Advance
Regards
Pradeep
December 22, 2014 at 6:39 am
Why don't you focus on the problem rather than adding more complexity?
Your problem is slow recovery on restart. If you're not careful, adding HA could make things worse. Do some investigation, figure out why you're getting slow recovery, whether it was a once off or whether it's a consistent problem
If you insist on implementing HA, then start by identifying SLAs. You've said 2 hour downtime is acceptable. Do the business users agree? What about data loss allowance? What's the unit of failover? If the DB fails over to the secondary server, will the app work? Are there other resources the app needs that also have to fail over? Do you have the hardware for the planned HA/DR strategy? If not, what's the budget to buy more? Do you have the skills to manage it? If not, what happens when something goes wrong?
HA/DR planning is a hell of a lot more than just 'what singular technology are we going to use'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 8:10 am
Hello Gail,
Thanks a lot for your reply.
The situation is not often on restart and occur rarely. Please clarify me, if the DB is in state of Restoring, can I recover the DB using command "restore database <Database name> with recovery"? As per my understanding the Slow recovery may be due to DB replication and related log files.
The mirroring requirement is not only for the above situation, rather its what the company want to implement for HA. The 2hr downtime and data loss of 1hr is acceptable in a disaster situation. In case of fail over the apps connection need to be changed manually, so we need it to manual fail over. Regarding the skill I need to make up my myself.. :).
Also Please suggest how it would be if the mirror/secondary DB set up in same server on a separate instance.
Besides this if I set up mirroring / log shipping, in above scenario, i.e. if the DB gone for restoring state, what will happen to mirrored / secondary DB. As the mirrored / secondary DB are always in recovery mode, if I recover the mirrored or Secondary DB and connect to production is it ok? If so what should be the next step, shall I have to create another mirroring / log shipping set up for the new production DB and this cycle goes on?
Sorry for asking too many questions.. Please make me correct If I am asking any silly questions.
Thank you..
Regards
Pradeep
December 22, 2014 at 8:26 am
choudhury.pradeep (12/22/2014)
The situation is not often on restart and occur rarely. Please clarify me, if the DB is in state of Restoring, can I recover the DB using command "restore database <Database name> with recovery"? As per my understanding the Slow recovery may be due to DB replication and related log files.
RECOVERING != RESTORING. That command works on a database that's in the RESTORING state. After restart a DB goes through RECOVERING, not RESTORING
Also Please suggest how it would be if the mirror/secondary DB set up in same server on a separate instance.
Um... You want a HA scenario to go down completely if a single component of a single server fails. Not very *high* availability is it?
Besides this if I set up mirroring / log shipping, in above scenario, i.e. if the DB gone for restoring state, what will happen to mirrored / secondary DB.
Depends on how you set the mirroring up, and it's not the RECOVERING state that's relevant, it's the restart. Log shipping, nothing happens.
Sorry for asking too many questions..
You need to do a *huge* amount of reading, you need to set these things up in lab environments and work with them until you're comfortable, before you even consider suggesting them for production. Otherwise you're setting yourself and your company up for failure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 10:56 pm
Hi Pradeep,
Couple of questions...
What is the size of you Database? (mdf , ldf).
Auto Growth settings for your database (mdf and ldf)
Also share the output of the DBCC Loginfo for your database.
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 23, 2014 at 4:58 am
Hi Gail
Thanks for your reply.
Understand Restoring is not same to recovery. The full story is, in the morning I restarted the DB server, after restart when I open SSMS I check the DB status is “Restoring”. Then I wait for a 2-3 hrs still it’s restoring and never stops. Just for a note we have also replication going on for reporting. Then I finalized to drop the DB and restore from backup and proceed with same. But unfortunately the restring was dead slow for a DB size of 170 GB, it took around 10 hrs to reach 100%, then after 100% it again took around 1 hr. Now I try to check what’s may be the reason for such slow restoring and I suspect it might be a I/O issue and I check the system performance monitor and alas I see there is a number of read / write process of Symantec antivirus and due to this, it makes the read/write for the DB Log file @2.6 mbps. I disable the antivirus and read/write sparked to 100 mbps and the restore finished successfully. The whole day I search google a lot to identify slow restore nowhere found that antivirus may be also a cause.
I may be posting above experience in a wrong topic, sorry for same, but i feel to share what I experience so posted same.
Ok… so I identified the reason for slow restoring. But unable to find why the DB goes for restoring after restart in the morning. Is it may be due to same Antivirus? How can I identify same?
Hi Shafat,
Thanks you for your response.
The details are as
DB Size
DB Size - 174380.13 MB, unallocated space -10533.63 MB
Reserved- 66148608 KBdata-50187136 KB
index_size-14191600 KBunused1769872 KB
Auto Growth
Mdf – 10%, unrestricted
Ndf – 10%, unrestricted
Ldf – 10%, restricted to 2097152 MB
DBCC loginfo
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
213041664819228635021280
2130416641304985628635121280
265536026091520286352264186770000000816900373
265536026746880286353264186770000000816900373
265536027402240286354264186770000000816900373
271270428057600286355264186770000000816900373
272089628770304286356264186770000001105300368
272089629491200286357264186770000001105300368
272089630212096286358264186770000001105300368
272089630932992286359264186770000001105300368
278643231653888286360264186770000001429400423
278643232440320286361264186770000001429400423
278643233226752286362264186770000001429400423
285196834013184286363264186770000001429400423
285196834865152286364264186770000001789400338
285196835717120286365264186770000001789400338
285196836569088286366264186770000001789400338
298304037421056286367264186770000001789400338
291750438404096286368264186770000002186000432
291750439321600286369264186770000002186000432
291750440239104286370264186770000002186000432
2111411241156608286371264186770000002186000432
2104857642270720286372264186771000000073100398
2104857643319296286373264186771000000073100398
2104857644367872286374264186771000000073100398
21114112454164482863752128186771000000073100398
21114112465305602863762128186775000000037600415
21114112476446722863772128186775000000037600415
21114112487587842863782128186775000000037600415
21310720498728962863792128186775000000037600415
21245184511836162863802128186779000000025600297
21245184524288002863812128186779000000025600297
21245184536739842863822128186779000000025600297
21376256549191682863832128186779000000025600297
21376256562954242863842128186782000000132700368
21376256576716802863852128186782000000132700368
21376256590479362863862128186782000000132700368
21507328604241922863872128186782000000132700368
21507328619315202863882128186799000000037600065
21507328634388482863892128186799000000037600065
21507328649461762863902128186799000000037600065
21703936664535042863912128186799000000037600065
21703936681574402863922128186801000000229600407
21703936698613762863932128186801000000229600407
21703936715653122863942128186801000000229600407
21703936732692482863952128186801000000229600407
21835008749731842863962128186804000000204900422
21835008768081922863972128186804000000204900422
21835008786432002863982128186804000000204900422
22031616804782082863992128186804000000204900422
22031616825098242864002128186807000000229600424
22031616845414402864012128186807000000229600424
22031616865730562864022128186807000000229600424
22162688886046722864032128186807000000229600424
22228224907673602864042128186810000000300200422
22228224929955842864052128186810000000300200422
22228224952238082864062128186810000000300200422
22424832974520322864072128186810000000300200422
22490368998768642864082128186814000000013600049
224903681023672322864092128186814000000013600049
224903681048576002864102128186814000000013600049
225559041073479682864112128186814000000013600049
227525121099038722864122128186827000000036400182
227525121126563842864132128186827000000036400182
227525121154088962864142128186827000000036400182
227525121181614082864152128186827000000036400182
230146561209139202864162128186829000000320300357
230146561239285762864172128186829000000320300357
230146561269432322864182128186829000000320300357
230801921299578882864192128186829000000320300357
232768001330380802864202128186832000000156800423
232768001363148802864212128186832000000156800423
232768001395916802864222128186832000000156800423
234734081428684802864232128186832000000156800423
236700161463418882864242128186835000000061600168
236700161500119042864252128186835000000061600168
236700161536819202864262128186835000000061600168
236700161573519362864272128186835000000061600168
239976961610219522864282128186837000000564000419
239976961650196482864292128186837000000564000419
239976961690173442864302128186837000000564000419
241287681730150402864312128186837000000564000419
243909121771438082864322128186840000000552400454
243909121815347202864332128186840000000552400454
243909121859256322864342128186840000000552400454
245875201903165442864352128186840000000552400454
248496641949040642864362128186843000000577600422
248496641997537282864372128186843000000577600422
248496642046033922864382128186843000000577600422
249807362094530562864392128186843000000577600422
253739522144337922864402128186846000000589600428
253739522198077442864412128186846000000589600428
253739522251816962864422128186846000000589600428
253739522305556482864432128186846000000589600428
258982402359296002864442128186849000000720500419
258982402418278402864452128186849000000720500419
258982402477260802864462128186849000000720500419
258982402536243202864472128186849000000720500419
264880642595225602864482128186853000000109600198
264880642660106242864492128186853000000109600198
264880642724986882864502128186853000000109600198
264880642789867522864512128186853000000109600198
271434242854748162864522128186856000000275400216
271434242926182402864532128186856000000275400216
271434242997616642864542128186856000000275400216
271434243069050882864552128186856000000275400216
278643203140485122864562128186859000000625600419
278643203219128322864572128186859000000625600419
278643203297771522864582128186859000000625600419
278643203376414722864592128186859000000625600419
286507523455057922864602128186863000000096200248
286507523541565442864612128186863000000096200248
286507523628072962864622128186863000000096200248
286507523714580482864632128186863000000096200248
295027203801088002864642128186866000000505600419
295027203896115202864652128186866000000505600419
295027203991142402864662128186866000000505600419
295027204086169602864672128186866000000505600419
2104202244181196802864682128186869000001114200449
2104202244285399042864692128186869000001114200449
2104202244389601282864702128186869000001114200449
2105512964493803522864712128186869000001114200449
2114688004599316482864722128186873000000475900417
2114688004714004482864732128186873000000475900417
2114688004828692482864742128186873000000475900417
2115998724943380482864752128186873000000475900417
2126484485059379202864762128186876000001273600422
2126484485185863682864772128186876000001273600422
2126484485312348162864782128186876000001273600422
2126484485438832642864792128186876000001273600422
2138936325565317122864802128186880000000765200204
2138936325704253442864812128186880000000765200204
2138936325843189762864822128186880000000765200204
2140247045982126082864832128186880000000765200204
215269888612237312286484264187061000000159200064
215269888627507200286485264187061000000159200064
215269888642777088286486264187061000000159200064
215466496658046976286487264187061000000159200064
28388608673513472286488264187065000002972500079
28388608681902080286489264187065000002972500079
28388608690290688286490264187065000002972500079
28388608698679296286491264187065000002972500079
28388608707067904286492264187065000002972500079
28388608715456512286493264187065000002972500079
28388608723845120286494264187065000002972500079
28650752732233728286495264187065000002972500079
29240576740884480286496264187073000001649000033
29240576750125056286497264187073000001649000033
29240576759365632286498264187073000001649000033
29240576768606208286499264187073000001649000033
29240576777846784286500264187073000001649000033
29240576787087360286501264187073000001649000033
29240576796327936286502264187073000001649000033
29437184805568512286503264187073000001649000033
210158080815005696286504264187081000001798500052
210158080825163776286505264187081000001798500052
210158080835321856286506264187081000001798500052
210158080845479936286507264187081000001798500052
210158080855638016286508264187081000001798500052
210158080865796096286509264187081000001798500052
210158080875954176286510264187081000001798500052
210420224886112256286511264187081000001798500052
2112066568965324802865122128187089000001992000039
2112066569077391362865132128187089000001992000039
2112066569189457922865142128187089000001992000039
2112066569301524482865152128187089000001992000039
2112066569413591042865162128187089000001992000039
2112066569525657602865172128187089000001992000039
2112066569637724162865182128187089000001992000039
2112066569749790722865192128187089000001992000039
2123207689861857282865202128187097000002152000002
2123207689985064962865212128187097000002152000002
21232076810108272642865222128187097000002152000002
21232076810231480322865232128187097000002152000002
21232076810354688002865242128187097000002152000002
21232076810477895682865252128187097000002152000002
21232076810601103362865262128187097000002152000002
21238630410724311042865272128187097000002152000002
21356595210848174082865282128187141000000446100023
21356595210983833602865292128187141000000446100023
21356595211119493122865302128187141000000446100023
21356595211255152642865312128187141000000446100023
21356595211390812162865322128187141000000446100023
21356595211526471682865332128187141000000446100023
21356595211662131202865342128187141000000446100023
21356595211797790722865352128187141000000446100023
21487667211933450242865362128187576000001819100022
21487667212082216962865372128187576000001819100022
21487667212230983682865382128187576000001819100022
21487667212379750402865392128187576000001819100022
21487667212528517122865402128187576000001819100022
21487667212677283842865412128187576000001819100022
21487667212826050562865422128187576000001819100022
21520435212974817282865432128187576000001819100022
21638400013126860802865442128187972000002928800060
21638400013290700802865452128187972000002928800060
21638400013454540802865462128187972000002928800060
21638400013618380802865472128187972000002928800060
21638400013782220802865482128187972000002928800060
21638400013946060802865492128187972000002928800060
21638400014109900802865502128187972000002928800060
21658060814273740802865512128187972000002928800060
21802240014439546882865522128187980000003189400066
21802240014619770882865532128187980000003189400066
21802240014799994882865542128187980000003189400066
21802240014980218882865552128187980000003189400066
21802240015160442882865562128187980000003189400066
21802240015340666882865572128187980000003189400066
21802240015520890882865582128187980000003189400066
21828454415701114882865592128187980000003189400066
21985740815883960322865602128187988000003533400009
21985740816082534402865612128187988000003533400009
21985740816281108482865622128187988000003533400009
21985740816479682562865632128187988000003533400009
21985740816678256642865642128187988000003533400009
21985740816876830722865652128187988000003533400009
21985740817075404802865662128187988000003533400009
21985740817273978882865672128187988000003533400009
22182348817472552962865682128187996000003837800037
22182348817690787842865692128187996000003837800037
22182348817909022722865702128187996000003837800037
22182348818127257602865712128187996000003837800037
22182348818345492482865722128187996000003837800037
22182348818563727362865732128187996000003837800037
22182348818781962242865742128187996000003837800037
22195456019000197122865752128187996000003837800037
22398617619219742722865762128188004000004248900020
22398617619459604482865772128188004000004248900020
22398617619699466242865782128188004000004248900020
22398617619939328002865792128188004000004248900020
22398617620179189762865802128188004000004248900020
22398617620419051522865812128188004000004248900020
22398617620658913282865822128188004000004248900020
22431385620898775042865832128188004000004248900020
22641100821141913602865842128188012000004708800016
22641100821406023682865852128188012000004708800016
22641100821670133762865862128188012000004708800016
22641100821934243842865872128188012000004708800016
22641100822198353922865882128188012000004708800016
22641100822462464002865892128188012000004708800016
22641100822726574082865902128188012000004708800016
22654208022990684162865912128188012000004708800016
22903244823256104962865922128188087000001943300030
22903244823546429442865932128188087000001943300030
22903244823836753922865942128188087000001943300030
2290324482412707840286595264188087000001943300030
2290324482441740288286596264188087000001943300030
2290324482470772736286597264188087000001943300030
2290324482499805184286598264188087000001943300030
2293601282528837632286599264188087000001943300030
2319815682558197760286600264188095000005693600040
2319815682590179328286601264188095000005693600040
2319815682622160896286602264188095000005693600040
2319815682654142464286603264188095000005693600040
2319815682686124032286604264188095000005693600040
2319815682718105600286605264188095000005693600040
2319815682750087168286606264188095000005693600040
2319815682782068736286607264188095000005693600040
2351272962814050304286608264188103000006197600061
2351272962849177600286609264188103000006197600061
2351272962884304896286610264188103000006197600061
2351272962919432192286611264188103000006197600061
2351272962954559488286612264188103000006197600061
2351272962989686784286613264188103000006197600061
2351272963024814080286614264188103000006197600061
2355205123059941376286615264188103000006197600061
2386662403095461888286616264188111000006898200138
2386662403134128128286617264188111000006898200138
2386662403172794368286618264188111000006898200138
2386662403211460608286619264188111000006898200138
2386662403250126848286620264188111000006898200138
2386662403288793088286621264188111000006898200138
2386662403327459328286622264188111000006898200138
2389283843366125568286623264188111000006898200138
2425328643405053952286624264188119000007555300088
2425328643447586816286625264188119000007555300088
2425328643490119680286626264188119000007555300088
2425328643532652544286627264188119000007555300088
2425328643575185408286628264188119000007555300088
2425328643617718272286629264188119000007555300088
2425328643660251136286630264188119000007555300088
2427950083702784000286631264188119000007555300088
2467927043745579008286632264188127000008317600037
2467927043792371712286633264188127000008317600037
2467927043839164416286634264188127000008317600037
2467927043885957120286635264188127000008317600037
2467927043932749824286636264188127000008317600037
2467927043979542528286637264188127000008317600037
2467927044026335232286638264188127000008317600037
2470548484073127936286639264188127000008317600037
2514457604120182784286640264188135000009151000020
2514457604171628544286641264188135000009151000020
2514457604223074304286642264188135000009151000020
2514457604274520064286643264188135000009151000020
2514457604325965824286644264188135000009151000020
2514457604377411584286645264188135000009151000020
2514457604428857344286646264188135000009151000020
251904512448030310400128188135000009151000020
25662310445322076160064190059000003159200060
25662310445888307200064190059000003159200060
25662310446454538240064190059000003159200060
25662310447020769280064190059000003159200060
25662310447587000320064190059000003159200060
25662310448153231360064190059000003159200060
25662310448719462400064190059000003159200060
25688524849285693440064190059000003159200060
26232473649854545920064190067000011069600037
26232473650477793280064190067000011069600037
26232473651101040640064190067000011069600037
26232473651724288000064190067000011069600037
26232473652347535360064190067000011069600037
26232473652970782720064190067000011069600037
26232473653594030080064190067000011069600037
26232473654217277440064190067000011069600037
26855065654840524800064190075000012134900010
26855065655526031360064190075000012134900010
26855065656211537920064190075000012134900010
26855065656897044480064190075000012134900010
26855065657582551040064190075000012134900010
26855065658268057600064190075000012134900010
26855065658953564160064190075000012134900010
26855065659639070720064190075000012134900010
27536640060324577280064190083000013347600025
27536640061078241280064190083000013347600025
27536640061831905280064190083000013347600025
27536640062585569280064190083000013347600025
27536640063339233280064190083000013347600025
27536640064092897280064190083000013347600025
27536640064846561280064190083000013347600025
27569408065600225280064190083000013347600025
28290304066357166080064192905000001989700052
28290304067186196480064192905000001989700052
28290304068015226880064192905000001989700052
28290304068844257280064192905000001989700052
28290304069673287680064192905000001989700052
28290304070502318080064192905000001989700052
28290304071331348480064192905000001989700052
28329625672160378880064192905000001989700052
29122611272993341440064192913000016229400126
29122611273905602560064192913000016229400126
29122611274817863680064192913000016229400126
29122611275730124800064192913000016229400126
29122611276642385920064192913000016229400126
29122611277554647040064192913000016229400126
29122611278466908160064192913000016229400126
291357184793791692800128192913000016229400126
2100335616802927411200128192921000017804200060
2100335616812960972800128192921000017804200060
2100335616822994534400128192921000017804200060
2100335616833028096000128192921000017804200060
2100335616843061657600128192921000017804200060
2100335616853095219200128192921000017804200060
2100335616863128780800128192921000017804200060
2100597760873162342400128192921000017804200060
2110362624883222118400128195906000013348000048
2110362624894258380800128195906000013348000048
2110362624905294643200128195906000013348000048
2110362624916330905600128195906000013348000048
2110362624927367168000128195906000013348000048
2110362624938403430400128195906000013348000048
2110362624949439692800128195906000013348000048
2110690304960475955200128195906000013348000048
2121438208971544985600128195914000021579200004
2121438208983688806400128195914000021579200004
2121438208995832627200128195914000021579200004
21214382081007976448000128195914000021579200004
21214382081020120268800128195914000021579200004
21214382081032264089600128195914000021579200004
21214382081044407910400128195914000021579200004
2132055040105655173120064235026000023681600002
2132055040106975723520064235026000023681600002
2132055040108296273920064235026000023681600002
2132055040109616824320064235026000023681600002
2132055040110937374720064235026000023681600002
2132055040112257925120064235026000023681600002
2132055040113578475520064235026000023681600002
2132186112114899025920064235026000023681600002
272613888116220887040064236792000005629400026
272613888116947025920064236792000005629400026
272613888117673164800064236792000005629400026
272613888118399303680064236792000005629400026
272613888119125442560064236792000005629400026
272613888119851581440064236792000005629400026
272613888120577720320064236792000005629400026
272613888121303859200064236792000005629400026
272613888122029998080064236792000005629400026
272613888122756136960064236792000005629400026
272613888123482275840064236792000005629400026
272613888124208414720064236792000005629400026
272613888124934553600064236792000005629400026
272613888125660692480064236792000005629400026
272613888126386831360064236792000005629400026
273007104127112970240064236792000005629400026
27988838412784304128286349264236808000014210000080
2798883841286419251200128236808000014210000080
2798883841294408089600128236808000014210000080
2798883841302396928000128236808000014210000080
2798883841310385766400128236808000014210000080
2798883841318374604800128236808000014210000080
2798883841326363443200128236808000014210000080
2798883841334352281600128236808000014210000080
2798883841342341120000128236808000014210000080
2798883841350329958400128236808000014210000080
2798883841358318796800128236808000014210000080
2798883841366307635200128236808000014210000080
2798883841374296473600128236808000014210000080
2798883841382285312000128236808000014210000080
2798883841390274150400128236808000014210000080
2801505281398262988800128236808000014210000080
2878837761406278041600128236824000015609100052
2878837761415066419200128236824000015609100052
2878837761423854796800128236824000015609100052
2878837761432643174400128236824000015609100052
2878837761441431552000128236824000015609100052
2878837761450219929600128236824000015609100052
2878837761459008307200128236824000015609100052
2878837761467796684800128236824000015609100052
2878837761476585062400128236824000015609100052
2878837761485373440000128236824000015609100052
2878837761494161817600128236824000015609100052
2878837761502950195200128236824000015609100052
2878837761511738572800128236824000015609100052
2878837761520526950400128236824000015609100052
2878837761529315328000128236824000015609100052
2880148481538103705600128236824000015609100052
2966656001546905190400128236840000017146100054
2966656001556571750400128236840000017146100054
2966656001566238310400128236840000017146100054
2966656001575904870400128236840000017146100054
2966656001585571430400128236840000017146100054
2966656001595237990400128236840000017146100054
2966656001604904550400128236840000017146100054
2966656001614571110400128236840000017146100054
2966656001624237670400128236840000017146100054
2966656001633904230400128236840000017146100054
2966656001643570790400128236840000017146100054
2966656001653237350400128236840000017146100054
2966656001662903910400128236840000017146100054
2966656001672570470400128236840000017146100054
2966656001682237030400128236840000017146100054
2969277441691903590400128236840000017146100054
21062993921701596364800128236856000018890500026
21062993921712226304000128236856000018890500026
21062993921722856243200128236856000018890500026
21062993921733486182400128236856000018890500026
21062993921744116121600128236856000018890500026
21062993921754746060800128236856000018890500026
21062993921765376000000128236856000018890500026
21062993921776005939200128236856000018890500026
21062993921786635878400128236856000018890500026
21062993921797265817600128236856000018890500026
21062993921807895756800128236856000018890500026
21062993921818525696000128236856000018890500026
21062993921829155635200128236856000018890500026
21062993921839785574400128236856000018890500026
21062993921850415513600128236856000018890500026
21071513601861045452800128236856000018890500026
21169817601871760588800128236872000020889200013
21169817601883458764800128236872000020889200013
21169817601895156940800128236872000020889200013
21169817601906855116800128236872000020889200013
21169817601918553292800128236872000020889200013
21169817601930251468800128236872000020889200013
21169817601941949644800128236872000020889200013
21169817601953647820800128236872000020889200013
21169817601965345996800128236872000020889200013
21169817601977044172800128236872000020889200013
21169817601988742348800128236872000020889200013
21169817602000440524800128236872000020889200013
21169817602012138700800128236872000020889200013
21169817602023836876800128236872000020889200013
21169817602035535052800128236872000020889200013
21170472962047233228800128236872000020889200013
21286471682058937958400128236888000022817200039
21286471682071802675200128236888000022817200039
21286471682084667392000128236888000022817200039
21286471682097532108800128236888000022817200039
21286471682110396825600128236888000022817200039
21286471682123261542400128236888000022817200039
21286471682136126259200128236888000022817200039
21286471682148990976000128236888000022817200039
21286471682161855692800128236888000022817200039
21286471682174720409600128236888000022817200039
21286471682187585126400128236888000022817200039
21286471682200449843200128236888000022817200039
21286471682213314560000128236888000022817200039
21286471682226179276800128236888000022817200039
21286471682239043993600128236888000022817200039
21292369922251908710400128236888000022817200039
21414922242264832409600128236904000025193800066
21414922242278981632000128236904000025193800066
21414922242293130854400128236904000025193800066
21414922242307280076800128236904000025193800066
21414922242321429299200128236904000025193800066
21414922242335578521600128236904000025193800066
21414922242349727744000128236904000025193800066
21414922242363876966400128236904000025193800066
21414922242378026188800128236904000025193800066
21414922242392175411200128236904000025193800066
21414922242406324633600128236904000025193800066
21414922242420473856000128236904000025193800066
21414922242434623078400128236904000025193800066
21414922242448772300800128236904000025193800066
21414922242462921523200128236904000025193800066
21424752642477070745600128236904000025193800066
21556480002491318272000128236920000027783200068
21556480002506883072000128236920000027783200068
21556480002522447872000128236920000027783200068
21556480002538012672000128236920000027783200068
21556480002553577472000128236920000027783200068
21556480002569142272000128236920000027783200068
21556480002584707072000128236920000027783200068
21556480002600271872000128236920000027783200068
21556480002615836672000128236920000027783200068
21556480002631401472000128236920000027783200068
21556480002646966272000128236920000027783200068
21556480002662531072000128236920000027783200068
21556480002678095872000128236920000027783200068
21556480002693660672000128236920000027783200068
21556480002709225472000128236920000027783200068
21566310402724790272000128236920000027783200068
21712455682740453376000128236936000030545700067
21712455682757577932800128236936000030545700067
21712455682774702489600128236936000030545700067
21712455682791827046400128236936000030545700067
21712455682808951603200128236936000030545700067
21712455682826076160000128236936000030545700067
21712455682843200716800128236936000030545700067
21712455682860325273600128236936000030545700067
21712455682877449830400128236936000030545700067
21712455682894574387200128236936000030545700067
21712455682911698944000128236936000030545700067
21712455682928823500800128236936000030545700067
21712455682945948057600128236936000030545700067
21712455682963072614400128236936000030545700067
21712455682980197171200128236936000030545700067
21717698562997321728000128236936000030545700067
21883504643014498713600128236952000033507400026
21883504643033333760000128236952000033507400026
21883504643052168806400128236952000033507400026
21883504643071003852800128236952000033507400026
21883504643089838899200128236952000033507400026
21883504643108673945600128236952000033507400026
21883504643127508992000128236952000033507400026
21883504643146344038400128236952000033507400026
21883504643165179084800128236952000033507400026
21883504643184014131200128236952000033507400026
21883504643202849177600128236952000033507400026
21883504643221684224000128236952000033507400026
21883504643240519270400128236952000033507400026
21883504643259354316800128236952000033507400026
21883504643278189363200128236952000033507400026
21892679683297024409600128236952000033507400026
22072248323315951206400128236968000036920400065
22072248323336673689600128236968000036920400065
22072248323357396172800128236968000036920400065
22072248323378118656000128236968000036920400065
22072248323398841139200128236968000036920400065
22072248323419563622400128236968000036920400065
22072248323440286105600128236968000036920400065
22072248323461008588800128236968000036920400065
22072248323481731072000128236968000036920400065
22072248323502453555200128236968000036920400065
22072248323523176038400128236968000036920400065
22072248323543898521600128236968000036920400065
22072248323564621004800128236968000036920400065
22072248323585343488000128236968000036920400065
22072248323606065971200128236968000036920400065
22076180483626788454400128236968000036920400065
22279342083647550259200128236984000040510500013
22279342083670343680000128236984000040510500013
22279342083693137100800128236984000040510500013
22279342083715930521600128236984000040510500013
22279342083738723942400128236984000040510500013
22279342083761517363200128236984000040510500013
22279342083784310784000128236984000040510500013
22279342083807104204800128236984000040510500013
22279342083829897625600128236984000040510500013
22279342083852691046400128236984000040510500013
22279342083875484467200128236984000040510500013
22279342083898277888000128236984000040510500013
22279342083921071308800128236984000040510500013
22279342083943864729600128236984000040510500013
22279342083966658150400128236984000040510500013
22285895683989451571200128236984000040510500013
22507407364012310528000128237000000044608700002
22507407364037384601600128237000000044608700002
22507407364062458675200128237000000044608700002
22507407364087532748800128237000000044608700002
22507407364112606822400128237000000044608700002
22507407364137680896000128237000000044608700002
22507407364162754969600128237000000044608700002
22507407364187829043200128237000000044608700002
22507407364212903116800128237000000044608700002
22507407364237977190400128237000000044608700002
22507407364263051264000128237000000044608700002
22507407364288125337600128237000000044608700002
22507407364313199411200128237000000044608700002
22507407364338273484800128237000000044608700002
22507407364363347558400128237000000044608700002
22511994884388421632000128237000000044608700002
22758410244413541580800128237016000049016100065
22758410244441125683200128237016000049016100065
22758410244468709785600128237016000049016100065
22758410244496293888000128237016000049016100065
22758410244523877990400128237016000049016100065
22758410244551462092800128237016000049016100065
22758410244579046195200128237016000049016100065
22758410244606630297600128237016000049016100065
22758410244634214400000128237016000049016100065
22758410244661798502400128237016000049016100065
22758410244689382604800128237016000049016100065
22758410244716966707200128237016000049016100065
22758410244744550809600128237016000049016100065
22758410244772134912000128237016000049016100065
22758410244799719014400128237016000049016100065
22759720964827303116800128237016000049016100065
23034316804854900326400128237032000053857800226
23034316804885243494400128237032000053857800226
23034316804915586662400128237032000053857800226
23034316804945929830400128237032000053857800226
23034316804976272998400128237032000053857800226
23034316805006616166400128237032000053857800226
23034316805036959334400128237032000053857800226
23034316805067302502400128237032000053857800226
23034316805097645670400128237032000053857800226
23034316805127988838400128237032000053857800226
23034316805158332006400128237032000053857800226
23034316805188675174400128237032000053857800226
23034316805219018342400128237032000053857800226
23034316805249361510400128237032000053857800226
23034316805279704678400128237032000053857800226
23034316805310047846400128237032000053857800226
23337748485340391014400128237048000059227200005
23337748485373768499200128237048000059227200005
23337748485407145984000128237048000059227200005
23337748485440523468800128237048000059227200005
23337748485473900953600128237048000059227200005
23337748485507278438400128237048000059227200005
23337748485540655923200128237048000059227200005
23337748485574033408000128237048000059227200005
23337748485607410892800128237048000059227200005
23337748485640788377600128237048000059227200005
23337748485674165862400128237048000059227200005
23337748485707543347200128237048000059227200005
23337748485740920832000128237048000059227200005
23337748485774298316800128237048000059227200005
23337748485807675801600128237048000059227200005
23337748485841053286400128237048000059227200005
23671326725874430771200128237064000065150400016
23671326725911144038400128237064000065150400016
23671326725947857305600128237064000065150400016
23671326725984570572800128237064000065150400016
23671326726021283840000128237064000065150400016
23671326726057997107200128237064000065150400016
23671326726094710374400128237064000065150400016
23671326726131423641600128237064000065150400016
23671326726168136908800128237064000065150400016
23671326726204850176000128237064000065150400016
23671326726241563443200128237064000065150400016
23671326726278276710400128237064000065150400016
23671326726314989977600128237064000065150400016
23671326726351703244800128237064000065150400016
23671326726388416512000128237064000065150400016
23674603526425129779200128237064000065150400016
24038328326461875814400128237080000071726200053
24038328326502259097600128237080000071726200053
24038328326542642380800128237080000071726200053
24038328326583025664000128237080000071726200053
24038328326623408947200128237080000071726200053
24038328326663792230400128237080000071726200053
24038328326704175513600128237080000071726200053
24038328326744558796800128237080000071726200053
24038328326784942080000128237080000071726200053
24038328326825325363200128237080000071726200053
24038328326865708646400128237080000071726200053
24038328326906091929600128237080000071726200053
24038328326946475212800128237080000071726200053
24038328326986858496000128237080000071726200053
24038328327027241779200128237080000071726200053
24044226567067625062400128237080000071726200053
24442030087108067328000128237096000078943400052
24442030087152487628800128237096000078943400052
24442030087196907929600128237096000078943400052
24442030087241328230400128237096000078943400052
24442030087285748531200128237096000078943400052
24442030087330168832000128237096000078943400052
24442030087374589132800128237096000078943400052
24442030087419009433600128237096000078943400052
24442030087463429734400128237096000078943400052
24442030087507850035200128237096000078943400052
24442030087552270336000128237096000078943400052
24442030087596690636800128237096000078943400052
24442030087641110937600128237096000078943400052
24442030087685531238400128237096000078943400052
24442030087729951539200128237096000078943400052
24450549767774371840000128237096000078943400052
24886364167818877337600128237112000086883000013
24886364167867740979200128237112000086883000013
24886364167916604620800128237112000086883000013
24886364167965468262400128237112000086883000013
24886364168014331904000128237112000086883000013
24886364168063195545600128237112000086883000013
24886364168112059187200128237112000086883000013
24886364168160922828800128237112000086883000013
24886364168209786470400128237112000086883000013
24886364168258650112000128237112000086883000013
24886364168307513753600128237112000086883000013
24886364168356377395200128237112000086883000013
24886364168405241036800128237112000086883000013
24886364168454104678400128237112000086883000013
24886364168502968320000128237112000086883000013
24893573128551831961600128237112000086883000013
25375262728600767692800128239961000086706400102
25375262728654520320000128239961000086706400102
25375262728708272947200128239961000086706400102
25375262728762025574400128239961000086706400102
25375262728815778201600128239961000086706400102
25375262728869530828800128239961000086706400102
25375262728923283456000128239961000086706400102
25375262728977036083200128239961000086706400102
25375262729030788710400128239961000086706400102
25375262729084541337600128239961000086706400102
25375262729138293964800128239961000086706400102
25375262729192046592000128239961000086706400102
25375262729245799219200128239961000086706400102
25375262729299551846400128239961000086706400102
25375262729353304473600128239961000086706400102
25379194889407057100800128239961000086706400102
25912657929460849049600128285063000048916800115
25912657929519975628800128285063000048916800115
25912657929579102208000128285063000048916800115
25912657929638228787200128285063000048916800115
25912657929697355366400128285063000048916800115
25912657929756481945600128285063000048916800115
25912657929815608524800128285063000048916800115
25912657929874735104000128285063000048916800115
25912657929933861683200128285063000048916800115
25912657929992988262400128285063000048916800115
259126579210052114841600128285063000048916800115
259126579210111241420800128285063000048916800115
259126579210170368000000128285063000048916800115
259126579210229494579200128285063000048916800115
259126579210288621158400128285063000048916800115
259185561610347747737600128285063000048916800115
Thank you friends..
Regards
Pradeep
December 23, 2014 at 5:12 am
choudhury.pradeep (12/23/2014)
Understand Restoring is not same to recovery. The full story is, in the morning I restarted the DB server, after restart when I open SSMS I check the DB status is “Restoring”.
A DB will be in the RECOVERING state after a restart, not RESTORING. Only way a DB gets into RESTORING is if someone's done a RESTORE WITH NORECOVERY or an interrupted restore of the database.
I check the system performance monitor and alas I see there is a number of read / write process of Symantec antivirus and due to this, it makes the read/write for the DB Log file @2.6 mbps. I disable the antivirus and read/write sparked to 100 mbps and the restore finished successfully. The whole day I search google a lot to identify slow restore nowhere found that antivirus may be also a cause.
Here is a recommendation, that helps to optimize the Antivirusscanner exclusion list for a smooth SQL Server Operation:
SQL Server data files
*.mdf
*.ldf
*.ndf
SQL Server backup files
*.bak
*.trn
http://www.brentozar.com/archive/2014/05/configure-anti-virus-sql-servers/
Here’s what you should know if you’ve got an anti-virus tool on your SQL Server.
Set the right Anti-Virus folder, file, and process exclusions
Good news: you don’t have take my word on what types of files and directories your anti-virus software should exclude. Microsoft lays it all out for you in KB 309422
http://support.microsoft.com/kb/309422
When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them.
SQL Server data files
These files usually have one of the following file-name extensions:
.mdf
.ldf
.ndf
SQL Server backup files
These files frequently have one of the following file-name extensions:
.bak
.trn
Ok… so I identified the reason for slow restoring. But unable to find why the DB goes for restoring after restart in the morning. Is it may be due to same Antivirus? How can I identify same?
The antivirus (which as mentioned earlier should exclude database files) and the large number of VLFs you have could result in the DB being in the RECOVERING state for some time after a restart. RECOVERING, not RESTORING.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2014 at 5:17 am
Dear Pradeep,
Output of DBCC Loginfo shows the number VLF's you have in your Log File (ldf), From what i can see you have WAY TOO MANY...!!
too many VLF's create substantial performance impact. their are small things that you can do resolve this issue.
First thing to look into is the Auto Growth option.
DONT GO WITH DEFAULTS....!! Set the autogrowth to a designated amount.
I would strongly suggest you read the article on the below mentioned URL by Kimberly Tripp.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
Apart from this there are a lot of resources on VLF's go through some of them and you will have a fair idea how this is a mess.
Hope it Helps...!!
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 24, 2014 at 4:42 am
Hi Gail / Shafat,
Thanks for taking your valuable time for my queries.
So have few last queries....,
?What I suppose to do if I find my DB taking a long time in recovery state after a server reboot?
?We have set our DB for Simple recovery and do full DB backup in night 2:00 AM, Differential backup every 6 hrs. As its simple recovery no Log backup. The problem every 6-7 days the Log file size increase around 100 to 110 GB. What is the best practice to manage the growth of Log files? I usually do shrink log, is it the correct way?
?The Log file set to auto growth of 10% and restricted to 2TB (means as good as unrestricted). Is it recommended to restrict the growth?
Regards
Pradeep
December 24, 2014 at 5:34 am
Dear Pradeep,
What I suppose to do if I find my DB taking a long time in recovery state after a server reboot?
Not really sure about this, Maybe Gila Can help me on this, However i had a similar situation once and managing the size of my log file helped a lot. (Read About VLF's).
We have set our DB for Simple recovery and do full DB backup in night 2:00 AM, Differential backup every 6 hrs. As its simple recovery no Log backup. The problem every 6-7 days the Log file size increase around 100 to 110 GB. What is the best practice to manage the growth of Log files? I usually do shrink log, is it the correct way?
Simple Problem with a simple solution:
Start taking Transaction log backups, Make a Backup plan which looks like this.
1. Full backup at 1:00 AM
2. Differential backup every 1 hour
3. Transaction Log backup every 10/5 minutes.
Taking a transaction log backup truncate you log file and the free space is available to use again.
The Log file set to auto growth of 10% and restricted to 2TB (means as good as unrestricted). Is it recommended to restrict the growth?
Set the initial file size to something more meaning full like 10 GB, and then set the auto-growth to at least 2 GB. (Again Read about VLF's). Restricting the growth of log file completely depends on your business requirements, If you can tolerate some downtime in case your log file reach maximum size then it is fine. I would rather not do it.
Hope it Helps...!!
Please mark it as answer if it resolves your query..!!
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 24, 2014 at 7:13 am
Shafat Husain (12/24/2014)
We have set our DB for Simple recovery and do full DB backup in night 2:00 AM, Differential backup every 6 hrs. As its simple recovery no Log backup. The problem every 6-7 days the Log file size increase around 100 to 110 GB. What is the best practice to manage the growth of Log files? I usually do shrink log, is it the correct way?
Simple Problem with a simple solution:
Start taking Transaction log backups, Make a Backup plan which looks like this.
1. Full backup at 1:00 AM
2. Differential backup every 1 hour
3. Transaction Log backup every 10/5 minutes.
Taking a transaction log backup truncate you log file and the free space is available to use again.
Tran log backups in Simple Recovery Model?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2014 at 7:17 am
choudhury.pradeep (12/24/2014)
What I suppose to do if I find my DB taking a long time in recovery state after a server reboot?
Well, I said earlier:
The antivirus (which as mentioned earlier should exclude database files) and the large number of VLFs you have could result in the DB being in the RECOVERING state for some time after a restart.
Given that, there are two things you can start looking at. I gave you the antivirus links and Shafat posted one about VLFs.
We have set our DB for Simple recovery and do full DB backup in night 2:00 AM, Differential backup every 6 hrs. As its simple recovery no Log backup. The problem every 6-7 days the Log file size increase around 100 to 110 GB. What is the best practice to manage the growth of Log files? I usually do shrink log, is it the correct way?
No. There's nothing wrong with a 100 GB log file.
Since you're in simple recovery, you can't do log backups. You should not be shrinking the log, you're just forcing SQL to spend time and resources re-growing the file. Set it to the size it needs to be for regular activity, plus a little margin, and leave it the hell alone. So if it's getting to 110 GB every week, I'd set the log to 125GB or 150GB and monitor, make sure it wasn't reaching the limit, if it starts using 95% of the log, then manually grow it some more.
The Log file set to auto growth of 10% and restricted to 2TB (means as good as unrestricted). Is it recommended to restrict the growth?
No, and every reputable best practices guide says not to use % growth for database files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 25, 2014 at 12:38 am
Oops...!! :crazy:
Apologies didn't paid heed to recovery model. Need full Recovery or Bulk logged.
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply