Γιατί δεν μπορώ να κάνω shrink το transaction log στον SQL Server και γιατί γέμισε
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Σε προηγούμενο άρθρο είχαμε δει τι είναι το Transaction Log. Αυτή τη φορά θα δούμε πως μπορούμε να το αδείασουμε και να το κάνουμε shrink, επίσης θα δούμε τι μπορούμε να κάνουμε ανά περίπτωση όταν δεν μας αφήνει.
Ξαφνικά μια μέρα βλέπουμε να μην μας σκάνε οι συναλλαγές σε συγκεκριμένη βάση δεδομένων στον SQL Server και να μας έχει γεμίσει ο δίσκος που είναι αποθηκευμένα τα Transaction Log files. Τι κάνουμε;
Πώς κάνουμε shrink το transaction log
Για αρχή με την παρακάτω function θα δούμε το μέγεθος του transaction log της κάθε βάσης και ποσοστό χρήσης του:
dbcc sqlperf(logspace);
Αφού το τρέχουμε βλέπουμε ότι σε συγκεκριμένη βάση έχει φτάσει τα 40GB και κάνει χρήση το 100% του:
Για να δούμε το όνομα του transaction log file που έχει γεμίσει τρέχουμε το παρακάτω στη βάση:
select name,(size*8/1024/1024) SizeGB from sys.database_Files where type_desc = 'LOG';
Τώρα που έχουμε το όνομα με την function DBCC SHRINKFILE μπορούμε να δοκιμάσουμε να μικρύνουμε το αρχείο σε έως 1024mb:
USE [StackOverflow2013]
GO
DBCC SHRINKFILE ('StackOverflow2013_log5' , 1024);
Μας εμφανίζει όμως το παρακάτω μήνυμα που μας λέει ότι ο χώρος χρειάζεται άρα κάτι δεν έχουμε κάνει καλά:
Γιατί δεν μας αφήνει να κάνουμε shrink το transaction log
Ας δούμε πόσο χώρο καταλαμβάνουν τα Virtual Log Files που είναι σε status Active τα οποία δεν γίνεται να τα σβήσουμε:
select sum(vlf_size_mb) ActiveVLFsizeMB from sys.dm_db_log_info(DB_ID('StackOverflow2013'))
where 1=1
and vlf_status = 2;
--Αν η έκδοση είναι πριν την 2016 τότε δεν δουλεύει το view και τρέχουμε την loginfo:
dbcc loginfo;
Βλέπουμε ότι πιάνει αρκετά gigabytes. Αυτό σημαίνει ότι είτε υπάρχει κάποιο transaction σε εξέλιξη είτε / και πρέπει να πάρουμε backup το transaction log σε περίπτωση που η βάση μας είναι σε Full recovery model.
Για να δούμε σε τι Recovery Model είναι η βάση μας:
select name,recovery_model_desc from sys.databases;
Βλέπουμε ότι είναι σε Full, πράγμα που σημαίνει ότι για να μειωθεί το ποσοστό και να μας αφήσει να κάνουμε shrink το transaction log θα πρέπει πρώτα να έχουμε πάρει transaction log backup:
Ας δοκιμάσουμε να τρέξουμε ένα job που θα πάρει transaction log backup:
exec msdb.dbo.sp_start_job 'DatabaseBackup - USER_DATABASES - LOG';
Αφού τελειώσει το backup, περιμένουμε λίγο και ξαναδοκιμάζουμε να δούμε το log:
dbcc sqlperf(logspace);
Βλέπουμε πάλι ότι είναι 100%, άρα παρόλο που δεν τρέχει κάτι στη βάση πρέπει να έχει μείνει ανοιχτό κάποιο transaction. Αυτό μπορούμε να το ψάξουμε με την function DBCC OPENTRAN:
dbcc opentran;
Βλέπουμε ότι υπάρχει ανοιχτή συναλλαγή στο session id 68…:
Ελέγχοντας με το παρακάτω τι τρέχει αυτή την στιγμή στη βάση με session_id 68 δεν βρίσκει αποτέλεσμα:
select * from sys.dm_exec_requests where session_id=68;
Όμως κάπου εδώ σας είχα κοροϊδέψει, καθώς εξαρχής είχα αφήσει ένα transaction το οποίο αν και είχε ολοκληρώσει το delete, δεν το είχα κλείσει με commit ή rollback και παρέμενε ενεργό στο transaction log:
Μπορούμε τώρα να κάνουμε kill το session id 68 και να βλέπουμε και το status του rollback με το παρακάτω:
kill 68 kill 68 with statusonly
Τώρα πρέπει να ξανά κάνουμε backup το transaction log:
exec msdb.dbo.sp_start_job 'DatabaseBackup - USER_DATABASES - LOG';
Οπότε τώρα αν ξαναδούμε τα active Virtual Log Files θα δούμε ότι έπεσε το μέγεθος:
select sum(vlf_size_mb) ActiveVLFsizeMB from sys.dm_db_log_info(DB_ID('StackOverflow2013'))
where 1=1
and vlf_status = 2;
--Αν η έκδοση είναι πριν την 2016 τότε δεν δουλεύει το view και τρέχουμε την loginfo:
dbcc loginfo;
Και κάνουμε πάλι έλεγχο του ποσοστού του transaction log:
dbcc sqlperf(logspace);
Θα δούμε ότι πλέον χρησιμοποιείται κάτω του 1% του transaction log:
Οπότε τώρα θα μπορούμε να κάνουμε shrink:
USE [StackOverflow2013]
GO
DBCC SHRINKFILE ('StackOverflow2013_log5' , 1024);
Βλέπουμε ότι ολοκληρώθηκε αυτή τη φορά επιτυχώς:
Αν δούμε πάλι τον χώρο στο transaction log:
dbcc sqlperf(logspace);
Θα δούμε ότι έπεσε από το φυσικό μέγεθος των 50GB στο 1GB που του ορίσαμε:
Αν κάποιο προγενέστερο VLF παραμένει active
Σε περίπτωση που μας εμφανίσει το παρακάτω μύνημα, σημαίνει ότι κάποιο Virtual Log File που προηγείται χρονικά των επόμενων που έχουν αδειάσει είναι ακόμα ενεργό (μπορούμε να το δούμε με την dbcc loginfo ψαχνόντας όσα έχουν status 2).
Τότε δοκιμάζουμε να ξαναπάρουμε transaction log backup και έπειτα κάνουμε ξανά shrink.
Αν εξακολουθεί να μας εμφανίζει αυτό το μύνημα, παρότι έχουμε αφήσει να περάσει και κάποια ώρα μπορεί να ευθύνεται κάποιο Availability Group, transactional replication ή ότιδήποτε άλλο.
Οπότε σαν λύση ανάγκης μπορούμε να χάσουμε προσωρινά τη δυνατότητα του Point in Time για μετά του τελευταίου transaction log backup αλλά να φτιάξουμε το πρόβλημα τρέχοντας τα παρακάτω:
USE [StackOverflow2013]
GO
ALTER DATABASE [StackOverflow2013]
SET RECOVERY SIMPLE;
GO
CHECKPOINT;
DBCC SHRINKFILE ('StackOverflow2013_log5', 1);
GO
ALTER DATABASE [StackOverflow2013]
SET RECOVERY FULL;
GO
