• To My knowledge TEXT data fields havent been fully depreciated and there are ways round it with Custom Datatypes when it does happen, but thought Id make you aware. Also a standard option is to create a file group for storing blob data (Text/Image Filegroup in SSMS properties)

    Sean's suggestions are a good starting point to keep the DB running, but its a sticking plaster, that will only solve the problem for a short while, the same goes for archiving, especially if your data is going to grow, archiving once a year isnt bad, but it can soon become once ever 6 months then once a Quarter.

    I dont know how old the solution is, or the technology it was written in, the first couple of points are about that :

    1) Business process changes that are no longer capable to be accomodated within the existing structure.

    2) The age of the solution and the technology used for delivering a front end and back end solution

    Other talking points,

    1) Maintenance costs increase due to increased regression testing times, as products evolve the regression testing becomes more and more cumbersome if the code base has grown. You fix procedures A and B but forget C, or that a Business critical Process D requires output from A but is run once a Quarter.

    2) Inflexible/Poor design, making it difficult to add new business processes as well as extract data,

    3) Data volumnes (yes disk is cheap), they will only increase making it more onerous to query for users.

    4) Partitioning is really not required, you start doing that when you get close to at least 500GB.

    If you have a Dev server, do a rough and ready POC during your spare time (lunches, friday afternoons etc) to show how taking the tblCall table and normalising it a little will make a significant difference to performance.

    Then propose a rethink of the Design as a whole, including the front end application, if the application is more than 2 years old you can bet the users are likely to want new features.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices