Managing SQL Server MSDBData.mdf Growth and Backup History in SIMATIC Process Historian

In SIMATIC Process Historian (such as PH 2014 SP1 on PCS 7 V8.1), massive growth of the Microsoft SQL Server system database msdb (MSDBData.mdf exceeding 100 GB to 200 GB) filling drive C: occurs because the SQL Server backup history and maintenance job history tables accumulate millions of records without automatic truncation. Process Historian frequently creates, backs up, and archives database segments, writing a historical row into msdb for every transaction. To resolve this, execute the system stored procedures sp_delete_backuphistory and sp_purge_jobhistory via SQL Server Management Studio to purge records older than 30 days, shrink MSDBData.mdf, and deploy an automated weekly SQL Agent maintenance job.

Process Historian Database Architecture and msdb Role

SIMATIC Process Historian (PH) serves as the high-capacity, long-term central archive server for SIMATIC PCS 7 and WinCC systems. It runs on Microsoft SQL Server (Enterprise or Standard Edition), managing massive relational databases partitioned into discrete time-based segments (PH#...) storing process values, alarm logs, and batch records.

In addition to application databases, SQL Server maintains standard system databases: master, model, tempdb, and msdb. The msdb database is utilized by SQL Server Agent, the Process Historian maintenance services, and automated backup tasks. Every time a database backup occurs—or every time a Process Historian segment is backed up, swapped offline, or verified—SQL Server logs comprehensive metadata (backup set IDs, physical media families, backup file paths, completion timestamps, and job outcomes) inside system tables within msdb.

Root Causes of Uncontrolled MSDBData.mdf Ballooning

When Process Historian drives exhaust all available storage on the operating system drive (C:\), technical analysis reveals that the culprit is rarely the Process Historian data itself, but rather MSDBData.mdf expanding to over 200 GB:

High-Frequency Segment Operations Without History Purging: In continuous plant operations, Process Historian creates and backs up dozens of archive segments daily. SQL Server inherently never deletes backup history metadata on its own. Over months or years, tables such as backupset, backupfile, and backupmediafamily swell to tens of millions of rows.
Excessive SQL Server Agent Job History Logging: Process Historian background jobs execute every few minutes to validate archive states, mirror databases, and monitor disk space. If the SQL Server Agent job history log limit is unconstrained, the sysjobhistory table accumulates millions of execution lines.
Default System Drive Placement: By default, SQL Server places system databases (msdb, master) on C:\Program Files\Microsoft SQL Server\.... Because C:\ partitions are typically sized for the OS (e.g., 100 GB to 250 GB), uncontrolled msdb growth crashes the server.
PH 2014 SP1 Base Limitations: Early versions of PH 2014 lacked an integrated background maintenance service to purge SQL Server Agent history, a feature refined in subsequent updates (PH 2014 SP3 Upd1).

SQL Server History Tables and Purge Stored Procedures Matrix

The following table details the primary tables responsible for msdb expansion and their official remediation procedures:

Table Name in msdbType of Data StoredTypical Growth ImpactOfficial Cleanup ProcedurebackupsetBackup metadata (size, date, user)Millions of rowssp_delete_backuphistory @oldest_datebackupfileFile locations of backup sets2 to 10 rows per backupCascaded delete via sp_delete_backuphistorybackupfilegroupFilegroup data for backup filesScales with backup filesCascaded delete via sp_delete_backuphistorybackupmediafamilyPhysical media device paths1 row per backup fileCascaded delete via sp_delete_backuphistorysysjobhistorySQL Agent job execution historyMillions of execution logssp_purge_jobhistory @oldest_datesysmail_mailitemsDatabase Mail history and logsThousands of email logssp_delete_mailitems @sent_before

Step-by-Step Procedure to Purge History and Shrink msdb

To permanently resolve MSDBData.mdf growth and recover disk capacity on the Process Historian server:

Purge Backup and Job History via SQL Server Management Studio (SSMS):
- Log into the Process Historian server with local Windows administrator credentials.
- Launch SQL Server Management Studio (SSMS) and connect to the local SQL Server instance (.\HISTORIAN or default instance).
- Open a New Query window, ensure the target database is set to msdb, and execute:
```sql
USE msdb;
GO
-- Declare retention date (purge records older than 30 days)
DECLARE @CleanupDate DATETIME = DATEADD(DAY, -30, GETDATE());

-- Purge backup history
EXEC sp_delete_backuphistory @oldest_date = @CleanupDate;

-- Purge SQL Server Agent job history
EXEC sp_purge_jobhistory @oldest_date = @CleanupDate;
GO
```
- Note: If the table contains hundreds of millions of rows, executing this query may take 15 to 45 minutes. Do not interrupt the query.
Shrink the Physical Database File (MSDBData.mdf):
- Once history is deleted, the data file contains empty space that must be released back to the Windows file system.
- In SSMS, execute the shrink commands:
```sql
USE msdb;
GO
-- Check logical file name
EXEC sp_helpfile;

-- Shrink data file to 1024 MB
DBCC SHRINKFILE (MSDBData, 1024);

-- Shrink log file
DBCC SHRINKFILE (MSDBLog, 512);
GO
`` - Verify in Windows Explorer that driveC:` immediately recovers tens or hundreds of gigabytes of free space.
Deploy an Automated Weekly SQL Server Maintenance Job:
- In SSMS, expand SQL Server Agent -> Jobs.
- Right-click Jobs and select New Job; name it PH_MSDB_Weekly_Maintenance.
- Under Steps, create a step that executes the sp_delete_backuphistory and sp_purge_jobhistory commands weekly.
- Under Schedules, configure the job to run every Sunday at 02:00 AM.
Configure SQL Agent History Limits:
- In SSMS, right-click SQL Server Agent and select Properties.
- Select History; check Limit size of job history log.
- Set Maximum job history log size (in rows) to 10000 and Maximum job history rows per job to 1000.

Verification

Verify database size and Process Historian operation:
1. In Windows Explorer, browse to C:\Program Files\Microsoft SQL Server\MSSQL...\DATA\; confirm that MSDBData.mdf size has dropped from > 200 GB to approximately 1 GB.
2. Open the Process Historian Management Console; confirm that server status is Active (green) and no longer in the locked state.
3. Trigger an archive segment backup; verify that the backup executes and completes successfully.
4. In SSMS, view job history for PH_MSDB_Weekly_Maintenance; confirm that scheduled purges execute with successful return codes.

Limitations and Operational Precautions

Locking Risk During Purge: Running sp_delete_backuphistory on an active server with hundreds of millions of rows can temporarily lock msdb. Schedule the initial cleanup during low plant activity.
Never Delete System Database Files Manually: Never attempt to delete MSDBData.mdf in Windows Explorer while SQL Server is running or stopped; deleting it destroys all SQL Agent jobs and renders the instance unbootable.
Service Pack Upgrade: Plan an upgrade of Process Historian to 2014 SP3 Upd1, which includes improved database maintenance algorithms.

Engineer FAQ

Why does msdb grow so large in Process Historian?

Every segment backup, validation, and maintenance job logs metadata into msdb backup history tables. SQL Server never deletes these records automatically.

Does purging msdb delete my plant historical process data?

No. Process data is stored in separate database segments (PH#...). Purging msdb only deletes SQL Server backup execution logs and job run histories.

How long does sp_delete_backuphistory take to execute?

If MSDBData.mdf is over 100 GB, the delete query can take 30 to 60 minutes because it cascades across multiple relational tables. Allow it to run to completion.