Problem sa Transaction log fajlom

Na SQL serveru 2008 imam bazu na kojoj sam zbog nekih problema ukljucio neki trace
i nakon toga mi je ldf fajl narastao do 70Gb.
Sada je trace iskljucen ali ne mogu da smanjim ldf file.
Standardno sam to ranije radio sa BACKUP LOG i onda DBCC SHRINKFILE... ali sad to ne sljaka..
DBCC LOGINFO mi ispise mnoooogo redova koji imaju status 2 pa pretpostavljam da su oni problem i da ne daju da shrinkujem log fajl.

Pokusao sam da atacujem samo MDF (sa kreiranjem baze pod drugim imenom i poturanjem ovog MDF-a) ali fuckin' Sql Server 2008 ne dozvoljava da se pise
u sysdatabases a to mi je neophodno da bi bazu doveo u emergency mode i da bi mogao da odradim ostatak procedure kreiranja nedostajuceg LDF fajla

Da li neko ima iskustva sa ovim?

Ovo me rastura vec nedelju dana i resenja nema ni na pomolu... :-(

HELP!

Pozdrav.

Te 2-ke znace samo da ti VLFovi nisu baceni u backup i da su aktivni. Dakle da, te 2-ke sprecavaju shrink log-a.

Nisi napisao, ali pretpostavljam da si koristio BACKUP LOG <baza> WITH NO_LOG (ili TRUNCATE_ONLY). To vise ne radi u SQL2008, izbaceno je, a logika je da prekida backup chain (sto je istina) i da su podaci nezasticeni do prvog sledeceg full backup-a (sto je takodje tacno) sto konkretno cini full recovery mode nesuvislim i time efektivno redukujes recovery model u simple. Ako ti ne treba full recovery mode i ako ti full log recovery predstavlja nepotrebni teret onda bazu treba da drzis u simple recovery modu i LOG ce se sam trunc-ovati, jednostavno ti vise ne dozvoljavaju da mesas dva koncepta, ili je full ili je simple Nista strasno, samo nas MS tretira kao dobrile sa wordom, i stiti baze od nas samih, naviknes se posle nekog vremena.

Za tvoj konkretan problem moras da prebacis bazu u simple recovery i da uradis checkpoint, to ce efektivno umlatiti log file. Narafski, gubis sve informacije iz log file-a ali kontam da si to vec otpisao, ali gledaj da uradis full backup posle ovoga da ne bi pogubio nesto od podataka.




Druga varijanta je da naravno napravis pravi pravcati log backup (sto ce VLFove prebaciti u status 0/inactive) ali ti onda treba 70gb prostora negde da ga ubacis.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Pa vidi, ni ova stvar sa CHECKPOINT ne radi nista... ne znam kako ali tako je :-(



Da li se ovo radi sa BACKUP LOG TO DISK=... ???
Ili kako vec?

Vidi sto se tice loga bas mi je nebitan jer imamo podatke u XML fajlovima kojima mogu da rekonstruisem koliko god mi podataka treba unazad, ako bas do toga dodje da zatreba.
Da te ne smaram sad kako to radim jer nije ni bitno...

A ako te ne mrzi da mi samo kazes kako da uradim ovu drugu varijantu

Pozdrav.

Jesi uradio shrink log-a posle onog koda koji sam ti dao. Taj kod ce samo da dumpuje istorijske transakcije iz loga i da deaktivira istorijske VLF-ove (ako uradis DBCC LOGINFO sad ce svi sem jednog VLFa biti inactive tj status=0). Ali ce poslednji aktvni VLF i dalje biti status=2 i fajl se nece sam smanjiti, moras da ga shrinkujes da bi SQL pomerio taj VFL na fizicki pocetak LOG fajla i truncovao ostatak iz file sistema.

U svakom slucaju druga varijanta je

backup log <baza> to DISK='c:\log.bak'

ali ako si vec uradio gornju skriptu tebi je aktivni deo loga vec prazan i dobices log.bak koji ima samo overhead.

btw, baza mora da ti je online. U kom stanju je baza?

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

uradio deo koda koji si poslao sa CHECKPOINT
pa onda


pa mi LOGINFO opet daje mali milion redova sa statusom 2

baza je online...

Ne znam da li me kolje to sto se u trenutku dok ovo radim koristi od strane klijenata...

Ako je to problem onda cu da odem i da skinem komp sa mreze da pobijem konekcije i da odradim...

Da bi aktivni korisnici bili problem mora da postoji transakcija koja je jos uvek ziva od "onomad" (nedelju dana?) i da spanuje sve ove VLFove. Dok god je ona ziva nema deaktiviranja VLFova koje obuhvata cak iako u tim VLFovima nema instrukcija vezanih za tu specificniu transakciju. Ne mora da bude ta transakcija kojom si ti nadrukao log, moze da bude bilo koja, al mi je malo neverovatno da jedna transakcija opstaje 7 dana koliko ti imas problem.

Probaj da skines korisnike sa baze (najbolje ako mozes da je prebacis u singleuser mode) i onda probaj da ga prebacis u simple recovery i uradis checkpoint. Javi ako to ne uspe jer onda verovatno imas neki ozbiljniji problem. Checkpoint u simple modu MORA da dumpuje zatvorene istorijske transakcije a u single user modu nece biti drugih procesa sem tvoje query console, samim tim nema ni tekucih transakcija.


PS: Da nisi ti sam ostavio otvorenu transakciju u nekom query prozoru? Ako nisi gasio SQL server menadzera od onda svi tvoji otvoreni procesi su u wait-u i ako si otvorio transakciju ista je jos uvek aktivna cak iako ne drzi lockove. Proveri...

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Probaj ovu skriptu:



trebalo bi da ti izlista sve aktivne transakcije po "starini". Vidi sta se tu krije i dal ti nesto drzi log, obrati paznju na HoursOld kolonu. Adaptirao sam neku skriptu koju sam nasao na net-u za lov na aktivne lockove (al tebi trebaju samo transakcije, bez lockova pa sam izbacio deo koji ti ne treba).

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Tjah...
uradio i sve su mi transakcije od danas (Hour = 0)...
svaka ima begin time najvise minut pre izvrsenja ovog upita i svaki sledeci put smenjuju se nove transakcije...
Uzgred, izbacio sam ovaj CROSS APPLY jer me zeza neka greska za '.' incorect syntacs... ali i pored toga ne vidim taj view dm_exec_sql_text, a mislim da je bez toga ok

pa sad cu da bacim MDF i LDF na drugi server pa tamo da uradim ovo u single modu sto si mi rekao.

A da ne znas mozda kako bi mogao da se attachuje single file (samo MDF)... ?
Da bi to uradio moram da "varam" sql server i da pisem u sysdatabases ali to mi ne dozvoljava.. tacnije treba u status da upisem -32876 i tako da bazu stavim u Emergency mode
pa onda da uradim rebuild loga...
probao sam sa db_reconfigure 'allow updates, 1 ali mi ne daje da pisem jer je valjda polje kompleksno ili tako nesto (pretpostavljam da je rezultat funkcije i sl)

Pa tim putem ti je najjednostavnije da detachujes bazu i onda da attachujes samo mdf (ne mozes da uradis attach pre nego detach jer ce puci sa greskom)

dakle, iskoristi procedure sp_detach_db i sp_attach_single_file_db.

Medjutim, to ce "ugasiti" tvoju produkcionu bazu dok ne obavis attach nazad, u principu bilo koja varjanta da ti dodjes do low level mdf fajla i da nad njim radis operacije podrazumeva izbacivanje korisnika iz opticaja.

btw, ta skripta sa 32768 flegom je bajata (za sql2000). Koristi ovu ( za nju ti ni ne treba allow updates).



Tebi ja mislim da ne prolazi prebacivanje u emergency mode generalno zato sto imas aktivne korisnike. gornji alter database ce uci u wait state i blokirace nove konekcije dok se useri ne razjure i onda ce prebaciti bazu u emergency mode kad se broj konekcija spusti na tu jednu u kojoj je alter.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Jedno glupo pitanje, jesi restartovao SLQ server nakon sto si ukljucio pa iskljucio trace?

razmisljam u pravcu da mozda nije ostala aktivna neka sistemska transkacija nad bazom, one se ne vide kroz skriptu. Ako nisi, probaj da restartujes server i onda ponovo probaj sa simple recovery skriptom.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

:-)))

