How do you configure when TMS purges information from it's database?

[shawn.kennedy30]

Hello All,

I would like to purge the DB on my TMS server and cannot find information on how to do so.

My server is running up against the 4GB limit of the 2005 SQL DB that I have running.  I would like to prevent the server from choking so I have pulled back the number of days TMS stores for it's logs but cannot figure out how to initiate a purge so the DB can make room for new conferences.

I know this happens every night at 12:00am but I would prefer to be able to initiate the purge now.

Is there anyway to do this?  Is there a way to change the default purge schedule time?

Thanks for the help,

Shawn

[Wayne DeNardi]

Under Administrative Tools > TMS Server Maintenance, there's "Purge Old Data in Database Tables Plan" and "Purge Log Plan" sections.  You can define a number of days to keep the data here.  There doesn't seem to be a "do it now" button though, nor a "time of day" setting.

Wayne
--
Please remember to rate responses and to mark your question as answered if appropriate.

[shawn.kennedy30]

I'm specifically looking for a way to "do it now" or some way to manage the "time of day" setting.

Seems strange that there wouldn't be any control over this so I'm thinking there must be some way to do it outside of the TMS Admin webpage, like on the server directly.

Any help would be appreciated.

[Steve Kapinos]

Technically it can be done by running the stored procedures directly... but that's not a supported way of doing things.  Why is this still an issue?  The changes would have been ran by now (7 days later :D)

[shawn.kennedy30]

Thanks for the info Steve.  You're right, the purge has obviously run by now and TMS is back up and functional so I don't have an urgent need to be able to do this.

It would be nice to be able to adjust the number of days to keep logs and then be able to see how much space you're creating by making changes without having to wait a day.

There's also the scenario where you hit the limit and TMS locks up.  It would be good to know that if that happened, I'd be able to resolve it immediately without having to wait until the purge happened that evening.

Anyways, thanks for the info.

[Steve Kapinos]

Yes, it's painful to find out the hard ward your limits have been reached.  Under most circumstances, the default is the databases are configured to grow automatically, so the only limit should be disk space.. which we do report on and have TMS tickets for.

But in the case where there is a hard limit (due to database definition or edition limits) there is the risk of this happening.  With the increased db limits in SQL Express 2008 R2 being 10gigs, the chances of small users hitting that is much less.  But even with the large database size limit, the RAM consumption in SQL 2008 R2 Express is still capped at 1gig.. which will be a huge performance bottleneck for larger databases.  It's strongly suggested that express edition only be used for very small installations.

As for tracking the 'savings', that might encourage some risky behavior where people set the limits based on what will 'fit' vs what they need.  The need should be defined by the business needs, and you should provision your sw/hw accordingly. 

For many of these tables, since you are just deleting rows by date, it's very easy to see how many rows are eligible based on simple select queries - but not as easy to qualify how much disk space that relates to.  Savvy users could query the db to satisfy the curiosity, but I can tell you in my experience with the product (since it's inception)... relying on the row counts shown in the GUI tends to tell me if things are big or small and adjust based on that. Curious cats could look through the stored procedures in the database as well for clues - but those are  not supported ways of doing things :)

[Steve Kapinos · Accepted solution]

You can not manually initiate the jobs from the TMS product interfaces.  They will run based on the schedule automatically in the background.  

To see when they will run, Goto Administrative Tools -> Activity Status and look for the job listed as 'purge expired data in tables'

BTW, if you upgrade to SQL Express 2008 or 2012, the database limit is much higher (10gb).  Plus, 2005 support will be dropped from newer TMS versions shortly.