Low Disk Drive Space Issue Caused by SQL Server Database Files
Low disk space is often raised as alerts from monitoring tools or at times we notice when we RDP to the server or where the SQL Server or databases appear down and we notice insufficient space errors.
– Need to determine what type of drive is filling up. Is it something that will threaten the integrity of the SQL process? For example a transaction log drive filling up is more urgent than a backup drive filling up.
– If drive only contains data files then check the file which is using lot of space. We can find the file size, path and free and used file sizes of SQL database files from SSMS using below query
Select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a
– It is not good to shrink data files as it will cause lot of fragmentation and dramatically slowdown the performance, so we need to check on adding addition disk space to the drive or plan to move some data files to another drive. Capacity planning needs to be done to estimate the required disk space for database files for next one year.
– If the drive contains transaction log files, run the below query to find the log files which have high percent of space used and large log files. you can try to easily shrink the log files sizes. However, you might run into issues when the engine does not let you shrink the file, for example: replication, log shipping or mirroring holding the log, a long running transaction, database set to Full recovery and no backup has run, etc. You will need to clear these conditions before space can be recovered from the log files.
Above query should give an idea on which log file is large and if it is FULL.
Below query will provide the reason on why the log file is now allowed to shrink
Select log_reuse_wait_desc, name from sys.databases
– If backup files causing low disk space. This depends on each environment on how the backup are configured, need to review the back maintenance plan/jobs and check for the retention period of the backups. Verify the backup path to see if more backups are stored than required, if yes, then check and remove backups which are not required after confirming with all stakeholders
– If the files taking up the space are NOT SQL Server related. For example, temp files, application files, hibernate files, general user files, etc. In these cases consult server team and other stakeholders for the server.
Hope this was helpful.