Tlogs Full no drive space left

FrankPorter
FrankPorter used Ask the Experts™
on
Hi,

The Tlogs are full and the backup drive can't handle the size when being back-up and the backup process of TLogs fails everytime.

ms sql 2000

any idea's how to go about this?


Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Make space available on the back drive first or backup to a different drive

Author

Commented:
problem is there's not enough space available.
AneeshDatabase Consultant
Top Expert 2009

Commented:
1. Enable Auto grow    right click on ur database -> properties -> select 'transaction log ' tab  -> check "automatically grow"
   Also make sure that there is enough space

2. Take regular transaction log backups

read the following article
http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html
Database Consultant
Top Expert 2009
Commented:
you can clear your transaction log using

BACKUP LOG <urDBName> with TRUNCATE_ONLY
Commented:
Note: running BACKUP LOG 'dbname' with TRUNCATE_ONLY will truncate the log and not back it up - any data that has been checkpointed (i.e. committed and then written to the mdf) will be cleared (existing uncommitted transactions will not be!).

This means that you should then perform a full backup of your database, as you have just cleared the transaction log, making those transactions 'unrecoverable' from future transaction log restores (as they essentially don't exists!)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial