SQL Server ja transaktiologit (a.k.a. "hei miks mun levytila loppuu?")

Noin työnsä puolesta sitä ihminen joutuu katselemaan kantapalvelimia, jotka yleensä kulkevat nimikkeen "MS SQL Server" alla. Kovin usein tuote X tai tuote Y vaatii taakseen tietokannan, ja suurella innolla em. tuotteen X tahikka Y käyttöönotossa unohdetaan, että se kantapalvelin ei olekaan mikään maailman yksinkertaisin lelu. Intopinkeä säätäjä-ässä tuikkaa kantapalvelimen oletusasetuksilla levylle, ja siirtyy opettelemaan tuotteen X tahikka Y käyttöä.

Pikakelaus puoli vuotta eteenpäin, ja kantapalvelimen levytila loppuu. Asiaa tutkittaessa huomataan että levyltä löytyy 50 megatavun kantatiedosto, ja 200 gigatavun lokitiedosto. Ohhoh, kuinkas tässä nyt näin. Vaihtoehtoisesti levypinta risahtaa ja kanta menee särki, vaan ei hätää! Mehän varmistamme palvelimen varmistustuotteella Z! Hei miksei tämä toimi kun mä palautan nämä kantatiedostot levysysteemiin? Ai miten niin piti ottaa erikseen varmistukset SQL Serverin sisältä?

Ja sitten teletapit sanovat "uudestaan!" - ja asennetaan koko roska alusta. Sinne meni puoli vuotta CRM:ää tai keskustelufoorumia tai mitä nyt ikinä sen kannan varassa ajettiinkaan.

Ei, vika ei ole SQL Serverissä (vaikka sekään nyt maailman ehjin tuote ole). Vika on - uskokaa tai älkää - säätäjä-ässässä, joka kuvitteli että SQL Server on suunnilleen yhtä monimutkainen tuote kuin pasianssi tai notepad.

Recovery Model

SQL Server 2000 ja 2005 luovat uudet tietokannat oletuksena ns. Full Recovery -mallissa. Mallin tarkoitus on tarjota kannan käyttäjälle maksimaalinen palautettavuus tilanteessa jossa kanta - syystä tai toisesta - joudutaan palauttamaan esim. varmistusnauhoilta.

Full Recovery toimii lyhykäisesti seuraavalla tavalla: Kantaan (yleensä .MDF-loppuinen tiedosto) kirjoitetaan muutokset, ja kaikki muutostapahtumat logitetaan transaktiologitiedostoon (yleensä .LDF-loppuinen tiedosto). Mallia käytettäessä tärkeää on, että transaktiologit varmistetaan usein - palautettaessa proseduuri nimittäin on se, että ensin palautetaan varsinanen kantavarmistus, ja tämän jälkeen kantaan ajetaan viimeiset muutokset transaktiologien varmistuksesta. Transaktiologeista ikäänkuin "soitetaan" viimeisen kokonaisen varmistuksen jälkeiset tapahtumat kantaan uudelleen. Mitä tiheämmin transaktiologit varmistetaan, sitä lähemmäs päästään tilannetta joka oli kun kanta esim. tuhoutui tai sen rakenne hajosi vaikkapa levypinnan rikkoutumisen takia.

Transaktiologit yleensä ovat hyvin pieniä tiedostoja - mallin toisena kantavana ideana on se, että kannan "iso" varmistus ajetaan esim. kerran päivässä (tämä kuormittaa palvelinta melkolailla), ja väliaikana varmistetaan vain pientä ja kevyttä transaktiologia vaikkapa tunnin välein.

Jos uutta kantaa luodessa asiaan ei kiinnitetä huomiota millään tavalla, on lopputulos yleensä se, että transaktiologit kasvavat loputtomasti - kun niitä ei varmisteta koskaan, ei SQL Server niitä myöskään "uskalla" katkaista.

Lopulta päädytään tilanteeseen, jossa tietokanta itsessään vie esim. gigan tai kaksi levytilaa, ja transaktiologi satoja (!) gigatavuja. Tämä sitten huomataan yhtäkkiä kun koneen levytila yksinkertaisesti loppuu, ja siinä vaiheessa saattaa olla hyvinkin ikävää ryhtyä lukemaan manuaalia asian selvittämiseksi.

Vaihtoehdot