jesam, restartovan je server ali nece onaj checkpoint pa SHRINKFILE nista da uradi...

... sad cu da probam da bacim MDF na test server pa da probam da ga atachujem bez log fajla... sa onim storkama koje si mi ispisao
pa ti se javljam

pozz

Probao sam:

i ispisao je:

Jesi ti siguran da ti baza nije u "suspect" modu umesto u online? sql2005 nema ikonicu za to, moras da pokrenes sp_helpdb da vidis.

Sad sam tek jos ubedjeniji da imas nezatvorenu transakciju u log fajlu, nesto se definitivno desilo nepredvidjeno, ako transakcija jos nije ziva onda je nasilno prekinuta i nije nikad zatvorena i automatski recovery nije odradio posao i baza ti je sad u suspect modu. Nazalost, ako je to situacija moraces u puni rollback nezatvorenih transakcija u produkciji. Sta god da radis, NEMOJ da detachujes suspect bazu, ako je ostecenje veliko neces moci da je attachujes opet sem nekim nesigurnim hack-om, secam se neke price sa bloga da je to veliki no-no.

Dakle, ako ti je baza u suspect modu ide sledeci skript, ovo sam svojevremeno pokupio sa nekog sajta, probaj, ali pazi, ovo ide u rollback svih nezatvorenih transakcija i ispravljanje allocation gresaka u samom mdf-u (rollback immediate + allow data loss), rekao si da ti to nije problem, ali da znas za svaki slucaj. Definitivno ces morati da privremeno razjuris sve korisnike sa baze, niko ne moze da bude zakacen dok se ovo desava (set emergency pa single_user ce se pobrinuti za to)



