9

I'm not a DBA, I've only googled what MSDB does it's basically a DB of SQL Agent of its Job and History, Now I'm running out of space in my cloud server and I have 1 year worth of MSDB year 2017, Is it okay to delete this or do I keep it for backup purposes?

My MSDB is 93GB in a 250GB HDD.

14

You can't drop the msdb database as stated in the docs (emphasis mine):

Restrictions

The following operations cannot be performed on the msdb database:

  • Changing collation. The default collation is the server collation.

  • Dropping the database.

  • Dropping the guest user from the database.

  • Enabling change data capture.

  • Participating in database mirroring.

  • Removing the primary filegroup, primary data file, or log file.

  • Renaming the database or primary filegroup.

  • Setting the database to OFFLINE.

  • Setting the primary filegroup to READ_ONLY.

Tampering with system databases isn't a good idea usually. You should check where your space problem is and consider expanding your drives.


To check where your size problem is:

  • Check table and index sizes inside msdb database using this query:

    USE msdb
    GO
    
    SELECT 
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    GROUP BY 
        t.Name, s.Name, p.Rows
    ORDER BY 
        TotalSpaceMB DESC
    

    If sysjobhistory pops up in space then review your current job history rentention policy and make sure that your jobs' schedules are on check and not triggering more often than they need.

  • Check database data and log file sizes with this query:

    ;with fs
    as
    (
        select database_id, type, size * 8.0 / 1024 size
        from sys.master_files
    )
    select
        name,
        (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeInMB,
        (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeInMB
    from 
        sys.databases db
    where
        db.name = 'msdb'
    

    If the log file size is high you need to find out which operation made it increase it's size and troubleshoot it. Shrinking the file will free some space but won't solve the underlying problem.

  • my MSDB is 93GB in a 250GB HDD, ill probably consider expanding my HDD but for now ill Purge the old records Thanks. – Terrence McGinnis Jul 18 '18 at 15:02
5

As covered in other answers here, do not attempt to delete the entire msdb database.

You might want to drop certain history records that are stored in the msdb database, and then shrink the database if you need to save space. Be aware, I'm not advising you do this since the database will most certainly need to grow again unless you closely manage space used by msdb on an ongoing basis.

You can remove history from the msdb database using these stored procedures:

USE msdb;
EXEC dbo.sp_delete_backuphistory @oldest_date = '2018-01-01T00:00:00';
EXEC dbo.sp_purge_jobhistory @job_name = NULL
    , @job_id = NULL
    , @oldest_date = '2018-01-01T00:00:00';

To shrink msdb, you can use this:

USE msdb;

DBCC SHRINKFILE (MSDBData, 0);
DBCC SHRINKFILE (MSDBLog, 0);

The above operations will attempt to shrink the msdb data and log files to the smallest size possible. For the log file, the "smallest size possible" is limited by the most recently allocated virtual log file.

FYI, you can see how much space is being consumed in a database by running the following code:

USE <database_name>;

DECLARE @Schema sysname;
DECLARE @Table sysname;
DECLARE @DSName sysname;

--if you're interested in a subset of the objects in a database,
--specify that subset by modifying these variables.  @DSName is
--the name of a filegroup, or possibly a partition.
SET @Schema = NULL;
SET @Table = NULL;
SET @DSName = NULL;

SELECT DataSpace = ds.name
    , ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = i.name
    , IndexType = i.type_desc
    --, total_pages
    --, used_pages
    --, data_pages
    , TotalMB = CONVERT(INT, total_pages * 8192E0 / 1048576)
    , UsedMB = CONVERT(INT, used_pages * 8192E0 / 1048576)
    , DataMB = CONVERT(INT, data_pages * 8192E0 / 1048576)
    , [rows]
    --, i.*
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p ON (au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)) OR (au.container_id = p.partition_id AND au.type = 2)
    INNER JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE /*o.is_ms_shipped = 0
    AND total_pages > 128
    AND */(ds.name = @DSName OR @DSName IS NULL)
    AND (s.name = @Schema OR @Schema IS NULL)
    AND (o.name = @Table OR @Table IS NULL)
ORDER BY ds.name, o.name, i.name
1

As per microsoft blog documentation here MSDB is an important system database in Microsoft SQL server. The msdb database is used mainly by the SQL Server Agent to store system activities like sql server jobs, mail, service broker, maintenance plans, user and system database backup history, etc..It is also used by database engine and management studio.

There are certain DML And DDL Operations which cannot be performed in the MSDB Database like:

  1. We cannot drop tables from this database.
  2. We cannot drop this database.
  3. We cannot make this system database to go off-line.
  4. We cannot do database mirroring on this database.
  5. We cannot rename this database name. etc…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.