Looking for suggestion of how to manage a DB file when it getting too big.

Hi all,

At our facility, we have a > 350 gigs database and the DB is breaking into 2 files. One file is our Image table with the size of 300 gigs. The other file contains all of the other tables. The image file is getting bigger and bigger everyday, I am worry that eventually when a data file getting too big it could lead into problem. - DB fragmentatio, hard to manage etc

My question is anyone has run into this problem, and how do you solve it?

I am open for any suggestion.

BTW: I am using SQL 2000.

Thanks in advance.

Use filegroups, Assign the frequently modified tables in one filegroup while those that don't get modified frequently be assigned in another filegroup. When you create filegroups, you also modify your backup strategies.|||

Since your basic problem is with growth of your images, you need to come up with a mechanism for managing those.

If this is SQL Server 2005, then I would implement partitioning against that table on your identifier. That will allow you to split the table across multiple filegroups and files. If this is SQL Server 2000, then you can add multiple files to the filegroup that this table resides on (the files don't necessarily need to be in the same directory or even drive).

It doesn't really matter that they are images or discrete data, managing them occurs using the same process. You need to rely on one or more filegroups each with one or more files in them.

|||

Thanks all for your suggestions. The main problem we are having is we have one huge table (300 gigs) reside in one file group and spliting a table across multiple files is not an option for us since we are using SQL2k, (Might have to wait until we upgrade to 2005) but I'm going to use your suggestions with other databases

Thanks

|||I don't understand why not. I am NOT suggesting splitting across multiple fileGROUPS, because that would require an upgrade to 2005. I am suggesting adding FILES to the existing filegroup. This is absolutely supported and possible in SQL Server 2000. The data in that single table would then be written across multiple FILES within a single filegroup and would at least give you options on managing the storage space.|||

Hmm, I obviously didn't think of this possibility. Data could be written across multiple files! it makes perfect sense now. damm, how could I not see this :-(- sorry for misunderstood your comment and thanks for enlightent me, Michael.

Appreciated.

|||Hello Michael Hotek,

I had gone through the post. But did not understood how it will help the issue.
I have one similar problem here. We aer using MS SQL 2000, where there is only one table in the database where scanned document images are storing and database is growing by 2 to 3 GB per day. Currently it is upto 300 GB. For safety reason we are planning to partition/split the data in the table according to one key field 'book_no' so that there will be seperate partitions for each book_no. At the end we need to combine all together to form the original table.
Please guide/suggest me how to split the table with the query like 'SELECT * FROM doc_master WHERE book_no=1' and split the entire table for seperate book_no's and later combine them together to form the original table?

Right now I am planning like this: create demmy databases for each book_no's with same structure as doc_master and use "INSERT...SELECT...WHERE book_no=?' statement to fill these databases and delete them from original database. Whether it will ok with out harming data? Please suggest.|||

Rajeev,

My problem is I can't reduce the size of my image db, I can't archive (at least in SQL 2000) I can't partition my table either and I don't want my data file growing too big. So spliting a single data file of a table into multiple data files (like what Michael suggested) is a way to go. I don't think this way will gain much in performance or anything but having a smaller data files would help me to easily manage files. I also store these smaller data files into separate drives etc. so the sugesstion did help me in the way of managing the physicall data file.

I think what you are trying to do is separate data into separate databases, in my opinion, this could lead into more complication since now you have to manage multiple databases, mutiple db permissions, db maintenances and it might also hurting your application performance etc. in the end you are not gaining much.

|||

You can add data files to a SQL 2000 database, within the same filegroup.

What this will accomplish is that the new data inserted into your table will start spreading to both files. It will not automatically rebalance existing data between files.

Have you experienced specific problems as a result of having a large table, or are you just anticipating that you might?

What is the storage that this database is on?