SQL Database Size

SQL Database Size

avatar
Chuck

Good afternoon.

I was just curious on some SQL size stuff and thought I would ask this while I'm thinking about it.

Yesterday afternoon, I had to upgrade my Teams instance from SQLExpress to SQL 2022 Standard. The DB was just under the 10GB limit with SQLExpress.

After upgrading yesterday, I went through and had to re-apply User Specific Settings, due to the fact some kept getting lost / deleted due to maintenance I had to run on the DB to keep it under the 10GB limit:

delete
FROM UserInfoHistory                  
WHERE DATEDIFF(day, ModifiedDate, GetDate()) >= 3

If I deleted anything less than three days, the DB was still huge and too close to the threshold.

After just applying the User Specific settings to approx. 2600 entries, the DB wen from right at 10GB, to just a little over 31GB just for the UserInfoHistory table:

Is it normal for the DB to grow almost 10MB per entry when making a change to the User Specific Settings?

Is there a way to trim down un-needed things in there like maybe stale transaction logs or something?

Or is this perfectly normal?

Thank you and have a great evening!

--- Chuck

Overgaard, AZ (-7 MST / Zulu Year-Round)

RDM Version: 2026.3.16.0 64-Bit - Free

All Comments (16)

avatar
Stéfane Lavergne

This issue can be resolved in two ways:

  1. Batch save the batch edit of User specific settings - we've created an enhancement request for this.

    1. Currently we save after each causing a UserInfoHisotory row to be created for each save. Say an average of 1024 bytes per override x 2600 saves = approx 3.3 GB of history data. Once batch this you would only have 1 history row of 2.6MB

    2. Note: 1024 bytes was for a simple credential override with username = a & password = a, so depending on how much data your override contains these numbers could be much bigger.

  2. Don't create 2600 overrides but instead set your entries to be inherited and only override at the parent/folder level.

    1. This might not be possible with your data structure.

As for cleaning up your current situation, in theory you only need to keep 1 (or a few) userinfohistory rows per day (not all 2600 rows).

First let's get some stats. Can you please run the following SQL to gets stats from the table

SELECT
    uh.UserInfoID
    ,u.Name
    ,COUNT(*) AS [Row Count]
    ,MAX(LEN(uh.ConnectionOverridesSafe)/1024) AS [Max Size (KB)]
    ,MIN(LEN(uh.ConnectionOverridesSafe)/1024) AS [Min Size (KB)]
    ,AVG(LEN(uh.ConnectionOverridesSafe)/1024) AS [AVG Size (KB)]
    ,SUM(LEN(uh.ConnectionOverridesSafe))/1024 AS [Total Size (KB)]
FROM dbo.UserInfoHistory uh
    LEFT OUTER JOIN dbo.UserSecurity u ON u.ID = uh.UserInfoID
GROUP BY
    uh.UserInfoID
    ,u.Name

Best regards,

Stéfane Lavergne