Archiving

[Guest]

We link our Access app to a SQL Server database. I'm having problems dealing
with the massive amounts of data our server loads up on. Each database
starts at about 200 kB and after analysis and processing, balloons to 4, 6
even 8 Gig. Nothing can be lost as the client may need to revisit in detail
any item of info. Although my drive holds 70 Gig, we generate Gigs of new
data everyday. We've been trying to offload post-processed databases by
copying them onto CDs but these only hold 700kB. We're able to squeeze some
of the plus-sized database files (MDFs & LDFs) by compressing them into zip
files but WinZip gives up on files greater than 3.5 Gig. I mistakenly have
tried to break up some of these databases but the server retains basically
the same size files for history and linking and I'm left with even less
space. I've tried several methods, such as using the "Shrink Database"
option in SQL Server Enterprise Manager. Unfortunately, due to the already
dense condition of the file, this did very little and took up considerable
time. What I found out on my own is this: Just copy all the tables from the
bloated database into a new one. The new database now 1/4th as large and can
be "zipped".

Is there some archiving tool or database compression method I can employ to
be able to automate this and offload some of these plus-sized database files?
I don't want to go the DVD route.

Thanks in advance

 

[Guest]

Hi, Joe.

We link our Access app to a SQL Server database. I'm having problems dealing
with the massive amounts of data our server loads up on. Each database
starts at about 200 kB and after analysis and processing, balloons to 4, 6
even 8 Gig.

The symptoms you describe are very common and are usually caused by applying
all of the default settings when creating the SQL Server database and by not
truncating the transaction log. If you truncate the log before shrinking the
database, you should notice a big difference in the final size.

First, back up the database, then truncate the log, then shrink the database
files. To truncate the log, try:

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY;

While it may be the easiest way to set up SQL Server databases, using all of
the default settings when creating these databases will eventually lead to
"Disk full"-type errors if there's a lot of data to handle. An experienced
SQL Server DBA knows which settings to use for maximum efficiency, but not
every organization has a DBA available for every database project. This
situation is only going to become more commonplace as IT budgets shrink and
more managers believe the growing number of "create self-managing databases
yourself -- no need to hire an expensive DBA!" advertisements from the
database vendors out there.

I suspect that you are unintentionally loading the data into the database
using the logged mode, which will fill up the transaction log pretty fast.
Trying to bulk copy data into the database table using non-logged mode -- but
with certain settings -- will automatically use logged mode, instead. Those
certain settings in SQL Server 2000 are any of the following:

1.) The target table has rows and indexes, or
2.) The "SELECT INTO/BULK COPY" option is not enabled on the target table, or
3.) The TABLOCK hint is not specified, or
4.) The target table is replicated, or
5.) The target table has triggers, or
6.) The recovery model is “Full.â€

Those certain settings in SQL Server 7.0 are any of the following:

1.) The target table has a primary key or any indexes, or
2.) The "SELECT INTO/BULK COPY" option is not enabled on the target table, or
3.) The TABLOCK hint is not specified, or
4.) The target table is replicated.

These are things that you would expect an experienced SQL Server DBA to
know, but you can’t really expect others to know them, even those who have
been using SQL Server for years.

I also suspect that the 200 KB size is actually a typo for 200 MB, since you
also mention that only 700 KB can be saved on a CD. But either way, the
input size and the magnitude of the outcome means that there's very likely a
lot of data duplication and storage of calculations in those tables.

Your description makes it sound like there are intermediate steps (as in the
"transform" part of ETL) between the data input and final output that are
also being stored in tables, just in case a user needs to look at the details
in those steps. The input data and "transformation" data don't necessarily
have to be stored in the same database files as the final output data, so
that may be where the file size can be sliced into separate categories for
storage, and therefore different files that may be archived elsewhere.

It's a good possibility that displaying the views for the users was
originally quite slow, so the tradeoff to speed things up was to sacrifice
disk space by storing the calculations in tables and adding more indexes to
the tables used in these views. Obviously, not only do these calculations
take up space, but triggers that update these calculated values will also
lead to additional transactions being recorded in the transaction log. And
too many unnecessary indexes (as well as "bloated" keys, i.e., non-numerical
composite keys) can take up significant space, too, as well as slow down
transactions.

My suggestion would be to have an experienced SQL Server DBA analyze the
database, the applications accessing it, the database server and the network
and then optimize them to come up with acceptable speed and space solutions
-- in conjuction, not as separate issues.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.