Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Archival Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 10:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
There is a requirement, details as below:

1) Copy data from transaction table (around million records inserted daily) into a archival table
2) Delete data from transaction table
3) Step (1) - transactions can be minimally logged
4) Step (2) - all transactions completely logged.
5) There are 14 transaction tables; step (1) to (4) needs to be repeated for all transaction tables.

Here is the sample code (for one table)

-- assuming archival table already exists
-- Step 1: Copy data into archivalTbl from transactionTbl with minimal logging
INSERT INTO archivalTbl WITH (TABLOCK)
(col1,
col2,
col3)
SELECT col1,
col2,
col3
FROM transactionTbl;

-- Step 2: Delete data from transactionTbl will full logging
DELETE FROM transactionTbl;

Can someone please advise any best way to achieve this?

Thank you.


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1361722
Posted Wednesday, September 19, 2012 11:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
Lokesh Vij (9/19/2012)
There is a requirement, details as below:

1) Copy data from transaction table (around million records inserted daily) into a archival table
2) Delete data from transaction table
3) Step (1) - transactions can be minimally logged
4) Step (2) - all transactions completely logged.
5) There are 14 transaction tables; step (1) to (4) needs to be repeated for all transaction tables.

Here is the sample code (for one table)

-- assuming archival table already exists
-- Step 1: Copy data into archivalTbl from transactionTbl with minimal logging
INSERT INTO archivalTbl WITH (TABLOCK)
(col1,
col2,
col3)
SELECT col1,
col2,
col3
FROM transactionTbl;

-- Step 2: Delete data from transactionTbl will full logging
DELETE FROM transactionTbl;

Can someone please advise any best way to achieve this?

Thank you.



First, INSERT INTO will be fully logged, not minimally logged (iirc).

So, let me get this straight, each table that needs to have records archived adds approximately 1,000,000 records each daily. Are you archiving that many records daily as well? What we really need to know is the structure of the data (DDL for the tables) so that we can make a determination of needs. If you are adding and deleting nearly 2,000,000 rows of data, then partitioning may be the what is needed for the archiving process. Depending on the data, it may be possible to establish a slinding window scenerio for quickly archiving data. This could also be done for adding data depending on how new records are added. If it is by a bulk process, a similar process can be used to quickly add the data as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361738
Posted Thursday, September 20, 2012 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Lynn Pettis (9/19/2012)
First, INSERT INTO will be fully logged, not minimally logged (iirc).


Hi Lynn
I am using table locking option TABLOCK, this support minimal logging.


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1361910
Posted Thursday, September 20, 2012 6:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 1,313, Visits: 2,521
Lynn Pettis (9/19/2012)


If you are adding and deleting nearly 2,000,000 rows of data, then partitioning may be the what is needed for the archiving process. Depending on the data, it may be possible to establish a slinding window scenerio for quickly archiving data.


+1

With partitioning you will be able to truncate the data rather than deleting it from the table. So it will also avoid generating that much transaction log that it is generated with current DELETE operation.



Sujeet Singh
Post #1361931
Posted Thursday, September 20, 2012 7:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
Lokesh Vij (9/20/2012)
Lynn Pettis (9/19/2012)
First, INSERT INTO will be fully logged, not minimally logged (iirc).


Hi Lynn
I am using table locking option TABLOCK, this support minimal logging.


Care to show us a reference that supports this assertion? I haven't been able to find one, but I'll keep looking.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361958
Posted Thursday, September 20, 2012 7:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
Lynn Pettis (9/20/2012)
Lokesh Vij (9/20/2012)
Lynn Pettis (9/19/2012)
First, INSERT INTO will be fully logged, not minimally logged (iirc).


Hi Lynn
I am using table locking option TABLOCK, this support minimal logging.


Care to show us a reference that supports this assertion? I haven't been able to find one, but I'll keep looking.



Looks like I found one in Books Online:

When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. For more information, see INSERT (Transact-SQL).


So this, plus your comments, tells me that your table is a heap (no clustered index) and you are using simple or bulk-logged recovery model on your database.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361963
Posted Thursday, September 20, 2012 9:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Hi Lynn

Very rightly pointed out that my table is actually a heap, without clustered index (I should have been more specific earlier). Just to add on, the transaction table does not have date field. So partitioning (implementing sliding window partition) is not feasible.

Kindly suggest some better method.

One more thing, I am not very sure if SQL server allows bulk insert (using "BULK INSERT" keyword) from one table to another? Definitely this feature is available when importing data from a flat file (and other variants) to a table.

I really appreciate your help on this.

Thank you.


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1362406
Posted Thursday, September 20, 2012 10:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:54 PM
Points: 23,227, Visits: 31,921
Still like to see the ddl for the table snd some sample data.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1362413
Posted Friday, September 21, 2012 2:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 1,313, Visits: 2,521
Lokesh Vij (9/19/2012)
There is a requirement, details as below:

1) Copy data from transaction table (around million records inserted daily) into a archival table
2) Delete data from transaction table
3) Step (1) - transactions can be minimally logged
4) Step (2) - all transactions completely logged.
5) There are 14 transaction tables; step (1) to (4) needs to be repeated for all transaction tables.



What is the frequency of this process? You need to do it every day or is it done on monthly basis?

If it is a monthly activity or done once every 3-4 months, I guess I would have tried below:

1. Generate the script of the transaction tables & store it.

2. At the time of archiving, I would like to just rename all the transaction tables to some name like TransationTableName_MM_DD_YY.

3. Run the script generated in the first step to make new transaction tables which will now hold the new data.

4. Now insert all the data that has to be archived into archive table from the transaction table that we just renamed as TransactionTableName_MM_DD_YY.

5. Once archiving is done, you can just truncate the old transaction table i.e. TransactionTableName_MM_DD_YY

Benefits:

1. When inserting the data in archive table we are not touching the newly created transaction tables. We will be using the tables that we renamed as TransactionTableName_MM_DD_YY. This should make the insertion process much better.

2. Once archiving is done, instead of deleting the data we can just truncate the table that we renamed as TransactionTableName_MM_DD_YY. This will make this step very fast (truncate will take seconds when delete will take hours!).

P.S.: This is just an idea in my mind right now, you need to evaluate if this can be performed in your environment.



Sujeet Singh
Post #1362481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse