Minimum optimization

It is very important to keep the Lime database maintained to keep it optimized and to prevent performance issues. A maintenance plan aims to assert that the database integrity is not compromised, that the indexes are kept in shape and that the statistics are updated.

Microsoft has a good guide for setting up a maintenance plans.

Ola Hallengren is also a well known SQL Server guru with scripts for maintenance of index, statistics, backups and more

For the bare minimum it's suggested to set up CommandExecute and IndexOptimize and the job manually.

Start a new query in your desired database and from the GitHub link click on the Copy raw contents button in the top right, then paste and execute it in the query to build (or alter if you're updating) the table.

The job is as simple as executing [dbo].[IndexOptimize] and either select the database in the drop-down, or if multiple databases should be checked, configure the @Database line shown below.

It's also suggested to make a step before this that runs the [lsp_cleanupdb] command to avoid logs filling up and locking up the database. If this is already the case, have a look at the next section.

If [lsp_cleanupdb] hasn't been run there's a chance that old deleted objects are still around in Lime and certain logs could be massive. This can cause lock ups and other problems in the SQL database.

Start off by running these three in a new query:

SELECT COUNT (*) FROM transactionlog
SELECT COUNT (*) FROM updatelog
SELECT COUNT (*) FROM relationlog

If the results are in the millions on any, that's a big red flag. To clear them without locking up the entire database with [lsp_cleanupdb], create the script for [csp_mwe_cleanup_trashcan] with the code from the big block at the bottom then execute it with the following:

EXECUTE [dbo].[csp_mwe_cleanup_trashcan]

The script removes logs in batches of 20000 by default and the actual progress can be followed in the messages tab. This can be changed in the section on line 38

SET @batchsize = 20000

GO
/****** Object:  StoredProcedure [dbo].[csp_mwe_cleanup_trashcan]    Script Date: 2020-09-30 10:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ##SUMMARY */
/* ##REMARKS */
/* ##RETURNS 0 if successful.*/
 
/* $Revision: 8 $ */
/* $Modtime: 09-03-27 13:18 $ */
 
CREATE PROCEDURE [dbo].[csp_mwe_cleanup_trashcan]
AS
	-- FLAG_NOTFORREPLICATION --	
 
	SET NOCOUNT ON
 
 
	-- Declarations
	DECLARE @retval INT	
	DECLARE @TRANSACTION INT
    DECLARE @DATE datetime
    DECLARE @TABLE sysname
    DECLARE @SQL nvarchar(4000)
 
 
	-- mwe
	DECLARE @r INT
	DECLARE @batchsize INT
 
	DECLARE @itransactionlog INT
	DECLARE @iupdatelog INT
	DECLARE @irelationlog INT
	DECLARE @c INT
 
	SET @batchsize = 20000
 
 
	-- add infologrecord to detect if and when we started
	INSERT INTO [infologdata] 
	([server],[DATABASE],iduser,lang,activegroups,workstation,[application],[TYPE],[SOURCE],[location],[NUMBER],[message],[TIMESTAMP])
	VALUES
	('SERVER',db_name(),1,'sv','','SQLServer','lsp_cleanup_trashcan',0,'lsp_cleanup_trashcan','Database','3977','lsp_cleanup_trashcan started',getdate())
 
 
	DECLARE @s nvarchar(256)
 
	SET @s = 'csp_mwe_cleanup_trashcan 0.3 by mwe 2020-10-01'
	RAISERROR(@s,0,1) WITH NOWAIT
	SET @s = '-----------------------------------------'
	RAISERROR(@s,0,1) WITH NOWAIT
	SET @s =  'Started ' + CONVERT(nvarchar, getdate(), 121)
	RAISERROR(@s,0,1) WITH NOWAIT
	SET @s =  'Deleting in batches of ' +CONVERT(nvarchar(32), @batchsize) + ' records.'
	RAISERROR(@s,0,1) WITH NOWAIT
 
	-- Set initial values
	SELECT @retval = 0
	SELECT @TRANSACTION = 0
 
 
 
    -- Only cleanup if it isn't a subscriber
    IF @retval = 0 AND dbo.lfn_ismergesubscriber() = 0
    BEGIN
 
        -- Set date limit for record destruction
        IF @retval = 0
            SELECT @DATE = GETDATE() - dbo.lfn_gettrashcanperiod()
 
		SET @s = 'Using trashcan period of ' + CONVERT(nvarchar(32), dbo.lfn_gettrashcanperiod()) + ' days.'
		RAISERROR(@s,0,1) WITH NOWAIT
 
        -- Delete all the removed records older than @date
        IF @retval = 0
        BEGIN
			SET @itransactionlog = (SELECT COUNT(*) FROM [transactionlog] WHERE [TIMESTAMP] < @DATE)
			SET @s = 'Transactionlogs to delete: '+ CONVERT(nvarchar(32),@itransactionlog)
			RAISERROR(@s,0,1) WITH NOWAIT
 
			SET @iupdatelog = (SELECT COUNT(*) FROM [updatelog] WHERE [TIMESTAMP] < @DATE)
			SET @s = 'Updatelogs to delete: '+ CONVERT(nvarchar(32),@iupdatelog)
			RAISERROR(@s,0,1) WITH NOWAIT
 
			SET @irelationlog = (SELECT COUNT(*) FROM [relationlog] WHERE [TIMESTAMP] < @DATE)
			SET @s = 'Relationlogs to delete: '+ CONVERT(nvarchar(32),@irelationlog)
			RAISERROR(@s,0,1) WITH NOWAIT
			RAISERROR('',0,1) WITH NOWAIT
 
            -- Delete transactionlog records
            IF @retval = 0
            BEGIN
				SET @c = 0
				SET @r = 1
				WHILE @r > 0
				BEGIN
					BEGIN TRANSACTION
						DELETE TOP(@batchsize) FROM dbo.[transactionlog] 
						WHERE [TIMESTAMP] < @DATE
						SET @r = @@ROWCOUNT
						SET @c = @c + @r
						SET @s = 'Deleted '+CONVERT(nvarchar(32),@c)+ ' of ' +CONVERT(nvarchar(32),@itransactionlog)+ ' from [transactionlog]'
						RAISERROR(@s,0,1) WITH NOWAIT
					COMMIT TRANSACTION
				END
                SELECT @retval = @@ERROR
            END
 
            -- Delete updatelog records
            IF @retval = 0
            BEGIN
				SET @c = 0
				SET @r = 1
				WHILE @r > 0
				BEGIN
					BEGIN TRANSACTION
						DELETE TOP(@batchsize) FROM dbo.[updatelog] 
						WHERE [TIMESTAMP] < @DATE
						SET @r = @@ROWCOUNT
						SET @c = @c + @r
						SET @s = 'Deleted '+CONVERT(nvarchar(32),@c)+ ' of ' +CONVERT(nvarchar(32),@iupdatelog)+ ' from [updatelog]'
						RAISERROR(@s,0,1) WITH NOWAIT
					COMMIT TRANSACTION
				END
                SELECT @retval = @@ERROR
            END
 
            -- Delete relationlog records
            IF @retval = 0
            BEGIN
				SET @c = 0
				SET @r = 1
				WHILE @r > 0
				BEGIN
					BEGIN TRANSACTION
						DELETE TOP(@batchsize) FROM dbo.[relationlog] 
						WHERE [TIMESTAMP] < @DATE
						SET @r = @@ROWCOUNT
						SET @c = @c + @r
						SET @s = 'Deleted '+CONVERT(nvarchar(32),@c)+ ' of ' +CONVERT(nvarchar(32),@irelationlog)+ ' from [relationlog]'
						RAISERROR(@s,0,1) WITH NOWAIT
					COMMIT TRANSACTION
				END
                SELECT @retval = @@ERROR
            END
 
 
            IF @retval = 0
            BEGIN
                DELETE dbo.[filelog] 
                WHERE [TIMESTAMP] < @DATE
                SELECT @retval = @@ERROR
            END
            IF @retval = 0
            BEGIN
                DELETE dbo.[subscriberlog] 
                WHERE [TIMESTAMP] < @DATE
                SELECT @retval = @@ERROR
            END
			IF @retval = 0
            BEGIN
                DELETE dbo.[procedurelog] 
                WHERE [TIMESTAMP] < @DATE
                SELECT @retval = @@ERROR
            END
 
            -- Delete archived field files
            IF @retval = 0
            BEGIN
                DELETE dbo.[filearchive]
                WHERE [idfile] IN
                    (
                        SELECT [idfile]
                        FROM dbo.[file]
                        WHERE [STATUS] = 2
                            AND [filetype] = 1
                            AND [TIMESTAMP] < @DATE
                    )
                SELECT @retval = @@ERROR
            END
 
            -- Delete field files
            IF @retval = 0
            BEGIN
                DELETE dbo.[file]
                WHERE [STATUS] = 2
                    AND [filetype] = 1
                    AND [TIMESTAMP] < @DATE
                SELECT @retval = @@ERROR
            END
 
            -- Delete attributes for user files
            IF @retval = 0
            BEGIN
                DELETE dbo.[attributedata]
                WHERE [owner] = N'file'
                    AND [idrecord] IN
                    (
                        SELECT [idfile]
                        FROM dbo.[file]
                        WHERE [STATUS] = 2
                            AND [filetype] BETWEEN 100 AND 1000
                            AND [TIMESTAMP] < @DATE
                    )        
                SELECT @retval = @@ERROR
            END
 
            -- Delete archived user files
            IF @retval = 0
            BEGIN
                DELETE dbo.[filearchive]
                WHERE [idfile] IN
                    (
                        SELECT [idfile]
                        FROM dbo.[file]
                        WHERE [STATUS] = 2
                            AND [filetype] BETWEEN 100 AND 1000
                            AND [TIMESTAMP] < @DATE
                    )
                SELECT @retval = @@ERROR
            END
 
            -- Delete user files
            IF @retval = 0
            BEGIN
                DELETE dbo.[file]
                WHERE [STATUS] = 2
                    AND [filetype] BETWEEN 100 AND 1000
                    AND [TIMESTAMP] < @DATE
            END
 
            -- Declare cursor for all user tables
            IF @retval = 0
            BEGIN
                DECLARE usertable__cursor CURSOR READ_ONLY FORWARD_ONLY STATIC FOR
                SELECT [name]
                FROM [usertableview]
                    WHERE [STATUS] = 2
 
                -- Open cursor
                OPEN usertable__cursor
 
                -- Get first user table
                FETCH NEXT FROM usertable__cursor
                    INTO @TABLE
 
                -- Iterate through all user tables 
                WHILE @@FETCH_STATUS = 0 AND @retval = 0
                BEGIN
 
					SET @s = 'Deleting from table ' + CONVERT(nvarchar(64),@TABLE)
					RAISERROR(@s,0,1) WITH NOWAIT
                    SELECT @SQL = N'DELETE dbo.[' + @TABLE + N'] WHERE [status] IN (1, 2) AND [timestamp] < @date'
                    EXECUTE sp_executesql @SQL,
                                        N'@date datetime',
                                        @DATE = @DATE
 
		SET @s = CONVERT(nvarchar(64),@@ROWCOUNT) + ' deleted'
		RAISERROR(@s,0,1) WITH NOWAIT
                    SELECT @retval = @@ERROR
 
                    -- Get next user table
                    FETCH NEXT FROM usertable__cursor
                        INTO @TABLE        
                END
 
                -- Close and deallocate cursor
                CLOSE usertable__cursor
                DEALLOCATE usertable__cursor
            END
        END
    END
 
 
	-- add infologrecord to detect if and when we ended
	INSERT INTO [infologdata] 
	([server],[DATABASE],iduser,lang,activegroups,workstation,[application],[TYPE],[SOURCE],[location],[NUMBER],[message],[TIMESTAMP])
	VALUES
	('SERVER',db_name(),1,'sv','','SQLServer','lsp_cleanup_trashcan',0,'lsp_cleanup_trashcan','Database','3977','lsp_cleanup_trashcan ended',getdate())
 
 
 
	RAISERROR('ALL SYSTEMS MJAU',0,1) WITH NOWAIT 
	PRINT ''
	PRINT 'Ended ' + CONVERT(nvarchar, getdate(), 121)
	RETURN @retval

Minimum optimization

Cleanup Trashcan