A disk is full of SQL Server datafiles, what should I do?

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

Sometimes a disk will suddenly become full. On this disk we may have one base or many, while we may have space on another disk, we have to somehow find which one datafiles they are full. In this article we will see how we can quickly solve the problem SQL Server.

We find how many datafiles with autogrowth have filled the disk

Our first move is to quickly find out which one datafiles who have autogrowth on the disk are full. In order to be able to check all bases in the instance at the same time, we should use the procedure sp_MSforeachdb.

With the script I have written, we simply change the disk that has been filled in where s.physical_name like ”C:\%” and with the number for the percentage and above that we want it to bring us 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
'

We quickly see that we have two datafiles on disk C:/ which have an occupancy rate above 90%. We can export the results directly to csv file without headers so that if there are too many we can have them all physical names in one column for the next step:

We make new datafiles for what was filled on the other disk

As a next step we want to give new datafiles to another disk for what we found in the previous step to be full. In the next script that I have made, we define the parameter @new_storage='D:\Data\' for the disk that will also go to the parameter sys.master_files.physical_name in
('C:\Program…..,'C:\….') as many datafiles as we found in the previous step:

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'
)

Then we simply execute the generated commands. For the example I have set an initial size of 1gb with 256mb autogrowth:

We have one more thing left, autogrowth is still open

After we solved the problem and now the bases are writing to another disc, it is good to close the autogrowth on them since the original disc has no space. All we have to do is have in the parameter physical_name like 'C:\%' the analog 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'
--)

And finally we simply execute the commands created to close autogrowth on the files of this disk: