Is it okay to delete MSDB?

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.

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

Is it okay to delete MSDB?

Tags:

Sql Server

Msdb

Related

Recent Posts