Vraag Is het veilig om automatisch krimpen van SQL Server ingeschakeld te hebben?


Er zijn veel SQL Server-opties die kunnen worden ingeschakeld voor databases, en een van de meest onbegrepen opties is automatisch krimpen. Is het veilig? Zo nee, waarom niet?


42
2018-06-05 23:04


oorsprong




antwoorden:


(Oorspronkelijk stelde ik als een gewone vraag, maar toen kwam ik erachter wat de juiste methode is - bedankt BrentO)

Nee nooit.

Ik ben dit al verschillende keren tegengekomen op ServerFault en wil een aardig breed publiek bereiken met een goed advies. Als mensen fronsen op deze manier om dingen te doen, downvote en ik zal dit graag verwijderen.

Auto-shrink is een zeer algemene database-instelling die ingeschakeld is. Het lijkt een goed idee - verwijder de extra ruimte uit de database. Er zijn veel 'onvrijwillige DBA's' (denk aan TFS, SharePoint, BizTalk of gewoon oude SQL Server) die misschien niet weten dat automatisch krimpen positief slecht is.

Toen ik bij Microsoft was, was ik eigenaar van de SQL Server Storage Engine en probeerde ik de auto-shrink-functie te verwijderen, maar deze moest blijven voor compatibiliteit met eerdere versies.

Waarom is auto-shrink zo slecht?

De database zal waarschijnlijk weer gewoon groeien, dus waarom krimpen?

  1. Krimpen-groeien-krimpen-groeien veroorzaakt fragmentatie op bestandsniveau en neemt veel middelen in beslag.
  2. Je hebt geen controle over wanneer het begint (hoewel het gewoon-achtig is)
  3. Het gebruikt veel bronnen. Het verplaatsen van pagina's in de database kost CPU, veel IO en genereert veel transactielogboeken.
  4. Hier is de echte kicker: het verkleinen van gegevensbestanden (al dan niet automatisch) veroorzaakt een enorme indexfragmentatie, wat leidt tot slechte prestaties.

Ik deed een tijdje geleden een blogpost met een voorbeeld-SQL-script dat de problemen laat zien die het veroorzaakt en in een beetje meer detail uitlegt. Zien Automatisch krimpen - zet het UIT! (geen reclame of rommel zoals dat op mijn blog). Krijg dit niet in de war door het logbestand te verkleinen, wat soms nuttig en noodzakelijk is.

Dus doe jezelf een plezier - kijk in je database-instellingen en schakel automatisch krimpen uit. U zou ook om dezelfde reden niet moeten krimpen in uw onderhoudsplannen. Vertel het door aan je collega's.

Bewerken: ik zou dit moeten toevoegen, herinnerd door het tweede antwoord - er bestaat een algemene misvatting dat het onderbreken van een bewerkingsproces kan leiden tot corruptie. Nee dat zal het niet. Ik was de eigenaar van de shrink-code in SQL Server - deze rolt de huidige paginaverplaatsing die het onderbreekt, terug.

Ik hoop dat dit helpt!


67



Is er een manier om direct na een psychiater opnieuw te indexeren? - Lance Roberts
Niet opnieuw indexeren omdat dat het bestand weer zal laten groeien (bouwt een nieuwe index op voordat het de oude laat vallen), maar een reorganisatie (hetzij via mijn oude DBCC INDEXDEFRAG of de nieuwe ALTER INDEX ... REORGANISEREN) sorteert fragmentatie, ten koste van meer IO, cpu, logging ... - Paul Randal
Ik heb gemerkt dat na het verwijderen van autoshrink het geheugengebruik van de srrver hoger is. - user193655


Natuurlijk heeft Paul gelijk.

Bekijk alle DB's en hun autoshrink-instelling. Als je veel databases hebt, sluip je erin.

sp_msforeachdb  @command1 = 'Select ''[?]'',DATABASEPROPERTYEX(''?'',''IsAutoShrink'')'

Ligt dit ergens in de dmv's ... vraag ik me af.


4



sys.databases heeft een veld is_auto_shrink (en is_auto-close, is_auto_update_stats, etc) - Paul Randal
geweldig, ik ben googlen: hoe heb ik een rapport dat ons helpt te weten welke dbs zijn geconfigureerd als autoshrink en je code werkt goed en genereert goed rapport van alle db voor ons - saber tabatabaee yazdi


Het is niet "onveilig" - het zal niets beschadigen.

Maar het wordt niet aanbevolen voor productieomgevingen waar de database kan besluiten om te beginnen en een dure herschikkingsoefening te starten net voordat een stapel verzoeken komt om deze verzoeken langer uit te voeren. U bent veel beter af met het plannen van krimpregelingen samen met andere onderhoudsactiviteiten zoals back-ups (eigenlijk, na back-ups - het zal meer uit het transactielogboek komen). Of gewoon helemaal niet krimpen, tenzij er een groeiprobleem is - u kunt altijd een monitor instellen om u te laten weten wanneer de ongebruikte toegewezen ruimte groter is dan een bepaalde verhouding of vaste grootte.

IIRC de optie is standaard uitgeschakeld voor alle databases in alle MSSQL-edities, behalve Express.


2



Krimpkousen zouden niet gepland moeten worden - het zouden echt zeldzame operaties moeten zijn vanwege de problemen die ze veroorzaken. Ik begrijp uw opmerking over het doen van de derving na de back-up niet - de logboekrecords die door de krimpbewerking zijn gegenereerd, worden opgepikt door de volgende back-up van het transactielogboek, ongeacht wanneer u het doet of welke andere back-ups u neemt. Bedankt - Paul Randal


Er is een whitepaper beschikbaar op TechNet die SQL-onderhoud in meer detail uitlegt.

http://technet.microsoft.com/en-us/library/cc262731.aspx


1



Jammer genoeg is die whitepaper alleen gericht op SharePoint-installaties en bevat hij inderdaad enkele fouten. Ik heb net tijd besteed aan het lesgeven van de huidige SharePoint MCM-klasse met de auteur van de whitepaper, Bill Baer. - Paul Randal
Een goede algemene inleiding tot database-onderhoud is in mijn TechNet Magazine-artikel over het onderwerp Effectief databaseonderhoud - technet.microsoft.com/en-us/magazine/cc671165.aspx. - Paul Randal


Ik heb een SQL-server gezien met zowel Autogrow als Autoshrink ingeschakeld. Deze (relatief krachtige) server was ontzettend traag, omdat alles wat hij de hele dag deed, was krimpen en de databasebestanden groeien. Autoshrink kan nuttig zijn, maar ik zou twee dingen aanbevelen:

  1. Schakel Autoshrink standaard uit.
  2. Documenteer uw serverconfigs, zodat u weet waar Autogrow en Autoshrink zijn ingeschakeld en waar ze dat niet zijn.

1





De enige keer dat ik gedwongen werd om een ​​database te verkleinen, was om een ​​kopie op een testserver te vernieuwen met minder schijfruimte (onvoldoende om de productiedatabase vast te houden).

Het bestand (de bestanden) van de productiedatabase had een royale vrije ruimte, helaas moet je een database herstellen met dezelfde bestandsgroottes als waarmee je een back-up hebt gemaakt. Dus had geen andere keus dan de productie te verkleinen voordat ze een back-up maakte. (De drogisterij duurde eeuwen, er werd veel bronnen verbruikt en de daaropvolgende transactieloggroei was problematisch.)


1





Bekijk ook deze video-tutorial ....

Kijk hoe Paul Randal demonstreert hoe krimpen en automatisch verkleinen ernstige fragmentatieproblemen kan veroorzaken voor uw database http://wtv.watchtechvideos.com/topic194.html


1