SQL Queries For Archiving The Logs For The Timeframe And Using New Table As Logs Table

News Knowledge Base
,
1

How to archive the huge data more than 50Million records of dbo.logs table easily and save the time of deleting the older logs?

Due to less or no DB maintenance the dbo.logs table sometimes contains huge amount of data may be more than 50million and archiving those logs will take more time. To reduce the time of maintenance activity you can create a new dbo.Logs table and move only the latest records for last 30 or 60 days.



Execute the below query:

Select MIN(Id),MAX(Id),MIN(Timestamp), Max(Timestamp),count(*)

From [databaseName].[dbo].[Logs] (NOLOCK)

where 1=1

And CONVERT (varchar(10),[Timestamp],120)>=DATEADD(DAY,-40,CURRENT_TIMESTAMP)

And CONVERT (varchar(10),[Timestamp],120)<=DATEADD(DAY,-2,CURRENT_TIMESTAMP)

Note: 40 & 2 are the days mentioned to get the records for those timeframes. Modify according to the requirements.

Output looks like below:

(No column name)

(No column name)

(No column name)

(No column name)

(No column name)

159

243

2021-10-11 13:51:03.323

2021-11-12 19:00:13.550

85

ID from where record will be copied to new logs table

ID upto where records will be copied to new logs table

From Timeframe

To Timeframe

No. of rows to be moved in new logs table

Now, create a new logs table by following the steps below:
  1. Right click dbo.logs table-> Script Table As -> Create To-> New query editor window
  2. A script will be generated for creating a new log table.
  3. Replace the below two line with new table as dbo.Logs_New
    • CREATE TABLE [dbo].[Logs] to CREATE TABLE [dbo].[Logs_New]
    • CONSTRAINT [PK_dbo.Logs] to CONSTRAINT [PK_dbo.Logs_New]
  4. Execute the query & refresh the database. A new table dbo.Logs_New will be generated.


Execute the below query to insert the logs from old table to new logs table:

SET IDENTITY_INSERT [DatabaseName].[dbo].[Logs_NEW] ON;

INSERT INTO [DatabaseName].[dbo].[Logs_NEW]

([Id],[OrganizationUnitId],[TenantId],[TimeStamp],[Level],[WindowsIdentity],[ProcessName],[JobKey],[RobotName],[Message],[RawMessage],[MachineId],[UserKey],[HostMachineName])

SELECT *

FROM [DatabaseName].[dbo].[Logs] (NOLOCK)

WHERE Id >= 159 And Id<=243

SET IDENTITY_INSERT [DatabaseName].[dbo].[Logs_NEW] OFF;

Now rename your old logs table from dbo.Logs to dbo.Logs_Old and rename new logs table from dbo.Logs_New to dbo.Logs.

The logs will be written in new logs table from now onwards.