DB getting large because of the varbinary. Possible actions...

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » DB getting large because of the varbinary....

DB getting large because of the varbinary. Possible actions...

Rate Topic

Display Mode

Topic Options

Author

Message

zaebis 88446

Posted Tuesday, November 17, 2009 3:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, November 21, 2009 10:38 AM
Points: 6, Visits: 5

Ok Folks I have been saving files (20K to 250K) into varbinaries in a table. The number of records is relatively small (below 200K) but it is the size of the files themselves that drove DB file size into 60G territory (and growing fast). DB is accessed by both standalone app and web logic. Everything works and dandy but I am concerned about infinite expantion of my DB. Needless to say backups take over an hour (full backups).

I need a strategy that would allow me to

1. keep my existing code instact (too much of it already written)
2. help me mitigate future DB expantion
3. improve backup restore times

All of the BLOBS are in 1 single table. The rest of the DB is a peanut in terms of size and number of records.

I am on SQL Server 2005 but can switch to 2008.

Any advices...

Post #820439

Jack Corbett

Posted Wednesday, November 18, 2009 8:07 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 8,186, Visits: 7,971

Switching to 2008 and using Filestream may help.

You could also create a new filegroup and move your single table with the BLOB data to a file in that filegroup. Then you can do filegroup backups.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #820864

Paul White NZ

Posted Friday, November 20, 2009 2:28 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 4,712, Visits: 3,093

If you are using Enterprise Edition, you could consider partitioning the LOB table.

Placing archive data on read-only file groups within the partitioning scheme would mean you could use the READ_WRITE_FILEGROUPS option of BACKUP DATABASE to create a partial backup. You can supplement these backups with Differential Partial Backups, as well as normal transaction log backups.

See:

http://msdn.microsoft.com/en-us/library/ms191539.aspx (Partial backups)
http://msdn.microsoft.com/en-us/library/ms190218.aspx (Differential partial backups)

Full file backups are also an option:

http://msdn.microsoft.com/en-us/library/ms189860.aspx (Full file backups)
http://msdn.microsoft.com/en-us/library/ms190939.aspx (Full file diffs)

If you don't need to keep all data online forever, you can also use partitioning to make archival simple.

Paul

Post #822172

PaulB-TheOneAndOnly

Posted Saturday, December 05, 2009 5:00 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:26 PM
Points: 889, Visits: 1,381

zaebis 88446 (11/17/2009)

I am concerned about infinite expantion of my DB.

How about a purge strategy?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #829458

« Prev Topic | Next Topic »

Permissions