Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zaebis
Starting Member
3 Posts |
 Posted - 2009-06-16 : 01:12:19
|
| Ok I have a DB (already implemented) with Varbinary field to store files. There is not a lot of data in all other tables (<10K raws) but this one table with BLOBs is getting out of hand. DB is gobbling 50G now and is incrementing by a 2 GIGs a week. I need to take actions before I wind up with a single file in Tb size. What are my options here? Remember there is already legacy data in it so moving BLOBs to the file system will be a challenge.Thanx |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-16 : 05:04:10
|
| If moving the BLOB's to the file system is not an option I'd suggest to partition the blob tables (either with partitioning in enterprise version or by creating a custom partitioning by using hand made constraints and UNION ALL views) and place the different tables in different file groups which again are on different disks.- Lumbago |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-16 : 05:05:29
|
Which version and edition of Microsoft SQL Server are you using?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
zaebis
Starting Member
3 Posts |
Posted - 2009-06-16 : 21:40:17
|
quote:
Originally posted by Peso
Which version and edition of Microsoft SQL Server are you using?
E 12°55'05.63"N 56°04'39.26"
I am on SQL 2005 Developer. |
|
|
|
zaebis
Starting Member
3 Posts |
Posted - 2009-06-16 : 21:42:51
|
quote:
Originally posted by Lumbago
If moving the BLOB's to the file system is not an option I'd suggest to partition the blob tables (either with partitioning in enterprise version or by creating a custom partitioning by using hand made constraints and UNION ALL views) and place the different tables in different file groups which again are on different disks.- Lumbago
I was under the impression that if I partition (in SQL Server 2005) I will not have to change any queries to unionize the data for selection. Will look into partition option. My concern though is about the backup with large DB files. Does 2005 Developer have an option to backup only the partition that changed? Incremental partition backup if you will? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-17 : 03:18:12
|
| If you use the partitioning that comes with 2005 Dev/Ent edition there shouldn't be any need to change any procedures/queries as the table names will remain the same. It should work quite seamless if you do it correctly. Creating the partitioning manually can also be somewhat seamless except that you might face some problems with identity columns if you need to do partitioned inserts (inserting data using the UNION ALL view). About the backup: what you need to do is to create several filegroups and place the different partitions in different filegroups. It is possible to take a differential backup of a filegroup only but I have never tried this myself and you will have to practice restores before putting this in to production. All too often people have non-functioning backups...- Lumbago |
|
|
|
|
|
|
VLDB design suggestions