Set = 'DBCC CHECKIDENT ('' dbo.Log'', RESEED, ' + cast ( as varchar ) + ')' Set = IDENT_ CURRENT ( ' dbo.Log_1month' ) Change the identity of the table to continue from the ID the other one was Print the table log_2month has data newer than 2 months, which is the retention period, the process is not executedĮXEC sp_rename ' dbo.Log_2month', 'Log_new' ĮXEC sp_rename ' dbo.Log_1month', 'Log_2month' ĮXEC sp_rename ' dbo.Log', 'Log_1month' Checking what is the newest data in the table log_2month: SET = cast ( DATEADD ( month ,- 2, CURRENT_TIMESTAMP ) AS DATE ) We will remove everything that is older than 2 months, so that is going to be the limitDate: Create a view to select the 3 tables if necessary, as for example: CREATE INDEX Log_2month_insert_time ON Log_2month (insert_time )
CREATE INDEX Log_1month_insert_time ON Log_1month (insert_time ) CREATE INDEX Log_insert_time ON Log ( insert_time ) Create a new index for insert_time on the tables. Create two additional tables to store the older data per month, as for example: Log_1month, Log_2month We need to take this in consideration when performing this change. However, if the application also needs to read the data, it may be necessary to make a few changes to have It read from the view or It would only show the most recent data (< 1 month).
If the application only writes to this table and It is only read manually when necessary, it may not be necessary application changes. We can have a view with the union all of the 3 tables if necessary. We could switch between 3 tables once a month and truncate the one that contains the older data. Store the data by month in different tables.We will perform the clean-up task once a month and delete everything that is older than 2 months. The only index present is the primary key PK_Log on ID.
I have provided some examples, but please keep in mind you should implement your own process and test It in a non-production environment first.įor a simple demonstration, we will consider a table called ‘Log’ which may contain several columns, but the ones we are most interested in are: ‘ID’, that is an int identity(1,1) column and ‘insert_time’, which is a datetime column that stores when the data was inserted. I will show two options to achieve this, one using multiple tables and other using a partitioned table, which will not only improve the performance, but also reduce transaction log consumption, table fragmentation and eliminate the need to scale up the database and reduce the costs or prevent increasing It.
0 kommentar(er)
