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

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

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

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.

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

backup log dbname with no_log
dbcc shrinkdatabase(dbname, 0)

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

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

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

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.

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