How To Truncate Your DNN Logs In MS SQL

If you are using DNN (DotNetNuke) 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 DNN log tables 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 DNN Logs.

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 DNN database, to determine if the eventlog, sitelog and schedulehistory tables are the reason for your database size, by using the following script snippet:

   
   SELECT   
       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
   WHERE 
       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 eventlog
   truncate table sitelog
   truncate table schedulehistory
   

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);
   GO
   

Return to Category