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