All,

Yesterday we were happily coding along on our testing system when we ran out of space on the SQL 2000 box due to having a transaction log of 9.5 gigs.

Deleted a few files from the drive then tied to shrink the log - which actually added 125 megs to the file size each time.

Then created a new log on anther drive and tried to mark it as primary. Stopped and started SQL 2000, tried to delete the old log - no can do - tried to delete the one I just created - could not do it - now I had two logs.

Shutdown the server and deleted the log files seeing if by magic the logs would just be recreated but much smaller - as if. Database was marked a questionable, had to put them back.

Sooooooo - we backed up the database and the transaction log. Deleted the database. Restored from tape. I can now delete the log I had added. Now all of a sudden I can shrink the primary log - it goes from 9050 megs to 45, although it really takes up 750 megs of physical disk.

We press the shrink database button again - it drop to 25. Getting greedy, we press it again - goes to 52megs. Press it again, it goes to 65 megs. Plus the physical size of the log stays at 750 megs.

How does anyone get around this? Of course it should not happen again since I put a maintenance plan in effect to back up the transaction log every three hours.

What tactics do people us to keep the transaction log small?

Doug