Reclaim Drive Space From Your SQL Server

In any work process, major and minor issues are ever-present and there are no exceptions for driving space. For many people, a driving place that has the greatest memories is truly irritating and bothersome. Our client – Europe’s leading supplier of vertical market software for customers in a restricted set of public and private markets, our strategic partner, struggled to manage the driving space, which led to slowness and therefore diminished work effectiveness.

Mr. Lu Dieu, a Senior .NET Developer with over 5 years of experience, who joined an offshore development team that Saigon Technology builds for our clients according to Saigon Technology’s customer service model, successfully handled this situation thoroughly, with specific instructions following the steps below, may this information be beneficial for you: 

Problem

We recently received an alert from the monitoring system informing us that a database server was operating extremely slowly… The disk that SQL Server relies on was close to being full when the first alert arrived, and the second one appeared shortly after.

The Journey to The Solution

First, I attempted to RD to the server, and I was shocked to see that SQL Server was installed on D disk, which only had around 10Gb in total.

Determine database sizes on the server

I used the SQL script below to list all server databases of their size: 

WITH fs

AS (SELECT database_id,

           type,

           size * 8.0 / 1024 size,

           file_id

    FROM sys.master_files)

SELECT name,

       (

           SELECT SUM(size)

           FROM fs

           WHERE type = 0

                 AND fs.database_id = db.database_id

       ) DataFileSizeMB,

       (

           SELECT SUM(size)

           FROM fs

           WHERE type = 1

                 AND fs.database_id = db.database_id

       ) LogFileSizeMB

FROM sys.databases db

ORDER BY DataFileSizeMB DESC;

As we see, the msdb has taken up 5 Gb, while the disk has got just 10 Gb.

The msdb database (SQL Server Agent Service) is a system database used for various components of the SQL Server. The replation, log shipment, and maintenance plan data are kept in the msdb databases in addition to SQL Server Agent setup and task information.

Next, we will determine the size of each table in the database.

Determine table sizes in msdb

We can use the below script to get the size of each table in the msdb database.

USE [msdb];

GO

SELECT TOP (10)

       –o.[object_id],

       obj = SCHEMA_NAME(o.[schema_id]) + ‘.’ + o.name,

       o.[type],

       i.total_rows,

       i.total_size

FROM sys.objects o

    JOIN

    (

        SELECT i.[object_id],

               total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18, 2)),

               total_rows = SUM(   CASE

                                       WHEN i.index_id IN ( 0, 1 )

                                            AND a.[type] = 1 THEN

                                           p.[rows]

                                   END

                               )

        FROM sys.indexes i

            JOIN sys.partitions p

                ON i.[object_id] = p.[object_id]

                   AND i.index_id = p.index_id

            JOIN sys.allocation_units a

                ON p.[partition_id] = a.container_id

        WHERE i.is_disabled = 0

              AND i.is_hypothetical = 0

        GROUP BY i.[object_id]

    ) i

        ON o.[object_id] = i.[object_id]

WHERE o.[type] IN ( ‘V’, ‘U’, ‘S’ )

ORDER BY i.total_size DESC;

The above result shows that most records are part of the backup history.

Let us now look at two tables, what are they? What are they now?

dbo.backupset contains a row for each backup set. A backup set contains the backup from a single, successful backup operation.

dbo.backupfile contains one row for each data or log file of a database.

Besides, we can also learn more or find more information about other tables by reading several knowledgeable sites.  

Reduce the size of the backup and restore history tables

By removing the backup entries, we may minimize the size of these tables. Fortunately, Microsoft provides us with a stored procedure for handling the work, we do not need to delete data manually in every database.

Assume that we wish to delete backup records older than 3 months, therefore the script is:

USE msdb;  

GO  

— Remove all history log order than 3 months

DECLARE @oldest_date DATETIME = DATEADD(MONTH, -3, GETDATE())

EXEC sp_delete_backuphistory @oldest_date;  

Let’s see the result after executing the script.

The size of the table is significantly reduced from 5Gb to less than 400Mb. Nice!

It’s not the end, nevertheless!

We know that after every backup or restore operation, more rows are added to the backup and restoration history tables and will expand every day. Therefore, sp_delete_backuphistory is regularly required.

The maintenance plan will this time be implemented. Let us now proceed to configure the clean-up task for history. 

SQL Server Maintenance Plan History Cleanup Task

We’re going to create a cleaning of history. It may be designed through the Wizard maintenance plan and the Designer maintenance plan. To design it, let’s use the wizard.

Follow these procedures in SSMS in order to start the Wizard:

Go to Management > Maintenance Plans and get a right-click, then select Maintenance Plan Wizard:

Click “Next”

Fill the name and description for the task, then click on “Change…” to set the schedule.

Select your appropriate schedule and click “OK”

Double-check the schedule information is correct and click “Next”

After that, we obviously choose the “Clean Up History” task and click “Next”:

In the next window, we set the configurations of the task. As we can see, we can choose which kind of historical data we need to clean Backup and restore history, SQL Server Agent job history and Maintenance plan history. Additionally, we can set how much data to keep:

We are going to clean all mentioned types of historical data records from msdb which are older than three months. Then, we click “Next”

Finally, we click finish to create the plan:

Thus, our cleanup task is successfully created:

If We refresh Maintenance Plans and SQL Server Agent Jobs, we can locate the newly created plan and the corresponding job.

From now on, we don’t need to worry about the unnecessary information generated and cause the disk is full again.

Useful Links For Further Reference

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backup-and-restore-tables-transact-sql?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/history-cleanup-task-maintenance-plan?view=sql-server-ver15 

https://www.mssqltips.com/sqlservertip/1727/purging-msdb-backup-and-restore-history-from-sql-server/ 

https://www.sciencedirect.com/topics/computer-science/master-database