Full Recovery - mallille on vaihtoehto, Simple Recovery. Simple Recovery - mallissa kantojen transaktiologit flushataan kantaan automaattisesti SQL Serverin itsensä toimesta. Simple recovery -mallissa kannasta voidaan palauttaa VAIN se kuva, joka siitä oli viimeisen "oikean" ja "ison" varmistuksen yhteydessä - viimeisen varmistuksen jälkeistä tilannetta ei ole mahdollista palauttaa.

Kummassa mallissa kantoja sitten kannattaa pitää?

Mallin valinta tapahtuu kannan käyttöluonteen mukaisesti.

• Jos kannasta pääosin vain luetaan dataa, ja päivitykset tapahtuvat bulkkiajoina vaikkapa kerran päivässä, viikossa tai kuukaudessa, ehdottomasti Simple Recovery.
• Jos kannan avulla ajettava palvelu tai toiminto ei _oikeasti_ ole busineskriittinen - ja hyväksytään että kanta saa olla vuorokauden vanhassa tilassa palautuksen yhteydessä - jälleen Simple Recovery. Esimerkkinä vaikkapa jonkin keskustelufoorumin tai vastaavan kantana toimiminen, mitään kriittistä ei menetetä jos kanta joudutaan palauttamaan eilispäivän tilaan.
• Jos kanta on busineskriittinen ja sisältää vaikkapa päivittäin tehtyjä tilauksia tai ERP-dataa tahi sen kaltaista, ehdottomasti Full Recovery.

Ja sitten se ERITTÄIN TÄRKEÄ OSUUS:

Jos kannan katsotaan olevan niin tärkeä, että se pitää olla Full Recovery -mallissa, ON ASIOISTA HUOLEHDITTAVA ASIANMUKAISESTI, tai mitään hyötyä Full Recoverystä ei saada - jos transaktiologeja ei varmisteta, ei ole mitään mistä kantatapahtumia voitaisiin ajaa takaisin. Varmistamaton transaktiologi on täysin hyödytön - tilanne vastaa Simple Recoveryä, mutta tämän lisäksi levytilaa palaa täysin turhaan gigatavutolkulla.

Käytännössä siis:

Simple Recovery -kantoja varten luodaan varmistusjobi (tahi maintenance plan), joka varmistaa kannat vaikkapa kerran päivässä. Helppoa ja yksinkertaista. Jobissa kannattaa siivota vanhoja varmistuksia pois, luonnollisesti, mutta mitään muuta ei tarvitse tehdä, kaikki pysyy kasassa ja elämä on huoletonta.

Full Recovery - kannat varmistetaan kuten Simple Recovery - kannatkin, mutta tämän lisäksi luodaan ERILLINEN jobi, joka varmistaa transaktiologit esim. kerran tunnissa tai kahdessa. Transaktiologin varmistaminen katkaisee login, joten katastrofia ei synny.

Transaktiologien varmistustiedostot työnnetään ERI LEVYLLE kuin missä kanta on, tai mielellään jopa erillisellä toiminnolla nauhalle mahdollisimman pian. Vanhemmat transaktioiden varmistustiedostot voidaan siivota siihen ajanhetkeen asti pois, jolloin viimeinen "oikea" kantavarmistus on tehty - niitä ei siis tarvitse säilyttää kuin vuorokauden verran (sikäli kun kerran vuorokaudessa ajetaan "oikea" varmistus).

Jos nyt käytte läpi kantapalvelimianne, ja huomaatte että levyillä on massiivisen kokoisia .LDF-tiedostoja, helpoin pikatoiminta asian pelastamiseksi - ainakin hetkellisesti - on seuraava:

1. Muuta kannan recovery model -asetus asentoon "Simple".
2. Tee uusi sql query, valitse kannaksi se josta se suuri .LDF oli olemassa
3. Aja komento

dbcc shrinkfile (TIETOKANNAN_NIMI_log,10,truncateonly)

(esim. jos kanta on nimeltään "Kukka", on komento "dbcc shrinkfile (Kukka_Log,10,truncateonly)" - jos kannan nimissä on välilyöntejä, pitää kannan nimi laittaa lainausmerkkeihin. Em. komento katkaisee kannan transaktiologin väkisin 10 megatavuun, pienempääkin numeroa voi käyttää)

4. Päätä haluatko tuoda kannan takaisin full recoveryyn, jos kyllä, luo transaktiologien varmistusjobi. Jos ei, kaikki olikin tässä, muuta ei tarvitse tehdä.