How to Truncate Your Umbraco Log Table in MS SQL

If you are using Umbraco and have not changed the default logging settings then your database can grow in size fairly quickly. This article shows you how to clear your Umbraco log table directly in your websites MS SQL database.

You will need to do this via Microsoft SQL Server Management Studio (SSMS), which if you do not have installed can be found here. You will also need to open up the firewall to allow you to connect to the SQL Server, this is shown here.

Follow these steps to truncate your Umbraco Log table.

  1. Open Microsoft SQL Server Management Studio and connect to your database. Details of the IP address and username/password can be found in your 'Space Summary'.
  2. Click on the 'New Query' button. Using F5 or the 'Execute' button to run each script.
  3. You can view your table sizes within your Umbraco database, to determine if the umbracoLog table is the reason for your database size, by using the following script snippet:
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
        t.NAME NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255 
    GROUP BY t.Name, s.Name, p.Rows
    ORDER BY SUM(a.total_pages) * 8 DESC
  4. Now use the following script to truncate your tables. If you have multiple SQL databases then put Using [YourDatabaseName] before
    truncate table umbracoLog
  5. If you have removed a large percentage of your data you may want to shrink your database to decrease the physical size using the following script:
    DBCC SHRINKDATABASE ([YourDatabaseName], 10);

Return to Category