Am'm nu un SQL expert, și am'm amintit de fiecare dată când trebuie să fac ceva dincolo de elementele de bază. Am o baza de date de test care nu este mare în dimensiune, dar jurnalul de tranzacții cu siguranta este. Cum pot goli jurnalul de tranzacții?
Face un fișier jurnal mai mici ar trebui să fie rezervate pentru scenarii în cazul în care este întâmpinat de creștere care nu se aștepta să se întâmple din nou. Dacă fișierul jurnal va crește la aceeași dimensiune, din nou, nu foarte mult este realizată în scădere temporar. Acum, în funcție de obiectivele de recuperare de date, acestea sunt acțiunile pe care le ar trebui să ia.
Nu face orice modificări la baza de date fără a se asigura, puteți restaura ar trebui ceva merge prost.
(Și cu punct-în-timp de recuperare, adică îți pasă de a fi capabil de a restabili și la altceva decât la un plin sau rezervă diferențială.) Probabil baza de date este în "COMPLET" modul de recuperare. Dacă nu, atunci asigurați-vă că este:
ALTER DATABASE testdb SET RECOVERY FULL;
Chiar dacă luați regulat backup-uri complete, fișierul jurnal va crește și crește până când efectuați o log backup - asta e pentru protecția ta, nu inutil să mănânce departe la spațiu pe disc. Tu ar trebui să fie efectuarea acestor log backup-uri destul de frecvent, în funcție de recuperare a obiectivelor. De exemplu, dacă aveți o regulă de afaceri care statele vă puteți permite să piardă mai mult de 15 minute de date în caz de dezastru, ar trebui să aveți un loc de muncă care susține jurnalul fiecare 15 minute. Aici este un script care va genera timestamped nume de fișiere în funcție de ora curentă (dar puteți, de asemenea, face acest lucru cu planuri de întreținere etc., doar don't alege oricare dintre psihiatru opțiuni în planuri de întreținere, au're groaznic).
DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
+ '.trn';
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;
Rețineți că \\backup_share\
ar trebui să fie pe o altă mașină, care reprezintă un alt suport de stocare a dispozitivului. Susținerea acestor până la aceeași mașină (sau la o altă mașină care folosește același suport, discuri, sau un alt NEG ca's pe același fizice gazdă) nu prea ajuta, deoarece dacă mașina explodează, ai'am pierdut baza de date și backup sale. În funcție de infrastructura de rețea se poate face mai mult sens să copie de rezervă la nivel local și apoi a le transfera la o altă locație în spatele scenei; în orice caz, doriți să obțineți-le pe primar mașină bază de date cât mai repede posibil.
Acum, odată ce ați regulat copii de rezervă jurnal de funcționare, ar trebui să fie rezonabile pentru a micsora fișier jurnal pentru ceva mai rezonabil decât orice's sufletul la gură până acum. Acest lucru nu nu spun funcționare SHRINKFILE
peste si peste din nou, până când fișierul jurnal este de 1 MB - chiar dacă sunt backup log frecvent, mai are nevoie pentru a se potrivi sumă de orice tranzacții simultane care pot avea loc. Fișier jurnal autogrow evenimente sunt scumpe, deoarece SQL Server are la zero fișierele (spre deosebire de fișiere de date atunci când inițializarea instantanee a fișierelor este activat), și tranzacții de utilizator trebuie să așteptați în timp ce acest lucru se întâmplă. Vrei pentru a face acest lucru crește-psihiatru-cresc-psihiatru de rutină cât mai puțin posibil, și cu siguranță nu't vreau pentru a face utilizatorii să plătească pentru asta.
Rețineți că poate fi necesar să conectați două ori înainte de a o micsora este posibil (datorită Robert).
Deci, aveți nevoie pentru a veni cu o dimensiunea practică pentru fișierul jurnal. Nimeni de aici nu poate spune ce este, fără să știe mult mai multe despre sistemul tau, dar daca'am fost frecvent în scădere fișierul jurnal și a fost în creștere din nou, un bun filigran este, probabil, 10-50% mai mare decât cel mai mare l's a fost. Las's spun că vine de la 200 MB, iar tu vrei ulterioare autocreștere evenimente să fie de 50 MB, atunci puteți ajusta dimensiunea fișierului jurnal astfel:
USE [master];
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO
Rețineți că, dacă fișierul jurnal este prezent > 200 MB, ar putea fi necesar pentru a rula această primă:
USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO
Dacă aceasta este o bază de date de testare, și nu't grijă despre point-in-time de recuperare, atunci ar trebui să asigurați-vă că baza de date este în "SIMPLU" modul de recuperare.
ALTER DATABASE testdb SET RECOVERY SIMPLE;
Punerea în baza de date "SIMPLU" modul de recuperare va asigurați-vă că SQL Server re-utilizează porțiuni din fișierul jurnal (în esență, eliminarea treptată a tranzacțiilor inactive) în loc de creștere pentru a ține o evidență a toate tranzacții (cum ar fi "COMPLET" de recuperare nu până te-ai întors log). CONTROL
evenimente va ajuta la controlul jurnal și asigurați-vă că acesta nu't trebuie să crească dacă nu genera o mulțime de t-jurnal de activitate între `CONTROL.
Apoi, ar trebui să facă absolut sigur că acest jurnal de creștere a fost cu adevărat din cauza unui eveniment anormal (de exemplu, un raport anual de curățenie de primăvară sau reconstruirea cei mai mari indici), și nu din cauza normală, de zi cu zi de utilizare. Daca psihiatru fișierul jurnal pentru a o ridicol de mici dimensiuni, și SQL Server are doar să-l crească din nou pentru a găzdui activitatea normala, ce ai câștigat? Ai fost în stare să facă uz de acest spațiu pe disc te-a eliberat doar temporar? Dacă aveți nevoie de o soluție imediată, atunci puteți executa următoarele:
USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO
În caz contrar, setați o dimensiune corespunzătoare și rata de creștere. Ca pe exemplul de la punctul-în-timp de recuperare caz, puteți utiliza același cod și logica pentru a determina ce dimensiune este corespunzătoare și a stabilit rezonabil autocreștere parametri.
opțiune și apoi
SHRINKFILE`. Pentru unul, acest TRUNCATE_ONLY
opțiune a fost depreciat și nu mai este disponibil în versiunile curente ale SQL Server. Al doilea, dacă sunteți în PLIN
de recuperare model, acest lucru va distruge lanț și necesită o nouă copie de rezervă completă. DBCC SHRINKDATABASE
și planul de întreținere opțiunea de a face același lucru, sunt idei proaste, mai ales dacă aveți într-adevăr nevoie pentru a rezolva un jurnal problema. Țintă fișierul pe care doriți să reglați și să-l adapteze în mod independent, folosind DBCC SHRINKFILE " sau " ALTER DATABASE ... MODIFICA FIȘIERUL
(exemplele de mai sus). ALTER DATABASE
), acest lucru va duce doar la mai multe inutile creștere evenimente, ca jurnalul are pentru a permite tranzacțiile curente, indiferent de modelul de recuperare. Ceea ce este punctul de a elibera acest spațiu temporar, doar ca SQL Server poate lua înapoi încet și dureros? În loc de scădere fișierul jurnal pentru unele cantitate mică și lasă-l în mod constant autogrow la o rată mică pe cont propriu, setați-l la un mod rezonabil de mari dimensiuni (cea care va găzdui suma mai mare set de tranzacții simultane) și a stabilit un termen rezonabil autogrow setare ca o rezervă, astfel încât să nu - 't trebuie să crească de mai multe ori pentru a satisface singure tranzacții și astfel încât acesta va fi relativ rare, pentru că va trebui să crească în condiții normale a operațiunilor de afaceri. Cel mai rău posibil setările de aici sunt de 1 MB de creștere sau creștere de 10%. Destul de amuzant, acestea sunt implicite pentru SQL Server (pe care am'am plâns și intrebat pentru ca modificările să nu avail) - 1 MB pentru fișierele de date, și de 10% pentru fișierele jurnal. Fostul este mult prea mic în această zi și de vârstă, și cei din urmă duce la mai mult și mai mult evenimentele de fiecare dată (de exemplu, fișierul jurnal este de 500 MB, prima creștere este de 50 MB, următoarea creștere este de 55 MB, următoarea creștere este 60.5 MB, etc. etc. - și pe lent I/O, crede-mă, vei observa această curbă).
Vă rugăm să don't opri aici; în timp ce mai mult de sfatul vezi acolo despre micșorarea fișierelor jurnal este în mod inerent rău și chiar dezastruoase, sunt unii oameni carora le pasa mai mult de integritate a datelor decât eliberând spațiu pe disc. Un post pe blog am scris în 2009, când am văzut câteva "aici's cum pentru a micsora fișier jurnal" mesaje de primavara up. Un post pe blog Brent Ozar scris în urmă cu patru ani, arătând spre mai multe resurse, ca răspuns la un SQL Server articol de Revistă, care ar trebui să nu au fost published. Un post pe blog de Paul Randal explica de ce t-log de întreținere este important și de ce nu ar trebui't psihiatru fișierele de date, fie. Mike Walsh are un mare răspuns care acoperă unele dintre aceste aspecte, inclusiv motive de ce nu ar putea fi capabil de a micsora fișierul jurnal immediately.
-- DON'T FORGET TO BACKUP THE DB :D (Check [here][1])
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
De la: DBCC SHRINKFILE (Transact-SQL)
Poate doriți să copie de rezervă în primul rând.
DISCLAIMER: vă Rugăm să citiți comentariile de mai jos cu atenție, și presupun că'am citit deja răspunsul acceptat. Așa cum am spus de aproape 5 ani în urmă:
dacă cineva are orice comentarii pentru a adăuga pentru situațiile când acest lucru NU este o adecvat sau o soluție optimă, atunci vă rugăm să comentariu de mai jos
Click dreapta pe numele bazei de date.
Selectați Sarcini → Psihiatru → Baza De Date
Apoi faceți clic pe OK!
Eu, de obicei, deschideți Windows Explorer director care conține fișiere de baze de date, așa că am putea vedea imediat efectul.
De fapt, am fost destul de surprins de acest lucrat! În mod normal, m-am'am folosit DBCC înainte, dar am încercat asta și nu't psihiatru nimic, așa că am încercat GUI (2005) și a lucrat mare - eliberarea 17 GB in 10 secunde
În modul de recuperare Completă asta s-ar putea să nu funcționeze, așa că trebuie să fie înapoi până la primul jurnal, sau schimba Simple de recuperare, apoi micsora fișier. [multumesc @onupdatecascade pentru asta]
--
PS: apreciez ceea ce unii au comentat cu privire la pericolele de acest lucru, dar, în mediul meu am n't au orice probleme de a face acest lucru singur, mai ales de când am face întotdeauna o copie de siguranță completă în primul rând. Deci, vă rugăm să ia în considerare ceea ce mediul este, și modul în care aceasta afectează strategie de rezervă și siguranța locului de muncă înainte de a continua. Tot ce am făcut a fost îndreptat pe oameni să o caracteristică oferită de Microsoft!
Mai jos este un script pentru a micsora jurnal de tranzacții, dar cu siguranță aș recomanda copierea de rezervă a jurnalului de tranzacții înainte de a-l micșoreze.
Daca ai micsora fișier aveți de gând să-și piardă o mulțime de date care ar putea veni ca un economizor de viață în caz de dezastru. Jurnalul de tranzacții conține o mulțime de date utile care pot fi citite cu ajutorul unui terț tranzacție jurnal de cititor (poate fi citit manual, dar cu mare efort, deși).
Jurnalul de tranzacții este, de asemenea, o necesitate atunci când vine vorba de recuperarea punct în timp, deci, nu doar sa-l arunc, dar asigurați-vă înapoi în prealabil.
Aici sunt mai multe posturi unde oamenii au folosit datele stocate în jurnalul de tranzacții pentru a realiza recuperare:
USE DATABASE_NAME;
GO
ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);
ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO
S-ar putea obține o eroare care arata ca acest lucru atunci când execută comenzi de mai sus
"nu Poate micsora fișier jurnal (log file name), deoarece logic jurnal de fișiere situat la sfârșitul fișierul este în uz"
Acest lucru înseamnă că TLOG este în uz. În acest caz, încercați executare acest lucru de mai multe ori într-un rând sau a găsi o modalitate de a reduce baza de date de activități.
Aici este un joc simplu si foarte neelegant & potențial periculoase cale.
Am'm ghicitul că nu faci backup log. (Care trunchia log). Sfatul meu este de a schimba modelul de recuperare de la full la simplu. Acest lucru va preveni log umfla.
Dacă nu utilizați jurnalele de tranzacție pentru restabilește (de exemplu, Tu doar faci full backup), puteți seta Modul de Recuperare pentru a "Simplu", și jurnalul de tranzacții în curând va micsora si nu umple din nou.
Dacă utilizați SQL 7 sau 2000, aveți posibilitatea să activați "trunchia jurnal pe punctul de control" în baza de date fila opțiuni. Acest lucru are același efect.
Acest lucru nu este recomandat în mediile de producție, evident, deoarece nu va fi capabil de a restabili la un moment dat în timp.
SQL Server jurnal de tranzacții trebuie să fie întreținute în mod corespunzător, în scopul de a preveni nedorite de creștere. Acest lucru înseamnă de funcționare tranzacție jurnal de backup-uri destul de des. De nu faci asta, riști jurnalul de tranzacții pentru a deveni complet și începe să crească.
În afară de răspunsuri pentru această întrebare recomand citirea și înțelegerea jurnalul de tranzacții comune mituri. Aceste date pot ajuta la înțelegerea jurnalul de tranzacții și de a decide ce tehnici să folosească pentru a "clear" ea:
La 10 cel mai important jurnal de tranzacții SQL Server mituri:
Mit: My SQL Server este prea ocupat. Nu vreau sa fac jurnal de tranzacții SQL Server backup
Una dintre cele mai mari performanțe operațiuni intensive în SQL Server este un auto-cresc eveniment online de fișier jurnal de tranzacții. De a nu face tranzacție jurnal de backup-uri destul de des, on-line jurnal de tranzacții va deveni plin și va trebui să crească. Implicit creșterea dimensiunea este de 10%. Cel mai ocupat de baza de date este, cu atât mai repede on-line jurnal de tranzacții va crește dacă tranzacție jurnal de backup nu sunt create Crearea unui jurnal de tranzacții SQL Server backup nu a bloca online jurnal de tranzacții, dar o auto-creștere evenimentului. Se poate bloca orice activitate în jurnal de tranzacții on-line
La jurnal de Tranzacții mituri:
Mit: Regular jurnal în scădere este o întreținere bună practică
FALS. Jurnal de creștere este foarte scump pentru cea mai nouă bucată trebuie să fie adus la zero-out. Scrie toate se oprește activitatea pe care baza de date până la zero nu este terminat, și dacă-ți disc de scriere este lent sau autocreștere dimensiunea este mare, această pauză poate fi foarte mare și utilizatorii vor observa. Asta e un motiv pentru care doriți, pentru a evita creșterea economică. Daca ai micsora jurnal, acesta va crește din nou și vă sunt doar pierdem disc funcționare pe andrelele psihiatru și să crească din nou jocul
Cele mai multe răspunsuri aici, atât de departe sunt presupunând că nu aveți nevoie de fapt fișierul Jurnal de Tranzacții, cu toate acestea, dacă baza de date este utilizarea "COMPLET" model de recuperare, și doriți să păstrați copii de rezervă în cazul în care aveți nevoie pentru a restabili baza de date, apoi nu trunchia sau șterge fișierul jurnal cum multe dintre aceste răspunsuri sugerează.
Eliminarea fișierul jurnal (prin trunchierea el, aruncând-o, ștergeți-l, etc) va rupe lanț de rezervă, și vă va împiedica de la restabilirea la orice moment în timp de la ultima ta complet, diferențial, sau tranzacție jurnal de rezervă, până la următoarea integrală sau de backup diferențial se face.
De articol Microsoft pe'BACKUP`
vă recomandăm să nu folosiți niciodată NO_LOG sau TRUNCATE_ONLY manual trunchiați fișierul jurnal de tranzacții, pentru că asta rupe lant. Până următor integral sau diferențial de backup de date, baza de date nu este protejat de mass-media eșec. Manual de utilizare jurnal de trunchiere în foarte circumstanțe speciale, și de a crea copii de siguranță ale datelor imediat.
Pentru a evita asta, de backup fișier jurnal disc înainte de a-l micșoreze. Sintaxa ar arata ceva de genul asta:
BACKUP LOG MyDatabaseName
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'
DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)
Aceasta tehnica care John recomandă nu este recomandată, deoarece nu există nici o garanție că baza de date va atașa fără fișierul jurnal. Schimba baza de date de la plin la simplu, vigoare un punct de control și așteptați câteva minute. SQL Server va șterge jurnal, care apoi puteți micșora folosind DBCC SHRINKFILE.
Folosi DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY)
comanda. Dacă aceasta este o bază de date de testare și vă sunt încercarea de a salva/recupera spațiu, acest lucru va ajuta.
Amintiți-vă însă că TX jurnalele au un fel de minim/starea de echilibru dimensiune care acestea vor crește până la. În funcție de modelul de recuperare este posibil să nu fi capabil de a micsora jurnal - în cazul în totalitate și nu't emiterea TX backup log log poate't fi micșorat - va crește pentru totdeauna. Dacă tu nu't nevoie TX log backup-uri, schimba modelul de recuperare a Simplu.
Și amintiți-vă, niciodată, în orice circumstanțe șterge jurnal (LDF) fișier! Va trebui instant de corupție de date. Fierte! Done! Datele pierdute! Dacă stânga "nereparate" principalele fișier MDF ar putea deveni corupt permanent.
Niciodată șterge jurnalul de tranzacții - veți pierde datele! O parte din datele dumneavoastră este în TX Log (indiferent de modelul de recuperare)... dacă te detașezi și "redenumiți" TX fișier jurnal care în mod eficient șterge parte din baza dumneavoastră de date.
Pentru cei care au eliminat TX Jurnal poate doriți să rulați câteva checkdb comenzi și repara erorile de corupție, înainte de a pierde mai multe date.
Check out Paul Randal's blog pe acest subiect, sfaturi proaste.
De asemenea, în general, nu folosesc shrinkfile pe MDF fișiere după cum se poate grav fragment datele. Check out Sfaturi Proaste secțiunea pentru mai multe informații ("de Ce nu ar trebui să micșoreze fișierele de date")
Check out Paul's site - ul- el se referă la aceste întrebări. Luna trecută, el a umblat prin multe dintre aceste probleme în Mit-O Zi serie.
Verificați mai întâi baza de date model de recuperare. În mod implicit, SQL Server Express Edition creează o bază de date pentru recuperare simplu modelul (daca nu ma insel).
Jurnal de rezervă DatabaseName Cu Truncate_Only:
DBCC ShrinkFile(yourLogical_LogFileName, 50)
SP_helpfile va da logic numele fișierului jurnal.
Se referă la:
A recupera de la un complet jurnal de tranzacții într-o bază de date SQL Server
Dacă baza de date este în Plină Recuperare Model și dacă nu iei TL backup, apoi schimba-l la SIMPLU.
La experiența mea pe cele mai multe Servere SQL nu există nici o copie de rezervă a jurnalului de tranzacții. Backup-uri complete sau diferențial sunt o practică comună, dar tranzacție jurnal de backup-uri sunt într-adevăr rar. Deci, fișierul jurnal de tranzacții crește la infinit (până când discul este plin). În acest caz model de recuperare ar trebui să fie setat la "simplu". Don't uita pentru a modifica sistemul de baze de date "model" și "tempdb", de asemenea.
O copie de rezervă a bazei de date "tempdb" nu are nici un sens, astfel încât modelul de recuperare de acest db ar trebui să fie întotdeauna "simplu".
Să Trunchiați fișierul jurnal:
Pentru a Micsora fișier jurnal:
Folosind Enterprise manager :- Click dreapta pe baza de date, Toate sarcinile, Psihiatru de baze de date, Fișiere, Selectați fișierul jurnal, OK.
Folosind T-SQL :- Dbcc Shrinkfile ([Log_Logical_Name])
Puteți găsi numele logic al fișierului jurnal de funcționare sp_helpdb sau prin căutarea în proprietățile de bază de date în Enterprise Manager.
Baza de date → click dreapta Proprietăți → fișier → adăugați un alt jurnal fișier cu un nume diferit și setați calea la fel ca vechiul jurnal fișier cu un alt nume de fișier.
Baza de date preia automat nou creat fișierul jurnal.
Acest lucru va lucra, dar este sugerat pentru a lua de rezervă a bazei de date în primul rând.
S-a întâmplat cu mine în cazul în care baza de date fișier jurnal a fost de 28 de GBs.
Ce puteți face pentru a reduce acest lucru? De fapt, fișierele jurnal sunt cei fișier de date care SQL server păstrează atunci când o tranzacție a avut loc. Pentru o tranzacție la procesul de SQL server alocă pagini pentru același. Dar, după finalizarea tranzacției, acestea nu sunt eliberate dintr-o dată, în speranța că mai poate fi o tranzacție vine ca una la fel. Aceasta deține spațiul.
Pasul 1: În primul rând Rula această comandă în interogare a bazei de date de explorat punct de control
Pasul 2: Click dreapta pe baza de date Sarcina> Înapoi Selectați spate sus tip de Tranzacție Jurnal Adăugați o adresă de destinație și numele de fișier pentru a păstra datele de backup (.bak)
Repetați acest pas din nou și în acest moment dea un alt nume de fișier
Pasul 3: Acum du-te la baza de date Faceți clic dreapta pe baza de date
Sarcini> Micsoreaza> Fișiere Alegeți Fișier de tip Log Psihiatrul acțiune ca a elibera spațiu nefolosit
Pasul 4:
Verificați fișierul jurnal în mod normal, în SQL 2014 acest lucru poate fi găsit la
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014EXPRESS\MSSQL\DATA
In cazul meu, sa redus de la 28 GB pentru 1 MB
Unele dintre alte răspunsuri nu au de lucru pentru mine: nu A fost posibil să se creeze un punct de control în timp ce pb a fost on-line, pentru jurnalul de tranzacții a fost plin (ce ironie). Cu toate acestea, după setarea bazei de date la modul de urgență, am fost capabil de a micsora fișier jurnal:
alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);