How to Fix the Space Issues Caused by SQL Server

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