473,323 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

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 1444
[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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: sebb | last post by:
I'm kind of newbie to programming, but I thought of something and I want some opinions on that. It's about a new instruction block to do some cycles. I thought about that because it's not very...
119
by: rhat | last post by:
I heard that beta 2 now makes ASP.NET xhtml compliant. Can anyone shed some light on what this will change and it will break stuff as converting HTML to XHTML pages DO break things. see,...
5
by: raz | last post by:
Greetings all. I apologize for what is almost certainly a stupid question, but I can't figure this out, and have no more time for head bashing... The short version: what is the appropriate...
4
by: IS | last post by:
At the recommendation of several people in this newsgroup I have downloaded two or three Compilers. One is the Beta version of Microsoft's Visual C++ 2005. I have entered a complete beginner code...
16
by: Justin Hoffman | last post by:
This is a question concerning query optimisation. Sorry if it's a bit long, but thanks to anyone who has the patience to help - This is my first post here... If I have two tables:...
14
by: UJ | last post by:
What's the easiest way (without setting up a style sheet) to set the font for an area of text. I need to be able to specify the exact font and font size - not the 1-7 numbers IE uses. I want to be...
6
by: Adam Smith | last post by:
I have posted this and similar questions repeatedly and can't even raise a single response. I am being led to believe that this then 'Must be a stupid question' although people say that there is no...
5
by: Alberto Salvati | last post by:
Hi, List. My company has a VERY BIG product base on db2 udb v7.x. We want to di an upgrade to v9, but.... current db has a lot of procedure (cobol..!). Therefore, we've planned to rewrite this...
9
by: AWW | last post by:
Running XP - Visual Studio 2005 - VB Want to have duplicate projects - one safe and stable - other for experimenting Can't fine easy way to make duplicate project. Stupid question? or stupid ME?...
9
by: Alec | last post by:
Sorry guys, stupid question.... Am no programming expert and have only just started using php for creating dynamic news pages. Then I see a dynamic website without the php extension. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.