Is it okay to delete MSDB?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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…