SQL Server Maintanence¶
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
Minimum optimization¶
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.
Cleanup Trashcan¶
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:
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
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
- Last modified: 23 months ago
- by Viktor Eliasson