June 29, 2011 at 1:59 am
I have a database with 600 GB MDF file.Its is use for MOM Alerts.I want to archive the old data and should remove it from the database.Kindly suggest a gud method to archive it
June 29, 2011 at 2:17 am
1-Identity the transaction tables
2-Identity the relationships of tables
You should write store procedure for each table and in each store procedure you will write a transaction its depend upon you how much data will be archive ,here I am generating the example script for the 1 month data archive and I am creating a log table for this archive procedure
Archive log table name is : ArchiveLog
Begin transaction
Select *
from tab1
where datecolumn < getdate() - 30
select @count=count(*) from
from tab1
where datecolumn < getdate() - 30
If @@Error > 0
Begin
rollback transaction;
Insert into archivelog(totalrecords,tablename,getdate(),status,errormessage)
values(@count,'Tab1',getdate(),'Failed',errormessage());
End
Else
Begin
commit transaction;
delete from tab1 where datacolumn < getdate() - 30
Insert into archivelog(totalrecords,tablename,getdate(),status,errormessage)
values(@count,'Tab1',getdate(),'Succeeded','N\A');
end
and schedule these all Store procedure in SQL Server Agent job step wise as your requirement,same procedure for the relationship table which one using the tab1 table id as a foreign key
so find the required Id in the tab1 table and then delete from the foreign key table,I can define more clearly if I have definitions of tables
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 29, 2011 at 1:07 pm
Usually the archival policy applies to few highly transactional table within a database, most of the time they have a column which can be used to sequence the rows that were inserted e.g an identity column or a timestamp.
You need to finalize the retention policy as to how many records would actually be required to maintain day to day operations.
These records would need to go into the archival table which is actually a replica of the original table minus any constraints.
Records older than the ones which qualify for archival should be deleted.
June 29, 2011 at 2:22 pm
How you handle your old records will probably be dependent on your business needs. For example will you need access to the old data at some point in the future and if so how readily available does it need to be?
Identifying the records should be pretty simple, pick a date and find the records older than that date but you'll have to decide what to do with the data. which could be anything from simply deleting the records to storing them in a different DB or exporting them to a flat file and saving that somewhere.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply