July 20, 2010 at 2:31 pm
I was wondering if anybody could help me figure out some kind of better solution to this.
Basically I have my database, and all the tables that record data on the users, the lookup tables, etc. take up about 20GB of space.
We also have tables that record log entries of every transaction we do with other companies in which there is a column that stores the full XML of the request. They aren't critical to our business (they are more of a record of what we did for debugging purposes and such) so they aren't needed in the main database.
Now, I archive the log tables every week so that only the last six months of data are kept in them, but as our company continues to grow (always a good thing) the log tables are growing exponentially as well. Now, the log tables are taking up 100GB+ of space, and while the space isn't a huge concern as far as storage on a server, the main concern is log shipping file sizes - specifically restore times and the amount of time it takes to copy the log files around. I figure I also need to figure out a solution to this problem sooner rather than later as about only 2 years ago keeping the last 6 months of data was only 10GB - so it's gone up 10x's in two years. If that continues it's going to be a big problem in just a few years..
I've thought about moving the log tables to a different database. The problem is they contain id's and such that need to be looked up in the main database. Up until now these have been done with FK constraints, and obviously moving the data to another db would stop the constraints from working. I've considered triggers after that - but not sure how good an idea it is to go that route to begin with...
If it's necessary to know, this is all with SQL 2005. If anybody has any good ideas to this problem, I'd be happy to hear them
Thanks in advance!
July 20, 2010 at 2:38 pm
One thing that does come to mind is FileGroups. Maybe you can create Filegroup in a different drive and move these log archive table to this filegroup. That means your Primary filegroup is compact. Mix it with compressed back ups (Tools like Litespeed, redgate can help with that).
Moving to SQL 2008 you could use the native compression of back ups. Also you could compress the log archive table data.
-Roy
July 20, 2010 at 7:42 pm
Adding to Roy's suggestion, you might consider marking your old filegroups as read-only once they are sufficiently old enough. This way, you'd only need to do the filegroup backup of this filegroup once, after it is marked read only (and test it!). The only complicated part here would be the restore process. If you have 2005 Enterprise, you can kick off a piecemeal restore from a backup of the PRIMARY filegroup. This means you can keep the old data offline until you need it. Without Enterprise Edition, I believe you'll still need to take full backups of the database.
However, your main issue is the size of your transaction log backups, which are affecting log shipping. This won't decrease by moving to filegroups. And archiving the older log rows out to a different database will increase the amount of transaction log traffic. Again, as Roy suggested, backup compression tools would help out here, although I'm not sure how well the built in Log Shipping functions support 3rd party compression. Hyperbac is transparent to SQL Server, so it might be more appropriate than the VDI-based backup tools. Alternatively, you can roll your own log shipping where you can control the backups and restores.
Given that the XML data is so large, it will be very compressible. However, as it doesn't impact your main database, and may not need to be log shipped, I would strongly suggest moving it out, and logging it to a new database. In terms of your FK issues, you might consider either de-normalising this in your logs - they might get slightly bigger, but your core log shipped database will be tiny.
Alternatively, consider keeping the log tables, with all its FKs, but extracting the XML part (which will most likely be the bulk of the log table). Move the XML column into a new table in a new database that stores an ID and the XML data, and copy the ID into your log column. This should shrink the table size dramatically, keep your FKs, and allow you to still look up the XML data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply