Shrink the size of Data and log file of MSDB database in SQL Server using SSMS

[aprao]

Overview 

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  1^st 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.

Deleting text report files with Maintenance Cleanup Task in SQL Server Management Studio – Part 2 | ...

 

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

Click OK.

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

Click OK.

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

 

Conclusion

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 => aprao

Please share your thoughts and feedbacks on this blog in a comment.

 

.