posle ovoga restartuj SQL i proveri opet da li je baza u suspect modu. Ako vise nije onda mozes da primenis onaj trick sa simple recovery + shrink skriptom da se konacno resis VLFova i log fajla.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Hm.. koliko se secam postojao je problem pre nekih mesec dana sa porukama kod klijenta nesto tipa:
"Transaction deadlock... Proces ID (XX)... deadlock victim... bla, bla, bla..." ne secam se tacno...
Uglavnom problem je resen tako sto je restartovan server na kome je web servis koji je pravio ove lockove i to zato sto se
zaglupeo da je svaka akcija odradjivana mnooogo dugo (memorija i procesor bili na 100%)
Od tada se deadlock javi ponekad... ali verujem da su te transakcije u logu od tada...

Ok, onda cekam jos malo pa akcija...
Pozitivna je stvar u tome sto je takav sistem da se baza moze lako rekonstruisati :-)
To je cak klijent jednom sam uradio jer je obrisao podatke za nekoliko dana unazad na toj bazi a nije trebalo to da uradi :-)

Uzgred:

deadlock ne moze da izazove suspect mode, deadlock je samo indikator lose napravljenog programa/sistema. kad nastupi deadlock, victim ide u rollback i to uredno udje u transaction log i pukne ti aplikaciju ali baza ostaje konzistenta. Suspect mode nastaje kad se desi nesto bas gadno, tipa los sektor, ili nestanak struje usred zapisa na starijim diskovima, itd, itd, znaci nesto van kontrole samog SQL servera, ali po ispisu koji si okacio nisi u suspect modu, a i dalje mislim da imas nezatvorenu transakciju u log fajlu, a vec se nalazis u simple recovery modu.

Ne znam sta da ti kazem vise, ovakvo nesto jos ni ja nisam video, probaj sa gornjom rollback immediate skriptom, ako to ne pomogne onda ne znam sta bih ti jos ponudio sem da dropujes bazu i kreiras novu ispocetka ako vec mozes da vratis podatke iz drugih izvora.

U stvari, ako nisi u suspect modu onda mozes slobodno da detachujes bazu. Dakle mozes da probas varijantu sa sp_detach_db i sp_attach_single_file_db, naravno ukloni aktivne korisnike prvo.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Uh... proslo je...

sp_detach_db pa sam sklonio LDF fajl, a onda sp_attach_single_file_db i napravio je sam LDF...

Hvala ti mmix na odvojenom vremenu i na zalaganju za "moju stvar"...
sta da ti kazem, ako zatreba nesto (treci za preferans, radnik na mesalici... ) nemoj se ustrucavati... tu sam ;-)

Pozdrav!