DNN Clear Log

To clear the DNN Log from DB use the following scripts 

TRUNCATE TABLE sitelog;

delete EventLog

where EventLog.LogGUID in (

SELECT     el.LogGUID

FROM            dbo.EventLog AS el LEFT OUTER JOIN

                         dbo.ExceptionEvents AS ee ON el.LogEventID = ee.LogEventID LEFT OUTER JOIN

                         dbo.Exceptions AS e ON el.ExceptionHash = e.ExceptionHash);

truncate table schedulehistory;

To shrink the DB (minimize the log file -.ldf- size) use the following script 

ALTER DATABASE dbname SET RECOVERY simple; --To change the recovery model

DBCC SHRINKDATABASE (dbname, TRUNCATEONLY); -- shrink database

DBCC SHRINKDATABASE (dbname);

ALTER DATABASE dbname SET RECOVERY full; --To change the recovery model

OR use the following script 

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'[sys_currentDNNVersion]') AND Type = N'FN')

       DROP FUNCTION [sys_currentDNNVersion]

GO

-- --------- create tooling: ---------

CREATE FUNCTION [sys_currentDNNVersion]()

       RETURNS Int

AS    

BEGIN

       DECLARE @Vers Int;

       SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM [Version] ORDER BY CreatedDate DESC;

       RETURN @Vers;

END

GO

       -- Drop Foreign Key Constraints:

       DECLARE @fkName nVarChar(100) = Null;

       SELECT @fkName = name FROM sys.foreign_keys

        WHERE parent_object_id = OBJECT_ID(N'[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 [ExceptionEvents] DROP CONSTRAINT [' + @fkName +'];');

       SET @fkName = Null;

       SELECT @fkName = name FROM sys.foreign_keys

        WHERE parent_object_id = OBJECT_ID(N'[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 [EventLog] DROP CONSTRAINT [' + @fkName +']')

GO

-- Truncate tables:

       TRUNCATE TABLE [Exceptions]

       TRUNCATE TABLE [ExceptionEvents]

       TRUNCATE TABLE [EventLog]

       -- Recreate Foreign Key Constraints (using common naming):

       ALTER TABLE [ExceptionEvents]

         WITH CHECK ADD CONSTRAINT [FK_ExceptionEvents_EventLog]

             FOREIGN KEY([LogEventID])

             REFERENCES [EventLog] ([LogEventID])

         ON DELETE CASCADE;

        

       ALTER TABLE [EventLog]

         WITH CHECK ADD CONSTRAINT [FK_EventLog_Exceptions]

             FOREIGN KEY([ExceptionHash])

             REFERENCES [Exceptions] ([ExceptionHash])

         ON DELETE NO ACTION;

GO

DROP FUNCTION [sys_currentDNNVersion]

GO