More operations: SQL Server storage consumption

[joe.mag]

This seems to be the best forum for this matter but I apologize if I selected wrong!

This is more an ongoing operations issue than an initial setup issue (although system configuration may play a role).

I am trying to monitor the growth of my .MDF files since the bigger they get the harder things like backups get, etc.

I am using a series of queries like this: "select 'UserEnteredFlags', 'Title', isnull(sum(DATALENGTH(UserEnteredFlags)), 0) from SelectDb.dbo.[Title]"

I have one query for each image, text, ntext -- all large-capacity data types -- column from each SelectDb table.

Here is my question/concern. I note that the table/field pair "HudLineAdditionalCharge.Sources" is a sizeable chunk of my usage--roughly 20%. When I look at that field it's binary data. I don't see any mention of this field in the SDK documentation and was curious what its purpose is and what it stores. As a title agency in NC that really doesn't rely on the HUD for much I question whether this 20% overhead is truly necessary or in use and if I could periodically purge the non-null field values for this field to reduce database bloat.

Thanks!

[Randy Mellow]

The "Sources" column contains information Select uses to manage relationships between charges and the collection of data items each charge depends on for its calculation. It is not safe to simply remove this data since it would likely cause the Order containing those charges to become corrupt and possibly inaccessible, i.e. you might not be able to open it.

[joe.mag]

SoftPro Devs, please refer to your internal ticket system to see ticket 591027 which provides further detail of the SelectDb growth we've experienced over the last 7 months. The rate of increase is of concern since it is already taxing our backup systems and threatens to grow ever worse.

For the benefit of other users of the forum, our growth metrics are as follows: In the seven months since 2/19/2012 it appears our database has grown from 4.1GB (as measured in the .bak backup file) to 11.7 GB.

BTW, I left out that I've checked if the database merely needs to be shrunk and there was very little free, unused space.

Modifying our original goals as stated in this issue, here's our current interest: What we’d like to know is this—is there any way we can purge data from the database (e.g. for files that are in a closed state, for instance) to reduce its size and/or does SoftPro have an archiving strategy wherein we’d essentially move quiescent orders to a separate database and yet not lose the ability to report on and access those files in a read-only mode.

Thanks,

Joe

[Randy Mellow]

Hi Joe, I apologize for the late reply. We are aware of the need for archiving data and are looking into that for a future release. Unfortunately, at this time, there is no way to automatically purge the database of historical information.

[joe.mag]

Randy, Thanks. I guess you can have them close that ticket. Thanks.