469,625 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

stupid... simple question

In sql server 2000, how do you manually go and truncate the logs? Does
this only occur when you create a backup? all I want to do is manually
shrink the log files. I have backups, so I don't need them.

I actually backup the db once a week and back up the logs every day,
yet the logs are getting real big. I thought this was configured in
the backup and/or the maintanance plan. I've done a restore (from the
db backup) manually hundreds of times, but I've never had to restore
to a checkpoint from the transaction log backups, maybe this is why
I'm confused on how to do this.

thanks,
dave
Jul 20 '05 #1
1 1338
[posted and mailed, please reply in news]

malcolm (ch********@yahoo.com) writes:
In sql server 2000, how do you manually go and truncate the logs? Does
this only occur when you create a backup? all I want to do is manually
shrink the log files. I have backups, so I don't need them.

I actually backup the db once a week and back up the logs every day,
yet the logs are getting real big. I thought this was configured in
the backup and/or the maintanance plan. I've done a restore (from the
db backup) manually hundreds of times, but I've never had to restore
to a checkpoint from the transaction log backups, maybe this is why
I'm confused on how to do this.


If you don't have any need for restore to point in time, but is always
content to restore the last backup, then you may want to consider setting
the database in simple recovery. In this case the transactlon log will
be truncated to up the oldest active transaction frequently (about once
a minute, I believe). Beware, though, that if you are running reindex
jobs, the log will still need some space, the reindexing of a table is
logged. Then again, if you have big archive tables that are rarely
updated, reindexing them may be a waste of resources.

Note that while you can shrink the log, it's a bad idea to shrink it
if it will grow again, because the growing takes resources.

If you need full or bulk-logged recovery, backing up your transaction log
is the way to keep it down in size. Note that merely backing up the
database does not affect the transaction log. If you need to truncate
the transaction urgently, because you have run out of disk space, you
can use WITH TRUNCATE_ONLY. Note that this invalidates the transaction
log for point-in-restores.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by sebb | last post: by
5 posts views Thread by raz | last post: by
16 posts views Thread by Justin Hoffman | last post: by
14 posts views Thread by UJ | last post: by
6 posts views Thread by Adam Smith | last post: by
9 posts views Thread by AWW | last post: by
9 posts views Thread by Alec | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.