How can i treate Huge DB size in sql server 2000

Hi all,

I have DB in operation its MDF size reached 8.38 GB and the system that work on the queries of insert timeout and the operation failed and many problems happens ...

actually the reason of the huge size of the DB is just one table that contain image field which we store word files in it in each row in the table .........

so how can i solve this problem without affecting the structure of the DB ..... coz we don't wanna to make code changes in the application that use this DB

thanks

if it 8.38 GB.. this can not be considered as a "Huge" DB from SQL Server point of view ... SQL Server handles much much larger databases... this is basically... architecture flaw... when u store word file in database it is bound to have some performance issue... its again if you have sufficient Hardware resources u can do that... you could have store the path of word file instead of file itself... anyhow, if u don't want to change the architecture... you can move the table which stores word file to a different Filegroup/disk ... or archive the unwanted data… add more hardware… etc.. etc…

Madhu

|||

I hear about the posibility of partioning the huge size table into physical partitions with out affect the logical structure so the performance of accessing the table will be faster ......

so if this process can be done on sql server 2000 on already exist DB how can it be done ...

i need an article about doing that .........

|||

If you are refering to partition of tables its supported in SQL 2005. This article might help you.

http://msdn2.microsoft.com/en-us/library/ms190787.aspx

Thanks,

|||

What about making partionning in sql server 2000?

|||

there are many article available... google it...

i think this will give some hints

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3861.mspx?mfr=true

regards

Madhu

|||You can create filegroups in SQL Server 2000. 8+GB is not a huge database as I have been handling databases more than 100GB and SQL Server is still functioning well. If your application is the one timing out, you have to revisit your application design and your codes as well as your database structure.|||When was the last time the database has been checked for consistency and redinexed for stats update?|||

Let me honest with that never happen ......

so .... is there any suggestions?!

|||

use DBCC DBREINDEX to reindex all the tables and use sp_updatestats to update all the table statistics and see the performance

also use sp_spaceused @.updateusage = 'TRUE' to get the space used by the data...

Madhu