TMS 14.6 - TMSNG database too big, not purging

[Randall White]

I have noticed that my TMS SQL server is running out of disk space. Instead of just throwing more disk space at it, I decided to enable Purging in TMS to remove old data, but it doesn't seem to be doing anything.

Under TMS Server Maintenance, I have enabled all checkboxes in "Purge Old Data in Database Tables Plan" and "Purge Log Plan". After 2 days, the TMSNG file has gotten slightly larger, it's at about 20Gig. This is an external SQL server running MS SQL 2012 Standard Edition, with Simple Recovery Mode enabled.

I ran the findsizeofobjects query in this post: TMS Database Cleaning (thank you Steve Kapinos)

It looks like 95% of the space is used by two tables, objSystemGateCallLog and objEventLog

The unusedSize is about 1% of the dataSize, so it looks like there is actual data in the tables. The server has been in operation for about 5 years, so I assume there are about 5 years of log info in these tables.

What can I do to kickstart the TMS Purge process? Should I try to manually remove old entries from these two tables? I do understand that I will need to run a manual Shrink on SQL to reduce the .mdf file size

Thanks, Randy

[Wayne DeNardi]

For starters, you should consider upgrading your TMS version.  TMS 14.x went End of Support almost a year ago on 31 March 2019.  The latest TMS version is TMS 15.10.0.

You mention that you've ticked all the boxes for the logs in the TMS Web Interface - but you didn't mention whether you have changed the number of days for each of the settings.  If many of the DB entries are recent, then they may not be up for purging yet.

Note that manually clearing the tables (or editing any of the tables directly) is not recommended, and should not be done unless under guidance from the TAC or Cisco TMS BU.

[Randall White]

I wound up having to open a TAC case to manually clean up the databases. Apparently any purge operation on the tmsng.mdf database will temporarily cause the tmsng_log.ldf log database to grow. Since the HDD was already close to full, it couldn't complete the purge operation. Once I added 50GB to the HDD, the "Purging expired data in tables” job worked as expected.

The bottom line is to monitor your SQL disk space and enable purging in TMS long before you run out of disk space.