Search
Monday, July 16, 2018

Published on Monday, March 21, 2016

DNN SQL Database Housekeeping

Checking table sizes and truncating log files

Disclaimer: You should always backup your database before carrying out maintenance! I take no responsibility if you use any of the scripts/queries on this page and they break your database/site!

Firstly, here is a query that you can run from MS SQL Server Management Studio (SSMS) to list the sizes of all tables in your database:

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 
    TotalSpaceKB DESC

At the top of the result list, you can see any tables that are misbehaving by growing too large!

Usually the EventLog and Exceptions tables are the ones that grow really large. For quite some time now, these tables have foreign key constraints, which means that a simple TRUNCATE TABLE x will not work. Here is a script that does work (source: Sebastian Leupold - see the original here):

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.sys_currentDNNVersion') AND Type = N'FN')
    DROP FUNCTION dbo.sys_currentDNNVersion
GO

-- --------- create tooling: --------- 
CREATE FUNCTION dbo.sys_currentDNNVersion()
    RETURNS Int
AS    
BEGIN
    DECLARE @Vers Int;
    SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM dbo.Version ORDER BY CreatedDate DESC;
    RETURN @Vers;
END
GO

IF dbo.sys_currentDNNVersion() >= 70400 BEGIN
    -- Drop Foreign Key Constraints:
    DECLARE @fkName nVarChar(100) = Null;
    SELECT @fkName = name FROM sys.foreign_keys 
     WHERE parent_object_id = OBJECT_ID(N'dbo.ExceptionEvents')
       AND Object_id IN (SELECT constraint_object_id  
                          FROM  sys.foreign_key_columns F 
                          JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
                          WHERE C.Name = N'LogEventID');
    IF Not @fkName Is Null
        Exec(N'ALTER TABLE dbo.ExceptionEvents DROP CONSTRAINT ' + @fkName +';');

    SET @fkName = Null;
    SELECT @fkName = name FROM sys.foreign_keys 
     WHERE parent_object_id = OBJECT_ID(N'dbo.EventLog')
       AND Object_id IN (SELECT constraint_object_id  
                          FROM  sys.foreign_key_columns F 
                          JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
                          WHERE C.Name = N'ExceptionHash');
    IF Not @fkName Is Null
        Exec(N'ALTER TABLE dbo.EventLog DROP CONSTRAINT ' + @fkName +'')
END
GO

-- Truncate tables:
IF dbo.sys_currentDNNVersion() >= 70400 
  BEGIN
    TRUNCATE TABLE dbo.Exceptions
    TRUNCATE TABLE dbo.ExceptionEvents
    TRUNCATE TABLE dbo.EventLog
  END
ELSE
    TRUNCATE TABLE dbo.EventLog
GO

IF dbo.sys_currentDNNVersion() >= 70400 BEGIN
    -- Recreate Foreign Key Constraints (using common naming):
    ALTER TABLE dbo.ExceptionEvents 
      WITH CHECK ADD CONSTRAINT FK_ExceptionEvents_EventLog 
        FOREIGN KEY(LogEventID)
        REFERENCES dbo.EventLog (LogEventID)
      ON DELETE CASCADE;
      
    ALTER TABLE dbo.EventLog 
      WITH CHECK ADD CONSTRAINT FK_EventLog_Exceptions 
        FOREIGN KEY(ExceptionHash)
        REFERENCES dbo.Exceptions (ExceptionHash)
      ON DELETE NO ACTION;
END
GO

DROP FUNCTION dbo.sys_currentDNNVersion
GO

You might then want to run one or more of the following, depending on the sizes of the tables (usually you won't need to bother, although they can grow quite big sometimes):

TRUNCATE TABLE SiteLog
TRUNCATE TABLE ScheduleHistory

Finally, you might also like to truncate the SearchItemWordPosition table and delete all records in the SearchItemWord, SearchWord and SearchItem tables:

TRUNCATE TABLE SearchItemWordPosition
DELETE FROM SearchItemWord
DELETE FROM SearchWord
DELETE FROM SearchItem

You will need to re-index the site after this by logging in using a Host account, and going to the Search Admin page (Host menu).

Comments (0)Number of views (1632)

Author: ExWeeto

Categories: CMS, DNN, Database, MSSQL

Tags:

Print

Please login or register to post comments.

“If I had eight hours to chop down a tree, I'd spend the first six of them sharpening my axe.”

Abraham Lincoln

Copyright 2018 by Exweeto Terms Of Use Privacy Statement