-- create procedure
CREATE PROCEDURE Hotfix81091 AS
BEGIN
BEGIN TRY
--idx0
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='agent_traffic_aggr_idx0'
AND object_id = OBJECT_ID('agent_traffic_aggr_monitored'))
BEGIN
DROP INDEX agent_traffic_aggr_idx0 ON agent_traffic_aggr_monitored
END
CREATE CLUSTERED INDEX agent_traffic_aggr_idx0 ON agent_traffic_aggr_monitored
(
protocol_id ASC,
ip_address ASC,
port ASC,
timestamp ASC
) ON pguard_data_large
--idx1
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='agent_traffic_aggr_idx1'
AND object_id = OBJECT_ID('agent_traffic_aggr_monitored'))
BEGIN
DROP INDEX agent_traffic_aggr_idx1 ON pguard.agent_traffic_aggr_monitored
END
CREATE NONCLUSTERED INDEX agent_traffic_aggr_idx1 ON pguard.agent_traffic_aggr_monitored
(
timestamp ASC,
group_id ASC
) ON pguard_indx_big
--idx2
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='agent_traffic_aggr_idx2'
AND object_id = OBJECT_ID('agent_traffic_aggr_monitored'))
BEGIN
DROP INDEX agent_traffic_aggr_idx2 ON pguard.agent_traffic_aggr_monitored
END
CREATE NONCLUSTERED INDEX agent_traffic_aggr_idx2 ON pguard.agent_traffic_aggr_monitored
(
timestamp ASC,
ip_address ASC
) ON pguard_indx_big
IF NOT EXISTS(SELECT 1 FROM db_version_history WHERE version = 81091)
BEGIN
INSERT INTO db_version_history(Status, Time, Version, Rollup, Message, Patch) VALUES('applied', pguard.t2d(pguard.CurrentTimestamp()), 81091, 'no', '', 'yes')
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
-- exeucte procedure
BEGIN TRY
EXECUTE Hotfix81091;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;