Håll koll på data - och loggfiler
Förord
Hantera dina data- och loggfiler på ett smidigt sätt i T-SQL. Nedan ser du hur man kan göra.
Att växa eller att inte växa
SQL Server lagrar all data, index och även transaktionsloggen i filer på hårddisken. När man skapar en ny databas (eller nya filer till en befintilig databas) så bestämmer man hur stora de ska vara inledningsvis. Standardinställningen i SQL Server är 1 MB (vill man ändra denna och andra standardinställningar kan man ändra på model-databasen eftersom nya databaser skapas genom att ta en kopia på denna), vilket naturligtvis är tillräckligt för en helt nyskapad databas men ganska snabbt kommer att bli fullt. Samtidigt konfigurerar man om SQL Server ska automatiskt hantera storleken på dem och i så fall hur detta ska ske. Om man vill att SQL Server ska förstora filerna automatiskt finns det två parametrar man kan skruva på.Storlek att förstora
Man kan antingen välja att förstora filerna ett visst antal MB eller att förstora dem ett antal % från nuvarande storlek. Standardinställningen är att de växer med 10%. Det innebär att om en fil är 10 MB stor och behöver utökas så lägger man till ytterligare 10% utrymme till den, så att den efteråt blir 11 MB. Det kan dock vara lite farligt att ha denna inställning, framförallt om man har väldigt stora datafiler. Har man ex. en datafil som är 1 GB stor och den behöver växa så innebär det att SQL Server ska skapa och allokera 100 MB till den, en operation som kan ta lite tid. Då är det bättre att ställa in en viss storlek som filerna ska växa med, 10 MB kanske kan vara lämpligt i detta fall. Exakt vad som är lämpligt är förstås olika från en situation till en annan.
Maxstorlek
Man ska även konfigurera om filerna ska få växa obehindrat eller om det ska finnas en gräns för hur stora de får bli. Vid det första alternativet riskerar man att få slut på hårddiskplats, vid det senare kan man hamna i en situation där en fil behöver växa men inte får lov att göra det eftersom maxgränsen är nådd. Många webbhotell och liknande där flera användare delar på en SQL Server-installation utnyttjar detta till att ransonera hur mycket data man kan lagra i sin databas. Min rekommendation brukar vara att sätta en maxgräns för hur stora filerna får bli, framförallt transaktionsloggen, därför att man annars riskerar att en felaktig SQL-sats eller annan körning råkar fylla upp hela hårddisken, vilket kan leda till betydligt större problem än om en fil inte kan växa med en tillbakarullad transkation som resultat.
Håll koll på filerna
Vilken väg man än väljer, automatisk växning eller ej, maxgräns eller ej etc, så bör man se till att hålla koll på storleken och utnyttjandet av filerna i en databas. Personligen gillar jag bäst att hantera detta semi-automatiskt, dvs låta SQL Server förstora filerna vid behov upp till en maxgräns jag sätter. Storleken sätter jag i antal MB, vilken jag normalt ändrar (ökar) när databasen växer alltmer. Men det viktiga är att hålla koll på vad som händer. Har man valt att låta SQL Server automatiskt hantera detta bör man ha koll på när filer växer, dels för att helt enkelt vara medveten om det men framförallt för att de inte ska nå maxgränsen, eller ännu värre fylla hårddisken. Har man stängt av den automatiska uppdateringen är det naturligtvis ännu viktigare att hålla koll på filernas storlek och utnyttjande så att man kan förstora dem manuellt i god tid innan de blir fyllda.
Storlek på datafiler
Naturligtvis kan man kolla storleken och utnyttjandet av filer i Enterprise Manager, men man vill antagligen göra detta lite mer automatiskt och helst i ett script. Det finns flera sätt att kontrollera hur mycket plats en databas tar, ett exempel är systemproceduren sp_spaceused. Denna kan antingen köras utan parametrar i vilket fall den visar info för hela databasen, alternativt kan man köra den med namnet på en tabell som inparameter för att visa info enbart för den tabellen.
USE pubs
EXEC sp_spaceused
database_name database_size unallocated space
------------------- ------------------ -----------------
pubs 2.50 MB 0.61 MB
reserved data index_size unused
-------------- ------------ -------------- ------------------
1168 KB 424 KB 616 KB 128 KB
EXEC sp_spaceused authors
name rows reserved data index_size unused
---------------- ---------- -------------- -------- --------------- ---------
authors 23 40 KB 8 KB 32 KB 0 KB
Detta ger oss en del bra info om databasens storlek och utnyttjande, och det är kanske allt vi behöver för att få en överblick över hur det ser ut just nu. Men den säger inget om de individuella filerna, och det är svårt att fånga upp och lagra detta i en tabell för att kunna jämföra över tid. Det odokumenterade DBCC-kommandot SHOWFILESTATS ger oss dock mer användbar info. Detta ger dock bara info om datafilerna, för att få info om loggfilerna får vi använda DBCC SQLPERF (LOGSPACE).
DBCC SHOWFILESTATS
Fileid FileGroup TotalExtents UsedExtents Name FileName
------ ----------- --------------- -------------- ------- -----------------------
1 1 876 855 pubs C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf
DBCC SQLPERF (LOGSPACE)
Database Name Log Size (MB) Log Space Used (%) Status
------------------- ---------------- ------------------------- ------
master 3.3671875 29.002319 0
tempdb 0.4921875 39.186508 0
model 0.4921875 79.067459 0
msdb 2.2421875 31.925087 0
pubs 0.9921875 33.956692 0
Med dessa resultat kan vi få en bra överblick på filstorlekar och utnyttjande. Några saker att notera är att storleken på datafilerna anges i extents (varje extent består av 8 st 8 KB sidor = 64 KB), logginfon ges för samtliga databaser samt att vi endast får info om samtliga loggfiler för varje databas om flera existerar.
Samla in resultatet
Vad vi vill göra nu är att samla den info vi får med ovan nämnda kommandon i en tabell för att kunna hålla koll på utvecklingen över tid. Nedanstående script skapar en procedur som samlar in datan och en tabell att lagra den i. Den lagrar filinfo för den aktuella databasen enbart, men det är enkelt att ändra om någon är intresserad av att samla in för alla databaser på en maskin.
IF EXISTS ( SELECT * FROM sysobjects WHERE [name] = 'FILEDETAILS' )
DROP TABLE FILEDETAILS
GO
CREATE TABLE FILEDETAILS
(
FileId int
, FileGroupId int
, TotalExtents int
, TotalSize AS ((TotalExtents * 64.0) / 1024)
, UsedExtents int
, UsedSize AS ((UsedExtents * 64.0) / 1024)
, Usage AS (STR((((UsedExtents * 1.0) / (TotalExtents * 1.0)) * 100), 5, 2) + ' %')
, [Name] nvarchar(128)
, [FileName] nvarchar(500)
, SampleDate datetime DEFAULT GETDATE()
)
GO
IF EXISTS ( SELECT * FROM sysobjects WHERE [name] = 'SampleFileInfo' )
DROP PROCEDURE SampleFileInfo
GO
CREATE PROCEDURE SampleFileInfo
AS
BEGIN
INSERT INTO FILEDETAILS (FileId, FileGroupId, TotalExtents, UsedExtents, [Name], [Filename])
EXEC ('DBCC SHOWFILESTATS WITH TABLERESULTS')
CREATE TABLE #LOGINFO (dbname sysname, logsize real, logspaceused real, status int)
INSERT INTO #LOGINFO (dbname, logsize, logspaceused, status)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH TABLERESULTS')
INSERT INTO FILEDETAILS (FileId, FileGroupId, TotalExtents, UsedExtents, [Name], [FileName])
SELECT 0, 0, SUM(sf.[size]) / 8, (SUM(sf.[size]) / 8) * (li.logspaceused / 100),null ',null N/A'
FROM sysfiles sf, #LOGINFO li
WHERE sf.groupid = 0
AND li.dbname = DB_NAME()
GROUP BY li.logspaceused
DROP TABLE #LOGINFO
END
GO
En snabb genomgång av procedurens funktionalitet:
* Först körs DBCC SHOWFILESTATS för att hämta info om datafilerna i den aktuella databasen. Observera att resultatet från detta körs direkt in i tabellen vi skapat. Notera även tillägget WITH TABLERESULTS, ett tillägg som kan användas till många DBCC kommandon för att få output i en tabulär form som kan sparas i en tabell.
* Därefter skapar vi en temporär tabell att lagra outputen från DBCC SQLPERF(LOGSPACE) i, eftersom denna inte är riktigt densamma som det förra kommandot ger oss.
* Slutligen kopierar vi in loggfilsinfon för den aktuella databasen genom att utnyttja DB_NAME() som urval, med en join mot systemtabellen sysfiles för att hämta lite extra info om loggfilerna som vi ej får genom DBCC-kommandot. Notera att loggfilernas storlek summeras eftersom DBCC-kommandot ger oss information om loggfilerna på det viset.
Sedan är det bara att schemalägga denna procedur att köra så ofta man vill, och sedan köra lämpliga SELECT-frågor på tabellen FILEDETAILS. Om man vill ha ett varningssystem som varnar när en fil har växt eller när en fil är fylld till mer än en viss nivå så kan det enkelt lösas med en trigger.
CREATE TRIGGER tiFILEDETAILS
ON FILEDETAILS
FOR INSERT
AS
BEGIN
-- Check for file growth
IF EXISTS ( SELECT i.fileid FROM INSERTED i INNER JOIN FILEDETAILS fd ON i.fileid = fd.fileid AND i.totalextents <> fd.totalextents WHERE fd.sampledate = (SELECT MAX(sampledate) FROM FILEDETAILS WHERE fileid = i.fileid AND sampledate < i.sampledate) )
BEGIN
-- One or more files have changed size, take appropriate actions
PRINT 'File growth'
END
-- Check for file usage
IF EXISTS ( SELECT fileid FROM INSERTED WHERE (UsedExtents * 1.0) / (TotalExtents * 1.0) > 0.85 )
BEGIN
-- One or more files is filled more than the safe level
PRINT 'Overuse of file'
END
END
Denna trigger är som synes väldigt enkel och tittar egentligen bara på om någon fil vuxit samt om någon fil används mer än en hårdkodad nivå. Detta kan enkelt byggas ut till att rapportera exakt vilka filer som varningarna gäller. Dessutom ska den antagligen göra mer än att bara printa ett varningsmeddelande, man kan t ex skriva i SQL Servers fellogg eller skicka ett mail till en operatör. Men det tar vi en annan gång. Ett annat tips till vidareutveckling kan vara att ge möjlighet att specificera för varje fil om de ska kontrolleras för storlek eller utnyttjande (eller både och) samt vilka gränser man vill ha.
0 Kommentarer