sql server new users: Hello,

If you do not want to have log backup then truncate the log and shrink the
LDF file.

BACKUP LOG <DBNAME> with TRUNCATE_ONLY
GO
DBCC SHRINKFILE('Logical_ldf_name','targetsizein_MB')

Incase if your data is not critical then make the recovery model of your
database to SIMPLE. This will make sure that LDF file will not grow heavily.

ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE

Thanks
Hari


[quoted text, click to view]

I have a MS SQL 2000 database, size is about 1 GB but the log id 4 GB, is
there a way to clear the file to reduce the file size ?
What is this file useful for ? So far I have never used it. How can I
delete content or make it smaller ?

Aleks

[quoted text, click to view]
To answer your second question, the log file is where all the changes to
your database and data are recorded. The changes are called transactions.

Imagine I am transfering £100 from my savings to my current account (it
always seems to go in that direction for some reason) I don't want a glitch
to happen halfway through and the money to come out of my savings but not go
into my current account. So if something nasty happens I want to be able to
roll back the transaction, i.e. put it back the way it was. It would be
better to have it never happen than sort of half happen. So the transaction
log (the ldf file) records the changes as they happen, so that I am able to
roll back (undo) transactions.

It also means that if I have to restore my data from a backup, I can roll
forward through the log file to bring the data back to the moment I lost it.

If your database is not in simple recovery model you need to periodically
back up your log file to truncate, otherwise it will just grow and grow for
ever.

[quoted text, click to view]

Hi Bob. Does that mean you have to specify TRUNCATE every time you do a log
backup, to ensure the log is shrunk?
I don't use TRUNCATE, but assumed the log was shrunk with just BACKUP LOG.
Did I have it wrong? (again! :))
Regards
Ron.

[quoted text, click to view]
Nope, BACKUP LOG truncates your log file by default.

--

Bob Simms
www.xpertise.co.uk

[quoted text, click to view]
Thanks, Bob.

To follow up on Bob's comments, by analogy, the Transaction Log is very
similar to the General Ledger in an accounting system: it is independent of
the chart of accounts and records all activity in the accounting system
BEFORE changes to the underlying accounts are recorded.

The database is just the current state of values for the rows. The
transaction log not only records the new values that well be made to the
database, but the actions required to modify those values.

Similarly, in an accounting system, when you balance, offset, and close
accounts out for the period (monthly, quarterly, yearly) the accounts and
the general ledger are zeroed out and the history archived before the new
period activity is initiated. This is similar to what happens when you back
up the transaction log.

Also, when accounts are closed out, some have outstanding balances that must
be carried forward into the new period. This are similar to the currently
active transaction log transactions that remain (are not truncated) when a
tlog backup completes.

Lastly, just like the general ledger, if you had the initial chart of
accounts, then with the complete general ledger history, you could bring
your accounts up to date to any point in time simply by replaying all of the
entries. The same is true with the transaction log backups. With an
initial full database backup (like right after you created the database), by
applying the entire transaction log backup history to the database, you
could restore the system to whatever point in time within that history that
you wanted to simply by replaying the contents of those backups, in
sequence.

Now, this can only happen if the backups are performed correctly. There are
operations that can destroy the transaction sequence contained in the
transaction log backups. One of them would be manually truncating the
transaction log without first backing it up. The other would be switching
the recovery model to SIMPLE, which is the two most frequent recommendations
for dealing with oversize tlog files.

The recommendations are valid, but you must understand what happens when you
perform these operations. If you need to maintain the transaction history
in order to restore the database to prior points in time, DO NOT TRUNCATE
the log and do not switch to the SIMPLE RECOVERY model.

Hope this helps.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]