Γέμισε ένας δίσκος με τα datafiles του SQL Server, τι να κάνω;

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Linux - 1 Ιούλιος 2024
• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024

Κάποιες φορές θα τύχει ξαφνικά να γεμίσει κάποιος δίσκος. Σε αυτόν τον δίσκο μπορεί να έχουμε μία βάση μπορεί και πολλές, ενώ μπορεί να έχουμε χώρο σε άλλο δίσκο πρέπει κάπως να βρούμε ποια datafiles έχουν γεμίσει. Σε αυτό το άρθρο θα δούμε πως γρήγορα μπορούμε να δώσουμε λύση στον SQL Server.

Βρίσκουμε όσα datafiles με autogrowth έχουν γεμίσει στον δίσκο

Πρώτη μας κίνηση είναι να βρούμε γρήγορα ποια datafiles που έχουν autogrowth στον δίσκο έχουν γεμίσει. Για να μπορούμε να κάνουμε ταυτόχρονα έλεγχο όλες τις βάσεις στο instance θα πρέπει να χρησιμοποιήσουμε την procedure sp_MSforeachdb.

Με το script που έχω γράψει απλα αλλάζουμε τον δίσκο που έχει γεμίσει στο where s.physical_name like ”C:\%” και με τον αριθμό για το ποσοστό και πάνω που θέλουμε να μας φέρει CAST(… as numeric ) > 89:

sp_MSforeachdb 'USE [?]
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [SizeMB],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpaceMB],
cast(((CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) END AS float))/1024) / ((s.size * CONVERT(float,8))/1024) * 100 as numeric )as [Percentage]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
where
s.[type] = 0 and s.physical_name like ''C:\%'' and s.growth != 0 
and cast(((CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) END AS float))/1024) / ((s.size * CONVERT(float,8))/1024) * 100 as numeric ) > 89
'

Βλέπουμε γρήγορα ότι έχουμε δύο datafiles στον δίσκο C:/ που έχουν ποσοστό πληρότητας πάνω από 90%. Μπορούμε να εξάγουμε τα αποτελέσματα κατευθείαν σε csv αρχείο χωρίς headers ώστε αν είναι πάρα πολλά να τα έχουμε όλα τα physical names σε μία στήλη για το επόμενο βήμα:

Φτιάχνουμε καινούργια datafiles για όσα είχαν γεμίσει στον άλλον δίσκο

Σαν επόμενο βήμα θέλουμε να δώσουμε καινούργια datafiles σε άλλον δίσκο για όσα βρήκαμε στο προηγούμενο βήμα ότι γέμισαν. Στο επόμενο script που έχω φτιάξει ορίζουμε τη παράμετρο @new_storage=’D:\Data\’ για τον δίσκο που θα πάνε και στη παράμετρο sys.master_files.physical_name in
( ‘C:\Program…..,’C:\….’) όσα datafiles βρήκαμε στο προηγούμενο βήμα:

declare @new_storage varchar(50)
set @new_storage='D:\Data\'

select 
    'ALTER DATABASE [' + sys.databases.name + '] ADD FILE ( NAME = ' + sys.master_files.name + '_new, FILENAME = '+@new_storage+ sys.master_files.name + '_new.ndf'',SIZE = 1GB, FILEGROWTH = 256MB);'
from sys.master_files 
cross apply sys.databases
cross apply sys.dm_db_file_space_usage 
Where 
sys.databases.database_id = sys.master_files.database_id and 
sys.dm_db_file_space_usage .file_id = master_files.file_id and
sys.master_files.[type] = 0 and 
--sys.master_files.physical_name like 'C:\%' and 
sys.master_files.growth != 0 and 
sys.master_files.physical_name in  --gia perisotero elegxo
(
'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\master.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\MSDBData.mdf'
)

Έπειτα απλά εκτελούμε τις εντολές που δημιουργήθηκαν. Για το παράδειγμα έχω ορίσει αρχικό μέγεθος 1gb με 256mb autogrowth:

Μας έμεινε κάτι ακόμα, το autogrowth είναι ακόμα ανοιχτό

Αφού λύσαμε το πρόβλημα και πλέον οι βάσεις γράφουν σε άλλο δίσκο καλό είναι από την στιγμή που αρχικός δίσκος δεν έχει χώρο να κλείσουμε το autogrowth σε αυτά. Το μόνο που έχουμε να κάνουμε είναι να έχουμε στη παράμετρο physical_name like ‘C:\%’ το ανάλογο drive:

select 
    'ALTER DATABASE [' + db_name(database_id) + '] MODIFY FILE ( NAME = N''' + name + ''', FILEGROWTH = 0)'
from sys.master_files Where [type] = 0 and 
physical_name like 'C:\%' and 
growth != 0 
--and sys.master_files.physical_name in  -- gia perisotero elegxo
--(
--'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\master.mdf',
--'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\MSDBData.mdf'
--)

Και τέλος απλά εκτελούμε τις εντολές που δημιουργήθηκαν για το κλείσιμο του autogrowth στα αρχεία του δίσκου αυτού: