I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space. The transaction log gets backed up every hour but I think over the weekend some existing jobs to re-index some tables grew the log file to the point where it ran out of space. The first thing I did was backup the log (the database is in full recovery mode) and then ran dbcc sqlperf to check the log space used percentage. It was still at over 99%. So I did some more research and found another dbcc command, loginfo.
When I saw that output, there were almost 15K rows returned with all of status 2 and some of the CreateLSN seemed dated a while back. I checked for any open transactions and there weren't any.
It seems like the active portion of the log file is permanently keeping the file very large forcing any new transactions to request more space. As far as I can tell, there's no replication going on requiring the VLFs to stay active.
Best Answer
if this is happening only for one db, check the default initial size of the log file for that DB. I suspect the minimum size has to be large enough so that after restoring or even after backing up the log SQL server increases the log size to specified minimum size and initialize all VLF with zero.
Alternately, if you have already taken a full backup of the db try to create dummy DDL stmt, backup the DB again, restore it on another machine, and confirm the DDL operation you did. now you can detach and attach the DB w/o using the existing log file that will create new log file with the default size you have in your instance.