Timeline for Best way to defrag/compact a database for archival purposes
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 7, 2013 at 21:10 | answer | added | DamagedGoods | timeline score: 1 | |
| Feb 27, 2013 at 12:09 | comment | added | db2 | @Stu I'm pretty sure they do, and if not, it isn't a problem to add one as part of the archiving process. | |
| Feb 26, 2013 at 19:37 | comment | added | Stu | Do your largest tables have a clustered index? | |
| Feb 19, 2013 at 4:53 | answer | added | Liam Confrey | timeline score: 0 | |
| Feb 6, 2013 at 16:43 | comment | added | Nic | How about two new filegroups, one for the LOB data. Create new tables with LOB on the second filegroup. Insert the data into the tables, drop the old, rename the new, shrink the primary. | |
| Feb 6, 2013 at 13:27 | comment | added | db2 | @Nic I'm sort of leaning toward this idea, but the complicating factor is the mountain of LOB data that won't get moved with a rebuild. | |
| Feb 5, 2013 at 23:58 | comment | added | Nic | It's just a thought, but why not create a new filegroup, add a file, set a reasonable growth (say 500MB) and then rebuild your tables onto that new filegroup. Then shrink the primary file down to almost nothing. You won't care a lick about fragmentation on the system tables. | |
| Feb 5, 2013 at 21:51 | comment | added | db2 | @Marian Sounds interesting, but I'd like to stick to native SQL Server capabilities for now. I just need to very effectively defragment the databases, without a whole lot of unused space left in the file(s). If it's a third-party tool that performs the work instead of scripting manually, that's fine. | |
| Feb 5, 2013 at 21:27 | comment | added | Marian | Are you considering only solutions you code yourself or you can also review some application to help you with that? You could use RedGate's SQL Storage Compress to compress live data. Or you could try Virtual Restore to make compressed backups available as online dbs (without actually having the complete space needed). They are all based on the older Hyperbac windows file driver which are very good at compressing live data and backups. | |
| Feb 5, 2013 at 19:13 | history | edited | db2 | CC BY-SA 3.0 |
added 103 characters in body
|
| Feb 5, 2013 at 19:09 | history | tweeted | twitter.com/#!/StackDBAs/status/298870980388982784 | ||
| Feb 5, 2013 at 17:53 | comment | added | db2 | @JonSeigel I suppose not, and actually that's a pretty good idea, as it would save me the trouble of having to create a template database, and move all the data. | |
| Feb 5, 2013 at 17:47 | comment | added | Jon Seigel |
Do you (or the application) care if the tables physically end up in a filegroup other than PRIMARY?
|
|
| Feb 5, 2013 at 15:54 | history | asked | db2 | CC BY-SA 3.0 |