Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 24th, 2004, 11:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default Transaction Logs

What is the best way to clear transaction logs? Should I just delete the file? Is there code I should run to do it? It is filling up my HD space and I need them cleared out.

Chris
__________________
Chris
  #2 (permalink)  
Old May 24th, 2004, 12:48 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Using the maintenance wizard you can have transaction logs automatically delete.


  #3 (permalink)  
Old May 24th, 2004, 01:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How do I do this? I opened the Database Maintenance Plan Wizard and followed it through. I can backup the database and/or transaction logs, but I do not see anywhere where I can delete or clear them out. Does backing them up clear them? Can I just delete the backup after it is done if this is the case? I do not see any reason why I need them except under backup and restore purposes. If I am wrong, somebody please tell me. We backup using Veritas which does the SQL backups for us, so I do not even use these methods from within SQL Server 2000. Thanks for your help.

Chris
  #4 (permalink)  
Old May 24th, 2004, 05:09 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

On the Maintenance plan wizard

Specify Transaction Log Backup Disk Directory. There is an option to "remove files older than"

Here you select how long to retain your backup transaction logs.

  #5 (permalink)  
Old May 25th, 2004, 09:31 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Romania.
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

this is the method i use when the database files are getting big:

backup log dbname with no_log
dbcc shrinkdatabase(dbname, 0)
  #6 (permalink)  
Old May 25th, 2004, 10:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Alright, I set up the plan through the wizard. I selected 1 database and only did the stuff on the Transaction Log Backup Page. I told it to backup and verify the backup. I told it to go to disk and gave it a path. To test, I told it to back up once every minute and delete files older than 10 minutes. It made a new backup every minute and deleted them as they got older than 10 minutes. This however did not shrink the actual Log file (.LDF), so I am not any further along than I need to be. Is there something I missed?

Chris
  #7 (permalink)  
Old May 25th, 2004, 12:54 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Since you are removing the transactions logs so often why are you using it all. Change your database to simply mode and not log transaction.

Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB.

Please Read information below (Books Online)
Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.


The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:

The most recent checkpoint.


The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.
This represents the earliest point that SQL Server still has to replicate.

Conditions for Backing Up the Transaction Log
The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running.

Do not back up the transaction log:

Until a database or file backup has been created because the transaction log contains the changes made to the database after the last backup was created. For more information



  #8 (permalink)  
Old May 25th, 2004, 01:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The 1 minute frequency was jut for testing. If a 1 gig DB can have a 128 MB, then why won't my 1.6 MB DB go below 16 MB? If it is from a checkpoint, how can I force it to actually commit what the transaction log has in it and create a checkpoint. I do not back up my DB's through SQL, I do it with Veritas Software. The Books Online is very unclear to me about the transaction logs. they tell you a bunch of information and explain it poorly and then never show you how to do what it tells you.

Maybe I just need to delete the files and let it start new ones.



Chris
  #9 (permalink)  
Old May 25th, 2004, 01:30 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

You have the option to automatically grow. If you deselect this option the file will only grow up to the amount allocated by default. . You have options in your database properties to set log and database file growth. You can set the limit as big you want but you can not shrink it below the default value.

Using veritas to backup your database. So your not backing up your database at all? From my understanding Veritas does not backup up live files MDF, LDF (as long SQL Server is running, those services must be stopped before veritas can backup those files) In order to successfully re-gain space you must backup your database to release space that was anticipated to be used by your database.
  #10 (permalink)  
Old May 25th, 2004, 02:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I restrict the growth, what happens when it fills up? Does it stop the database from accepting data? Does it start clearing out the old stuff in the file?

Chris




Similar Threads
Thread Thread Starter Forum Replies Last Post
capture error logs jemacc SQL Server 2000 1 January 10th, 2007 08:17 PM
About Web logs anshul Pro PHP 1 January 5th, 2007 04:54 AM
Keeping transaction logs down to a reasonable size afroblanca SQL Server 2000 3 August 23rd, 2005 12:31 PM
Oracle Logs echovue Oracle 1 December 22nd, 2004 10:12 AM
record logs rajanikrishna Pro VB 6 0 April 2nd, 2004 03:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.