Can SC_TICKET* keys be purged from the Properties table, and have tickets been moved elsewhere?

Question

I'm troubleshooting an issue with slow logins on a Sitecore instance.

As part of that investigation I've found a number of posts - such as Sitecore client and logon is very slow (properties table AGAIN), Sitecore.Web.Authentication.c__DisplayClass5.b__2(Ticket ticket) +52, and an answer on Sitecore Client login Timeout error - Azure PaaS - that point to the Sitecore_Core.dbo.Properties table and rows with a Key of SC_TICKET*.

Looking at our instance's database I see a number of rows, however there are no new records since we upgraded to 9.0.2 (July of last year).

Keeping in mind the usual best practices when touching a third-party system's database, is there any issue with purging these keys from the database?

Given that no new records are being added after logging in, are tickets in 9.x+ now stored in a different table?

Answer 1

A little background. The CleanupAuthenticationTicketsAgent should be cleaning up any expired tickets for you. But due to a bug in 9.0.x, this probably isn't happening. This is alluded to in the SSE answer you linked to. The longer term fix is definitely to make sure the agent is cleaning up the tickets correctly. Support can confirm whether https://github.com/SitecoreSupport/Sitecore.Support.223702 is appropriate for your solution.

Keeping in mind the usual best practices when touching a third-party system's database, is there any issue with purging these keys from the database?

100% agree with the sentiment, this should only be used as an intermediary solution (as advised by support) before the long term solution (apply a fix for CleanupAuthenticationTicketsAgent) is applied. Obviously it's hazardous if you delete the wrong properties, so apply appropriate caution. That said, there is no issue with deleting the tickets but please note any users currently logged in with valid "remember me" functionality will be required to login next time. To avoid this issue, you can try to just clean up really old tickets by manipulating the SQL query to filter by date (the date the ticket is created is in the value string).

--EG. This will select tickets created in DEC 2018    
USE {core_database_name};
SELECT TOP 10 * FROM [dbo].[Properties]
WHERE [dbo].[Properties].[Key] like '%SC_TICKET%'
AND [dbo].[Properties].[Value] like '%^201812%';

Given that no new records are being added after logging in, are tickets in 9.x+ now stored in a different table?

Sounds like you sorted this one. The key in the properties table is just named slightly differently in different versions of Sitecore. ie. (CORE_SC_TICKET%). If you've come down an upgrade path there may be some with a different naming convention.

I actually hit this issue recently too and blogged (just a few days ago!) about that specific case at https://devsencore.com/2019/02/06/sitecore-9-slow-login-clean-up-your-tickets/

Answer 2

I know this is kinda old now, and just to supplement @GeorgeTucker's response, here is a maintenance script I have been using for this cleanup. Basically these tickets control the "Remember Me" feature of the authentication module.

select [key] 'k', SUBSTRING( [value], CHARINDEX( '^', [value], CHARINDEX( '^', [value] ) + 1 ) + 1, 16 ) 'ExpiryDate' 
into ##Tickets 
FROM [Properties] 
where [Key] like 'CORE_SC_TICKET%'

/* Optional backup!
select * into [PropertiesBackup] from [properties]
*/

begin tran
-- remove virtual user tickets older than 1 day
delete p 
from
    [Properties] p
    JOIN [##Tickets] t on p.[Key] = t.k
where
    p.[Value] like '%sitecore\virtualssuser%^'
    AND CAST( SUBSTRING(ExpiryDate,1,4)+'-'+SUBSTRING(ExpiryDate,5,2)+'-'+SUBSTRING(ExpiryDate,7,2) AS datetime ) < DATEADD( DAY, -1, GetDate() )

-- remove all tickets older than 14 days
delete p 
from
    [Properties] p
    JOIN [##Tickets] t on p.[Key] = t.k
where
    CAST( SUBSTRING(ExpiryDate,1,4)+'-'+SUBSTRING(ExpiryDate,5,2)+'-'+SUBSTRING(ExpiryDate,7,2) AS datetime ) < DATEADD( DAY, -14, GetDate() )

rollback
-- commit

drop table ##Tickets
alter table [Properties] rebuild

Note that the actual delete is guarded by a begin tran which is rolled back by default. This is just my habit so if I accidentally run the script I don't do damage.

You will need to select and F5 the commit step manually (or adjust the script if you like to live dangerously, I guess).

Answer 3

The answer to the second part of the question is that they are still in the Sitecore_Core.dbo.Properties table, but now have a CORE_ prefix.

In our particular case it appears the following Keys have been used, sorted by age:

• SC_TICKET
• SC_TICKET_%
• SC_TICKET__%
• CORE_SC_TICKET__%

The following C# Statement(s) using LINQPad connected to the Sitecore_Core database can help review the tickets:

var tickets = Properties
    .Where(p => p.Key.Contains("TICKET"))
    .ToList()
    .Select(p => new
    {
        p.ID,
        p.Key,
        p.Value,
        Values = p.Value.Split('^')
    })
    .Select(t => new
    {
        Date = t.Values[2],
        User = t.Values[1],
        t.Values,
        t.ID,
        t.Key,
        t.Value
    })
    ;

tickets
    .OrderBy(t => t.Date)
    .Dump();