CODEWITHSUNDEEP
sql-serverdatabasearchivesql-server-2012-express
Chen_Dogg
Chen_Dogg

Reputation: 91

Archiving scripts to run daily on SQL Server 2012 Express in order to maintain good performance

I have a situation whereby I recieve many rows of transactional data from a hardware device. I analyze this data and turn the previous day's data into a report. As a result, my application is often combing the entire table to generate reports, but it only really needs to deal with the previous 72 hours data. I run an indexing script daily (rebuilds and re-organizes), and running this on tables in the same database causes the database to hang quite a bit for my report app. So, My thinking is to get the archived data out into a new database that can be managed seperately to this database, thus reducing the hang-time of this database due to re-organizing and re-indexing multiple indexes of a database consisting of millions of rows.

I currently have a script (below) that moves data into a seperate table that is older than 7 days old. I plan to change this to 3 days old, but I want to put this in a separate database for performance reasons. The script only deals with sets of 1000000 (just over the number of rows coming in each day)

As you can see, it is bound to one database. How do I make the script below use multiple databases? I have hacked around but with no luck. The first script is the original, and the second is my attempt to change and make use of the 2nd database. The names are activetrackdb (original) and activetrackarchivedb (new archive database). The archive db has identical coloumns, and it's own primary key.

USE activetrackdb;
DECLARE @3daysago datetime
SELECT @3daysago = DATEADD(d, -7, GetDate());
SET IDENTITY_INSERT [dbo].[Archived Data Import] ON;
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [dbo].[Data Import] 
    WHERE [Data Import].[Receive Date] < @7daysago)
DELETE CTE
  OUTPUT DELETED.id, 
  DELETED.[Company id], 
  DELETED.[Site id],
  DELETED.[Site name],
  DELETED.[Receive date],
  DELETED.[Detect date],
  DELETED.[Type],
  DELETED.[eventType],
  DELETED.[Tag name],
  DELETED.[Tag RFID],
  DELETED.[Route id],
  DELETED.[Route name],
  DELETED.[Patrol id],
  DELETED.[Device s/n],
  DELETED.[Guard id],
  DELETED.[Guard name],
  DELETED.[Employee id],
  DELETED.[Employee name],
  DELETED.[Alarm group id],
  DELETED.[Comment],
  DELETED.[GPS latitude],
  DELETED.[GPS longitude],
  DELETED.[GPS accuracy],
  DELETED.[GPS vertical accuracy],
  DELETED.[GPS height],
  DELETED.[GPS speed],
  DELETED.[GPS heading],
  DELETED.[Additional data],
  DELETED.[Device id],
  DELETED.[Device name],
  DELETED.[Company name],
  DELETED.[Occurrence date],
  DELETED.[GPS date] 
  INTO  [dbo].[Archived Data Import] 
  (id, 
  [Company id], 
  [Site id],
  [Site name],
  [Receive date],
  [Detect date],
  [Type],
  [eventType],
  [Tag name],
  [Tag RFID],
  [Route id],
  [Route name],
  [Patrol id],
  [Device s/n],
  [Guard id],
  [Guard name],
  [Employee id],
  [Employee name],
  [Alarm group id],
  [Comment],
  [GPS latitude],
  [GPS longitude],
  [GPS accuracy],
  [GPS vertical accuracy],
  [GPS height],
  [GPS speed],
  [GPS heading],
  [Additional data],
  [Device id],
  [Device name],
  [Company name],
  [Occurrence date],
  [GPS date]);

My attempt:

DECLARE @7daysago datetime
SELECT @7daysago = DATEADD(d, -7, GetDate());
SET IDENTITY_INSERT [activetrackarchivedb].[Data Import] ON;
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [activetrackdb].[Data Import] 
    WHERE [Data Import].[Receive Date] < @7daysago)
DELETE CTE
  OUTPUT DELETED.id, 
  DELETED.[Company id], 
  DELETED.[Site id],
  DELETED.[Site name],
  DELETED.[Receive date],
  DELETED.[Detect date],
  DELETED.[Type],
  DELETED.[eventType],
  DELETED.[Tag name],
  DELETED.[Tag RFID],
  DELETED.[Route id],
  DELETED.[Route name],
  DELETED.[Patrol id],
  DELETED.[Device s/n],
  DELETED.[Guard id],
  DELETED.[Guard name],
  DELETED.[Employee id],
  DELETED.[Employee name],
  DELETED.[Alarm group id],
  DELETED.[Comment],
  DELETED.[GPS latitude],
  DELETED.[GPS longitude],
  DELETED.[GPS accuracy],
  DELETED.[GPS vertical accuracy],
  DELETED.[GPS height],
  DELETED.[GPS speed],
  DELETED.[GPS heading],
  DELETED.[Additional data],
  DELETED.[Device id],
  DELETED.[Device name],
  DELETED.[Company name],
  DELETED.[Occurrence date],
  DELETED.[GPS date] 
  INTO  [activetrackarchivedb].[Data Import] 
  (id, 
  [Company id], 
  [Site id],
  [Site name],
  [Receive date],
  [Detect date],
  [Type],
  [eventType],
  [Tag name],
  [Tag RFID],
  [Route id],
  [Route name],
  [Patrol id],
  [Device s/n],
  [Guard id],
  [Guard name],
  [Employee id],
  [Employee name],
  [Alarm group id],
  [Comment],
  [GPS latitude],
  [GPS longitude],
  [GPS accuracy],
  [GPS vertical accuracy],
  [GPS height],
  [GPS speed],
  [GPS heading],
  [Additional data],
  [Device id],
  [Device name],
  [Company name],
  [Occurrence date],
  [GPS date]);

Any help would be much appreciated. I would assume partitioning would solve this usually but I am on SQL Server Express for $$$ reasons.

Upvotes: 0

Views: 425

Answers (1)

jpw
jpw

Reputation: 44891

Use three part object names (database.owner.table):

DECLARE @7daysago datetime
SELECT @7daysago = DATEADD(d, -7, GetDate());
SET IDENTITY_INSERT [activetrackarchivedb].dbo.[Data Import] ON;
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [activetrackdb].dbo.[Data Import] 
    WHERE [activetrackdb].dbo.[Data Import].[Receive Date] < @7daysago
    )
DELETE CTE
  OUTPUT DELETED.id, 
  DELETED.[Company id], 
  DELETED.[Receive date],
  ....
  INTO  [activetrackarchivedb].dbo.[Data Import] 
  (id, 
  [Company id], 
  [Receive date],
  ....
);

Upvotes: 1

Related Questions