Shrink the size of Data and log file of MSDB database in SQL Server using SSMS
Alert appearing in FRUN or CCMS (Tx code RZ20) as shown below,
that Filesystem was full and used space was 98% in the C: drive in the server host.
Login to windows server “<hostname>” .
As shown above, file size for MSDBData.mdf was approaching to 4.2 GB (normally it should not grow beyond 500MB) and MSDBLog file size used is 6.782GB (usually, it should not go past 100MB ).
MSDB is the schema database. This Database has two files Data and Log (Transaction log).
This indicates that 10.8Gb was used by MSDB database (it cannot go above 600MB).
The solution is to shrink the size of Data file and log file of MSDB database. This is done using SSMS (SQL Server management Studio available in the Windows Server).
The procedure for downsizing the MSDB database will be covered in this article
Secondly, as shown below
As shown above, these are txt files that contains sql statements.
Total number of txt files created by the SQL agent job prior to 1st Jan 2023 are 46123 files out of 56,500 files.
Do you keep discarded clothing or old card boxes in your room? NO, is the answer.
DO you want to build new rooms or increase the size of the current ones? NO, is the answer.
In a similar vein, it serves no purpose to retain old files created before January 1, 2023.
The LOG folder of the MS SQL Server on the C: disk needs to be cleaned out on a regular basis to get rid of old, unneeded txt files.
.It is expensive to add more disks and enlarge existing disk space.
The process to remove these outdated, useless txt files was covered in the link given below.
The purpose of MSDB database
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.
Procedure to reduce the size of Data file and log file of MSDB database.
This is done using SSMS (SQL Server management Studio available in the Windows Server).
Open SQL studio as shown below
“(DO not shrink other databases such as <SID> , master, model, tempdb)”
<SID> is the <SID> SAP Schema Database. NEVER perform shrink of <SID> Data and Log file.
Right click mouse on msdb , select Tasks–>Shrink–>Files
It takes few minutes to complete process.
MSDB Data file size was reduced from 4Gb to 900MB.
Repeat the procedure for MSDBLOG file as shown below
After few minutes, size of the of the msdblog file was reduced from 6.6GB to 1Mb
After shrink operation completed, new Disk space as shown below
As a result, the C: drive has more free space available and the free space rose from 1.2 GB to 12 GB as shown below
This procedure is done online. No need to restart the instance. Also it does not impacts the ongoing business transactions in SAP application.
Thanks for reading!
Follow for more such posts by clicking on FOLLOW => Prasad Rao
Please share your thoughts and feedbacks on this blog in a comment.