> Thanks for the input. Besides backing up the entire database and then
> deleting records from all years except the last two years , what is
> the best way of reducing the size of the database so that it only has
> the last two years of data.
And you are asking that about a database I don't anything about?
Sorry, this is an application-specific question. And just to show you,
let me tell you about a database where they had done this. This was a
database for a stock broker. Due to disk limitation, they had deleted
all transactions before 1998-01-01. Was this is a good thing?
Well, first define "transactions before 1998-01-01". In a context like
this, a transaction has four or five dates:
* The accounting date - the banking day the transaction was registered.
* The trade date - the day the transcation actually happened. Often the
same as the accounting day.
* The settle date - the day when you pay for your instruments ang get them.
* The value date - the date from which the transaction affects the interest.
The same date as the settle date, or the next banking day.
* The cancel date - the day the transaction was cancelled (because it had
never occurred or was incorrectly registered).
They had retained all transactions that had any of the first four dates
in 1998; I don't recall about the cancels.
Now, in a system like this you have a concept of a "trade" which is simply
is all contract notes in the same stock on one single day. But there are
different rule for the value date buy and sell transactions, so when I got
hold of this database I found a lot of incomplete trades made on
But that was not all. Most trading is buying and selling stock, but some
kind of trades are part of a chain. Examples of that are future contracts
and instrument loan. Since we were to add instrument loans, I had to
write code to reconstruct notes as far back as in August 1996. In the
spring of 1999 when I made this exercise (because we were converting
the data to our system), these were loans that were still open.
Obviously, the people who had removed old data in this database had
not analysed the exact consequences.
Now, I don't know anything about your data, but chances are good that
you have similar things to take in regard.
Erland Sommarskog, Abaris AB
SQL Server MVP