TMS Database Cleaning

[Justin Ferello]

Hey all,

My customer is having problems with their TMS, after doing some recon, I found that their tmsng is 93GB!!!!

I explained that this was most likely the cause.

The problem being is that we cannot get to the TMS Server Maintenance page, since it times out.

Are there some backdoor SQL scripts we can run against the DB to remove all the call records?

Thanks,

Justin

[Steve Kapinos]

It would be helpful to know which tables are the biggies... if you counted the rows and size of the different tables.  There has to be something abnormal going on for the database to get to that size... even customers running for years with large networks do not get to that size.

You also need to tell us the TMS version as it's relevant to some of the db upkeep.

The db maintenance is done by stored procedures which you can see by browsing the database itself... but I'm hesitant to give directions on this topic just out on the web because it can be very much a 'try, adapt, try again' type of situation given the table sizes and specifics in play here.  Usually I do this live by analyzing what is in play.  There have been some defects that can lead to explosive growth in the ticket table for instance.

Are you versed at all with SQL and running stored procedures?

Attached is a script you can run in the database and it will report on the size and # of rows in each of the tables

[Steve Kapinos]

Thinking more... this is a 'safe' way to let the tools do the job themselves

You can try enabling all the database maint procedures by toggling them on in the database.  This has the same effect as enabling them in the GUI...  but it will not initiate the jobs, just tell them to be enabled, so they will start to run during the nightly job

Run this sql script against your database to enable all the db jobs at their existing duration

update tmsng.dbo.DatabaseAndLogsMaintenance set Enable = 1 where Description like 'Table_%'

But note the jobs do not attempt to purge all possible data in one pass due to transaction sizes, timeouts, etc.  So if left to its own, it would decline over time.

Some questions to answer to check for easy outs....

What is 93gigs?  Their data file (the .mdf) or their log file (the .ldf)?  Is sql reporting that much space in USE or just the file sizes?

Are they running simple or full recovery on the database?

[Justin Ferello]

Steve,

Thank you so much for the quick reply.  The TMS version is 13.2.2, sorry, I always forget that.

I ran the scripts you gave me, changed all their maintenance to 1 too.

Looks like these tables are the offenders:

objSystemGateCallLogResolvedSystem	66701040	1391992 KB	1386008 KB	5352 KB	632 KB
objSystemGateCallLog	189402389	90848816 KB	86261272 KB	4576120 KB	11424 KB

So I guess the maintenance will run tonight, do you think it will delete this stuff or do we need to manually prune those tables?

Thanks,

Justin

[Steve Kapinos]

I apologize for a late reply  - but hopefully by now you have some progress

looking at the code, it should delete 10k rows at a time, and keep looping through until there are no more to delete.

Then you'll need to shrink your database files to get the file size back down and reclaim the disk space

I couldn't tell from your snapshots if the database was in simple or full recovery mode.  If in full recovery, your log file is going to balloon with all these deletes.  you should consider switching to simple recovery mode at least temporarily until the database is back to normal size.  Then you don't have to worry about the log file getting out of control mid-process