h0FnJEX0

1/**************************************************************************************************************************************************
2IndexOptimize 10.0 | Approved for Relativity versions 9 and higher | SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017
3This is a smart script to eliminate index fragmentation and update statistics across Relativity databases.
4
5Excludes working on the following databases:
6	Workspaces with a status containing the words Inactive, Archive, Loading, Import, or Export
7	Workspaces that have not been accessed by a user since the last IndexOptimize maintenance run
8
9-Setup Instructions-
10
11Step 1: Schedule this script (IndexOptimizeJob.sql) to run nightly on each active Relativity SQL instance.
12			Workspace Specific Version Instructions - Specify database(s) to include in maintenance on line 33
13Step 2: Execute the other script (IndexOptimizeObjects.sql) once on each active Relativity SQL instance to create the required tables, functions, and stored procedures.
14
15This script uses the free SQL Server Maintenance Solution provided by Ola Hallengren.
16Index and Statistics Maintenance: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
17
18Maintenance history is logged to the [Master].[dbo].[CommandLog] table on each SQL instance.
19***************************************************************************************************************************************************/
20
21SET NOCOUNT ON
22
23IF OBJECT_ID('tempdb..#WorkspacesToWorkOn') IS NOT NULL DROP TABLE #WorkspacesToWorkOn
24
25DECLARE @WorkspaceSpecific INT = 0
26--create temp table to house names of databases to be worked on
27CREATE TABLE  #WorkspacesToWorkOn (DBName varchar(25))
28
29/*** Workspace Specific Instructions***
30Uncomment the next two lines and specify the database you only want maintenance to run on.
31If there is more than one database, create additional INSERT INTO statements for each database you want to perform maintenance on. */
32--SET @WorkspaceSpecific = 1
33--INSERT INTO #WorkspacesToWorkOn VALUES('EDDS#######')
34
35IF @WorkspaceSpecific = 0 AND EXISTS(SELECT TOP 1 name FROM sys.databases (NOLOCK) WHERE [state] = 0 AND [name] LIKE 'EDDS%' AND [name] NOT IN('EDDS', 'EDDSResource','EDDSPerformance', 'EDDSQoS', 'EDDSArchiving', 'ARMResource', 'EDDSLogging', 'EDDSMetrics'))
36BEGIN
37	--automatically determine which active Relativity SQL instance is the "Master" housing the EDDS database
38	DECLARE @servers TABLE(n NVARCHAR(MAX))
39	INSERT @servers SELECT [name] FROM sys.servers (NOLOCK)
40	DECLARE @serverName NVARCHAR(MAX)
41	DECLARE @sql NVARCHAR(MAX)
42	DECLARE @dbs TABLE(n NVARCHAR(MAX))
43	DECLARE @masterSql NVARCHAR(MAX)
44	WHILE EXISTS(SELECT * FROM @servers) BEGIN
45	SET @serverName = (SELECT TOP 1 n FROM @servers)
46
47	SET XACT_ABORT ON
48	BEGIN TRAN
49	BEGIN TRY
50	 
51		  SET @sql = 'SELECT [name] FROM [' + @serverName + '].master.sys.databases WHERE [name] = ''EDDS'''
52		  INSERT @dbs EXEC SP_EXECUTESQL @sql
53		  IF EXISTS(SELECT * FROM @dbs) SET @masterSql = @serverName
54		  DELETE FROM @dbs
55		  DELETE FROM @servers WHERE n = @serverName
56
57	COMMIT TRAN
58	END TRY
59
60	BEGIN CATCH
61	IF @@TranCount > 0
62	BEGIN
63		  ROLLBACK TRAN
64		  DELETE FROM @servers WHERE n = @serverName
65	END
66	END CATCH
67	END
68
69	IF @masterSQL IS NULL
70	BEGIN
71	RAISERROR('@masterSQL not set, login to SSMS as SA and create the maintenance plan or move the entire T-SQL script into just a T-SQL job step and do not use a maintenance plan.', 20, -1) WITH LOG
72	END
73
74	--populate temp table with Relativity database names excluding those with a Status containing the words Inactive, Archive, Loading, Import, or Export
75	DECLARE @sql2 NVARCHAR(MAX)
76	SET @sql2 = '
77	INSERT INTO 
78		#WorkspacesToWorkOn
79	SELECT
80		''EDDS'' + CAST(ArtifactID AS varchar(12))
81	FROM
82		[' + @masterSql + '].EDDS.eddsdbo.[Case] WITH (NOLOCK)
83	WHERE
84		''EDDS'' + CAST(ArtifactID AS varchar(12)) IN (SELECT [Name] FROM sys.databases WHERE [state] = 0)
85		AND NOT ArtifactID IN (SELECT ArtifactID FROM [' + @masterSql + '].EDDS.eddsdbo.[Case] WITH (NOLOCK)
86		WHERE StatusCodeArtifactID IN (SELECT ArtifactID FROM [' + @masterSql + '].EDDS.eddsdbo.[Code] WITH (NOLOCK) WHERE CodeTypeID = 7
87		AND (
88		[Name] LIKE ''%Inactive%'' OR 
89		[Name] LIKE ''%Archive%''  OR
90		[Name] LIKE ''%Loading%''  OR
91		[Name] LIKE ''%Import%''   OR
92		[Name] LIKE ''%Export%''
93		))) AND RootFolderID != 0
94		--AND ArtifactID NOT IN (''#######'')  --exclude additional databases here if needed
95	'
96	EXEC SP_EXECUTESQL @sql2
97END
98
99	--exclude databases from maintenance that have not been accessed since the last job run
100	DECLARE @DBName varchar(25)
101	DECLARE DBCursor CURSOR
102	FOR SELECT * FROM #WorkspacesToWorkOn
103	OPEN DBCursor
104
105	FETCH NEXT FROM DBCursor INTO @DBName
106	WHILE  @@FETCH_STATUS = 0
107	  BEGIN
108		  EXEC('
109			DECLARE @ID INT
110			DECLARE @LastRunTime DateTime
111
112			SELECT @LastRunTime = MAX([StartTime])
113			FROM [master].[dbo].[CommandLog]
114			WHERE [DatabaseName] = ''' + @DBName + '''
115			AND [CommandType] IN (''ALTER_INDEX'', ''UPDATE_STATISTICS'')
116			SET @ID = (SELECT MAX(ID) FROM ' + @DBName + '.eddsdbo.[AuditRecord_PrimaryPartition] WITH (NOLOCK) WHERE Action <> 3 AND RequestOrigination <> N''Procuro'') --excluding action types of 3 for any auto batch audit entries
117
118			IF ((SELECT [TimeStamp] FROM ' + @DBName + '.eddsdbo.[AuditRecord_PrimaryPartition] WITH (NOLOCK) WHERE ID = @ID) < @LastRunTime AND @LastRunTime is not null AND @ID is not null) -- "is not null" check will only be true for new workspaces, or the first run of this script
119			BEGIN
120				  DELETE FROM #WorkspacesToWorkOn
121				  WHERE [DBName] = ''' + @DBName + '''
122			END
123	')
124		FETCH NEXT FROM DBCursor INTO @DBName
125	  END
126	CLOSE DBCursor
127	DEALLOCATE DBCursor
128
129IF @WorkspaceSpecific = 0 
130BEGIN
131	--populate temp table with EDDS, EDDSResource, EDDSPerformance, Invariant, RelativityImaging, EDDSQoS, EDDSLogging, and EDDSMetrics databases
132	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDS') 
133			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDS')
134
135	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDSResource') 
136			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDSResource')
137
138	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDSPerformance') 
139			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDSPerformance')
140
141	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'RelativityImaging')
142			INSERT INTO	#WorkspacesToWorkOn	VALUES ('RelativityImaging')
143
144	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDSQoS')
145			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDSQoS')
146
147	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDSLogging')
148			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDSLogging')
149
150	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'EDDSMetrics')
151			INSERT INTO	#WorkspacesToWorkOn	VALUES ('EDDSMetrics')
152
153	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME = 'Invariant')
154			BEGIN
155			INSERT INTO	#WorkspacesToWorkOn	VALUES ('Invariant')
156				IF EXISTS (SELECT TOP 1 * FROM [master].[dbo].[CommandLog] (NOLOCK))
157					INSERT INTO	#WorkspacesToWorkOn
158					SELECT DISTINCT [StoreName] FROM [Invariant].[dbo].[Jobs] (NOLOCK) WHERE [LastProgress] > (SELECT MAX([StartTime]) FROM [master].[dbo].[CommandLog] (NOLOCK))
159						ELSE
160							INSERT INTO	#WorkspacesToWorkOn
161							SELECT DISTINCT [StoreName] FROM [Invariant].[dbo].[Jobs] (NOLOCK) WHERE [LastProgress] > DATEADD(WEEK,-1,GETDATE())
162			END
163	
164	--populate temp table with Collection databases
165	IF EXISTS (SELECT [Name] FROM SYS.DATABASES WHERE NAME LIKE '%CLCT%')
166			INSERT INTO #WorkspacesToWorkOn
167			SELECT DISTINCT [Name] FROM SYS.DATABASES WHERE NAME LIKE '%CLCT%'
168
169END
170
171DECLARE @databaseList varchar(max)
172SELECT @databaseList = COALESCE(@databaseList + ', ', '') + (list.database_name)
173FROM (
174		SELECT '[' + [dbname]+ ']' as database_name FROM #WorkspacesToWorkOn) list
175
176IF @databaseList is not null
177BEGIN
178
179/**************************************************************************************************************************************************
180Below are adjustable settings that are detailed here: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
181
1821.	Statistics sample – We have found that Relativity data can be sensitive to sample size. Computing statistics with an auto generated sample
183	doesn’t always work well for large tables so ideally we’d like to use full scan (100). It does take longer and results in greater I/O utilization
184	but the results are better. If you do update statistics with a full scan then you need to see point 2 below.
185
1862.	What statistics to update on a daily basis and how - In a large environment, if you update all statistics, it could take a really long time,
187	hammer I/O, and possibly require increased tempdb space. You might be unhappily surprised when running Ola’s job using the “ALL” parameter along
188	with a full scan (100). So you might instead decide to update ALL statistics with a full scan (100) once a week. This would require two seperate
189	maintenance plans be setup and scheduled.
190
191We've separated the statistics update from the re-indexing process below.
192***************************************************************************************************************************************************/
193
194	--Index Fragmentation Maintenance
195	EXECUTE [master].dbo.IndexOptimize @Databases = @databaseList,
196	@FragmentationLow = NULL,
197	@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
198	@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
199	@FragmentationLevel1 = 10, -- default is 5
200	@FragmentationLevel2 = 40, -- default is 30
201	@MinNumberOfPages = 100, -- default is 1,000
202	@SortInTempdb = 'Y', -- default is N
203	@LogToTable = 'Y'
204
205	--Statistics Maintenance
206	EXECUTE [master].dbo.IndexOptimize @Databases = @databaseList,
207	@UpdateStatistics = 'ALL',
208	@OnlyModifiedStatistics = 'Y',
209	--@StatisticsSample=100,
210	@LogToTable = 'Y' 
211
212END
213
214--audit table cleanup
215DELETE FROM [master].[dbo].[CommandLog] WHERE [StartTime] < DATEADD(MONTH,-6,GETDATE())
216PRINT 'History is logged to the [master].dbo.CommandLog table of each Relativity SQL instance'
217
218--legacy audit table cleanup
219IF OBJECT_ID('[EDDSResource].[eddsdbo].[IndexOptimizeAudit]') IS NOT NULL DROP TABLE [EDDSResource].[eddsdbo].[IndexOptimizeAudit]
220IF OBJECT_ID('[EDDSResource].[eddsdbo].[IndexOptimizeAudit1]') IS NOT NULL DROP TABLE [EDDSResource].[eddsdbo].[IndexOptimizeAudit1]
221IF OBJECT_ID('[EDDSResource].[eddsdbo].[IndexOptimizeAudit2]') IS NOT NULL DROP TABLE [EDDSResource].[eddsdbo].[IndexOptimizeAudit2]
222IF OBJECT_ID('[EDDSResource].[dbo].[CommandLog]') IS NOT NULL DROP TABLE [EDDSResource].[dbo].[CommandLog]
223
224IF EXISTS (SELECT DBName FROM #WorkspacesToWorkOn WHERE DBName NOT IN (SELECT name FROM sys.databases (NOLOCK) WHERE [state] = 0))
225BEGIN
226RAISERROR('One or more workspaces specified for inclusion into workspace specific maintenance does not exist or is inaccessible.', 20, -1) WITH LOG
227END
228
229/*This script is intended for use with the Relativity system created and distributed by Relativity ODA LLC.
230Please contact Relativity Support at support@relativity.com or (312) 676-5099 for assistance.*/