April 1, 2017 at 1:49 am
I have some data being written to a sql database, by requirement I need to retain the data for 1yr. After that year is up then I can archive the data off so it can be stored off site, I would also like to purge the data once it's been archived. What is the best way to go about doing this?
April 1, 2017 at 5:48 pm
table paritioning is frequently used to meet this kind of requirement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 8:48 pm
If you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.
--Jeff Moden
April 1, 2017 at 8:58 pm
I was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.
That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2017 at 9:17 pm
SQLRNNR - Saturday, April 1, 2017 8:58 PMI was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".
I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?
--Jeff Moden
April 1, 2017 at 9:50 pm
Jeff Moden - Saturday, April 1, 2017 9:17 PMSQLRNNR - Saturday, April 1, 2017 8:58 PMI was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".
I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?
I do defrag indexes in the most active partitions only. I don't end up with that amount of free space but that said, I don't mind the extra free space either. We have sliding windows and rotate through the files nightly. Some days that file may require more space than other days depending on the data volume of the day.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2017 at 8:53 am
SQLRNNR - Saturday, April 1, 2017 9:50 PMJeff Moden - Saturday, April 1, 2017 9:17 PMSQLRNNR - Saturday, April 1, 2017 8:58 PMI was thinking of the stored procedure route. I really prefer partitioning because it is so much easier and it can take so much less time to run when done right.That said, the cheaper solution is the stored procedure route (well, minus development time). 😉
Agreed on the speed of partitioning and the wonderful tool known as "SWITCH".
I do have to ask, though. Are you using 1 file/filegroup per partition? And then do you ever rebuild indexes on a partition? If so, what are you doing to overcome the problem of having wasted free-space in each partition to the tune of about 120% of whatever the largest rebuilt index was?
I do defrag indexes in the most active partitions only. I don't end up with that amount of free space but that said, I don't mind the extra free space either. We have sliding windows and rotate through the files nightly. Some days that file may require more space than other days depending on the data volume of the day.
Ah... I didn't qualify what I was thinking and my apologies for that. The partitioning that was heavy on my mind was for the eventual conversion of older partitions (1 file per filegroup per month) to Read_Only so that they'd no longer need to be backed up (we're required to keep everything in the table forever and it's approaching a TB in size). I go through a bit of a complicated jag at the end of each month to rebuild the indexes in the partition about to be set to Read_Only so that we're not condemning a fair bit of free space to a Read_Only status. I thought that whatever you might be doing for index maintenance on partitioned tables might make it a bit easier.
I absolutely agree that having free space in active partitions is a good thing.
--Jeff Moden
April 3, 2017 at 7:19 am
Jeff Moden - Saturday, April 1, 2017 8:48 PMIf you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.
i would prefer to move rows to some sort of warehouse and trim the warehouse to retain only the required rows ongoing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 3, 2017 at 7:47 am
Perry Whittle - Monday, April 3, 2017 7:19 AMJeff Moden - Saturday, April 1, 2017 8:48 PMIf you don't want to go the table-partitioning route, then write a proc to move items to accumulator tables on a daily basis and delete them from the mains. Once a month, backup the accumulator tables and then truncate them.i would prefer to move rows to some sort of warehouse and trim the warehouse to retain only the required rows ongoing
I'm just a little confused, Perry. In your example, is the warehouse the archive data or the main data that the users hit on?
--Jeff Moden
April 5, 2017 at 12:21 am
SQLRNNR - Saturday, April 1, 2017 5:48 PMtable paritioning is frequently used to meet this kind of requirement.
I'm not a DBA by any means but I know my way around mssql & sql management studio, I've been learning as things come up. Anyway would this be a good set of instructions to follow https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes ?
April 5, 2017 at 12:37 am
This one may be a lot better for help in learning partitioning.
https://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply