Several times SQL Server might be the reason for the space issue on disks. In this article, we will see what the root causes for this issue are and how we can fix it.
Your SQL Server needs space.
Many times SQL Server will need disk space. This might be because of growing data inside your database or unshrunk log files or undeleted backup files or undeleted, unwanted database files. Whatever the reason is, on a SQL Server, space on the disk is very important for database transactions.
Cleanup task not working
If you are using SQL’s native maintenance plans to backup your SQL server databases, there might be chances that the cleanup module in those maintenance plans is not doing their task. When the disk runs out of space you will realize that old backup files are not properly cleaned. You would have included the cleanup module in the maintenance plan. In spite of all this do you wonder why it was not working?
Check if you have mentioned the correct folder to delete backup files, check if you have mentioned the correct file extension to delete backup files, try with dot “.” and without a dot in the file extension.
Log files are huge than database files
Most common cause for space issue on SQL Server is the unattended log files and tempdb files. Though tempdb will get shrunk to the original size whenever the SQL Service restarts, it is a good practice to monitor tempdb growth and shrink it when it is about to eat entire disk space. Similar to tempdb, the log files should be always under check. Ensure that you have log backup in place to always keep log files at minimal size.
Unused database files
There might be many unused and unattached database files sitting on your disk and wasting space. Execute the script on your SQL Server instance to identify such files along with their path. After a quick analysis, if you are still sure that those files are not needed anymore, delete them and save space.
DECLARE @dpth NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer' ,N'DefaultData' ,@dpth OUTPUT DECLARE @lpth NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer' ,N'DefaultLog' ,@lpth OUTPUT DECLARE @bk NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer' ,N'BackupDirectory' ,@bk OUTPUT DECLARE @md NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters' ,N'SqlArg0' ,@md OUTPUT SELECT @md = substring(@md, 3, 255) SELECT @md = substring(@md, 1, len(@md) - charindex('\', reverse(@md))) DECLARE @ml NVARCHAR(512) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters' ,N'SqlArg2' ,@ml OUTPUT SELECT @ml = substring(@ml, 3, 255) SELECT @ml = substring(@ml, 1, len(@ml) - charindex('\', reverse(@ml))) SET @dpth = isnull(@dpth, @md) SET @lpth = isnull(@lpth, @ml) PRINT @dpth PRINT @lpth EXEC sp_configure 'show advanced' ,1 RECONFIGURE EXEC sp_configure 'xp_cmdshell' ,1 RECONFIGURE IF object_id('tempdb.dbo.#table1') IS NOT NULL DROP TABLE #table1 CREATE TABLE #table1 ( [filename] VARCHAR(2000) ,depth INT ,isFile INT ) SET @dpth = 'DIR ' + @dpth + '\*.mdf /b /s' SET @lpth = 'DIR ' + @lpth + '\*.ldf /b /s' INSERT INTO #table1 EXEC xp_DirTree @dpth ,1 ,1 INSERT INTO #table1 EXEC xp_DirTree @lpth ,1 ,1 DELETE FROM #table1 WHERE isFile <> 1 UPDATE #table1 SET filename = rtrim(filename) CREATE TABLE t_list ( filepath VARCHAR(2000) ,sizeinmb DECIMAL(18, 2) ) INSERT INTO t_list (filepath) SELECT otable.filename AS orphaned_files FROM #table1 otable LEFT OUTER JOIN master.dbo.sysaltfiles db ON rtrim(db.filename) = otable.filename WHERE db.dbid IS NULL ORDER BY 1 DECLARE @sizeingb AS DECIMAL(18, 2) DECLARE @filepath AS VARCHAR(2000) DECLARE db_cursor CURSOR FOR SELECT filepath FROM t_list OPEN db_cursor FETCH NEXT FROM db_cursor INTO @filepath WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE t_temp (c1 VARCHAR(2000)) DECLARE @cmd AS VARCHAR(3000) SET @cmd = 'dir ' + @filepath PRINT @cmd INSERT INTO t_temp EXEC master.dbo.xp_cmdshell @cmd DELETE FROM t_temp WHERE c1 NOT LIKE '%1 File(s)%bytes' DECLARE @size AS DECIMAL(18, 2) SET @size = ( SELECT TOP 1 replace(replace(replace(c1, ' 1 File(s) ', ''), ',', ''), ' bytes', '') FROM t_temp WHERE c1 IS NOT NULL ) SET @size = cast((@size / (1024 * 1024)) AS DECIMAL(18, 2)) DROP TABLE t_temp UPDATE t_list SET sizeinmb = @size WHERE filepath = @filepath FETCH NEXT FROM db_cursor INTO @filepath END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM t_list DROP TABLE t_list EXEC sp_configure 'show advanced' ,1 RECONFIGURE EXEC sp_configure 'xp_cmdshell' ,0 RECONFIGURE
SQL Server Database Corruption
In addition to monitoring and maintaining your disk space, also monitor your disk’s health. Unhealthy disk can corrupt your SQL Server databases. If this happens, please use database recovery tool like DataNumen SQL Recovery to fix corrupted SQL Server.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook email corruption and excel recovery software products. For more information visit www.datanumen.com