T_Web tables and maintenance

Hi Damien,

Good day.

I have checked those two tables. If you really want to clean up the table, you need to delete the item in t_web_session_Variable first, then delete items in t_web_session_Session.  that is all.

you cannot delete a PK item when it is used as FK in another table.

Have fun.

Ben

I am getting ready to schedule this utility.  We have over 8 years of data, we are thinking of keeping only a years worth of data. Do you think it is a good idea to phase the purges or just go with the one purge.

Thanks!

Jon

Hi Jon,

We are facing this same problem . Our t_web_session_session and t_web_session_variable tables are huge and we are trying to run the UP_CLEAR_WEB_TABLES procedure without much luck.

Basically the procedure runs really slow (given the enormous size of the two tables) and in the end fills up our Impresario Transaction log file and/or consumes all available disk space on the server.

I guess you would have faced similar issues cleaning up these tables having 8 years old data...? Would much appreciate if you can share your experience and how you managed this process.

Many thanks,

Sujit.

Hi Sujit

We faced this issue earlier this year. My colleague basically sat on our test server and ran the stored procedure for the early years and then realised when we got to the later ones that he could only do 3 months at a time.

We then set up a local table to store the days old value in.

CREATE TABLE [dbo].[lt_clean_web_days](
[last_value] [int] NOT NULL
) 

We manually removed the lder years, then set the last_value to be 1800, then wrote a job with the main step being

DECLARE @days INT

SELECT @days = last_value
FROM lt_clean_web_days

EXEC Up_clear_web_tables @days_old=@days

SELECT @days = CASE
                  WHEN @days < 270 THEN 180
                  ELSE @days - 90
              END

UPDATE lt_clean_web_days
SET last_value = @days

Hi Mark,

Thanks for your response.

I much appreciate you sharing your SQL script here. That's a clever way of doing it.

You're right, decrementing the @days_old parameter by around 90 days might do the trick for the later years. In our testing we are getting similar outcomes, wherein deletion in small chunks seems more efficient.

Also part of the problem for us is that our database size is huge, and running a report on the database tables shows that the T_WEB_SESSION_SESSION and T_WEB_SESSION_VARIABLE tables are occupying most of it.

Do you know if you had the same problem, and if yes, was the size of the DB automatically reduced after running the clean up procedure? Or did you have to manually run some shrink commands?

Thanks for your cooperation.

Best,

Sujit.