/****** 2017-12-17 Version 11 ******/ /****** This script will delete old data ******/ /****** WARNING: Use caution as the script can delete data that you want to keep ******/ /****** If unsure create a separate backup of the database first ******/ /****** EXISTINGDATABASE is an existing database ******/ /****** Search and replace EXISTINGDATABASE with the name of the existing database ******/ /****** Search and replace YYYY-MM-DD with the date where data older will be ******/ /****** deleted from EXISTINGDATABASE ******/ /****** Example of date is 2013-01-01 and ******/ /****** data from the day before will be backed up ******/ /****** Features, Servers, Users, Hosts that no longer has data will NOT be deleted ******/ /****** When ready run Sampling All Records in JTB FlexReport Service Configurator ******/ IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'EXISTINGDATABASE') BEGIN print 'The database EXISTINGDATABASE is not existing. The script will not run.' RETURN END GO PRINT 'Delete from table log' SELECT * FROM [EXISTINGDATABASE].[dbo].[log] WHERE [DateTime]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[log] WHERE [DateTime]<'YYYY-MM-DD' GO PRINT 'Delete from table tblDeniedUserLog' SELECT * FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog] WHERE [LastDenial]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog] WHERE [LastDenial]<'YYYY-MM-DD' GO PRINT 'Delete from table tblPingedHosts' SELECT * FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts] WHERE [PingDate]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts] WHERE [PingDate]<'YYYY-MM-DD' GO PRINT 'Delete from table tblUserCompleteDetailedLog' SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Delete from table tblUserDetailedLog' SELECT [ServerName],[UserName],[UserHost],[AppName],[DateField],[LingerDate],[CheckedOutDate],[LicHandle],[Licenses],[ID],[ProjectID] FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Delete from table tblUserDetailedLogPrev' SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Delete from table UserDetailedLogTemp' SELECT * FROM [EXISTINGDATABASE].[dbo].[UserDetailedLogTemp] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[UserDetailedLogTemp] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Ready' GO