What shrinks when shrinking

[John Mosey]

A huge (and never used) database log was taking up about 4 GB of HD
space. We want the data for historical capacity, however, don't care
about the transactions log.

After a bit of research I ran the script on:
http://support.microsoft.com/default...NoWebContent=1

(which works just fine on Sql Server 2k)

And then

DBCC SHRINKFILE(Ramd omDataData_Log, 2)

This shrank the log file from 4ish gigs to 2 MB.

Of course my boss did backflips and wanted me to do it to *all* the
databases. I told him that it was probably a bad idea since we do want
the transaction logs incase something crashes, we can recreate the DB
from (for example) a week ago's DB backup.

So my question is this: When I shrink it to 2mb (or 200 MB as I am
suggesting) what are we actually "losing" and "keeping" does it keep
the most recent transactions (in which case I need to figure how much
we add each day) or earliest records, or random ones, or are they all
just "compressed ?"

I don't weant to lost the transaction logs for the last week or two,
but now that this shrinking has become the holy grail I need to show
there will be bad things happening if I just make these logs all reset
to the size of a floppy disk on a regular basis.

[Greg D. Moore \(Strider\)]

"John Mosey" <jm****@cayen.n et> wrote in message
news:9e******** *************** ***@posting.goo gle.com...

A huge (and never used) database log was taking up about 4 GB of HD
space. We want the data for historical capacity, however, don't care
about the transactions log.
This conflicts with what you say below.

Essentially it comes down to, "what do you need to recover."

If you can do a full backup once a day and don't care if you lose up to 24
hours worth of data, then set the db to simple recovery and ignore your
transaction logs.

If you need to recover up to the minute, or at least up to the point of the
last transaction log, then you need to keep them and do backups every so
often.

If you do a transaction log backup say every hour, your transaction log file
won't grow very large. You just need space to preserve all those logs.

Keep in mind that to recover, you need to apply the latest backup and ALL
transaction logs since then, not just the most recent.

Generally I'd recommend keeping at least two full backups, possibly more and
ALL transaction logs since the oldest full backup.


After a bit of research I ran the script on:
http://support.microsoft.com/default...NoWebContent=1
(which works just fine on Sql Server 2k)

And then

DBCC SHRINKFILE(Ramd omDataData_Log, 2)

This shrank the log file from 4ish gigs to 2 MB.

Of course my boss did backflips and wanted me to do it to *all* the
databases. I told him that it was probably a bad idea since we do want
the transaction logs incase something crashes, we can recreate the DB
from (for example) a week ago's DB backup.

So my question is this: When I shrink it to 2mb (or 200 MB as I am
suggesting) what are we actually "losing" and "keeping" does it keep
the most recent transactions (in which case I need to figure how much
we add each day) or earliest records, or random ones, or are they all
just "compressed ?"

The transaction log is basically a roadmap. It shows how to get from the
most recent full backup (or differential, but let's ignore those for now) to
the most recent state of the database.

So each transaction backup along the way is a snapshot of that roadmap.
I don't weant to lost the transaction logs for the last week or two,
but now that this shrinking has become the holy grail I need to show
there will be bad things happening if I just make these logs all reset
to the size of a floppy disk on a regular basis.

Well, it depends on your needs.

In our development environment, we just do backups, have the DB's in simple
recovery mode and don't worry about the transaction logs. If we lose a
database, we just restore the backup and move on. Not a big deal.

In our production environment, several key databases have a nightly backup
to disk (on a different device, never do disk backups to the same device as
your databases if you can help it) and transaction backups every hour.
(Except for one DB server where we do those every 15 minutes)

The subscriber database we don't bother to back up at all since we can
fairly easily recreate them from the publisher. So those are simple
recovery mode.

So, it basically comes down to, "what are your needs?"