I wanted to know if there is any way we can trim the transactional log file in MS SQL 2000.
My log file has reached 2 GB and has started usurping a lot of HDD space. Is there anyway to prevent/control the same.
A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.
The backup technique actually works. When before backing up the database my disk space utilised was about 3 GB data file and log file together. So I created a blank database. Imported the database from the old one. Dropped the old database. Created one more new DB with the old DB name and imported the data from the dummy DB. My disk space reduced from 3 GB to 250 MB.
Glad it works for you. From what you described on deleting and recreating the database to recover the disk space (If I understand it properly), I think that you could have used either DBCC SHRINKFILE or DBCC SHRINKDATABASE to accomplish the same thing. Books online has good descriptions on using these commands.
If you setup a backup device that backs up the database on a regular basis and overwrites itself you should not have any future problems with the logfile disk usage.