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.
Author Introduction:
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