Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Ah, the joys of 2000. +1 for sympathy. –  Jon Seigel Sep 29 '13 at 20:05
    
@user27810 there has to be something that is holding up the transaction log. Do a checkpoint and try backing up the log and see if the status changes to 0. Status = 2 means that the log is active and cannot be truncated. See lazy log truncation (blogs.msdn.com/b/sql_pfe_blog/archive/2013/06/27/…). As a last resort BACKUP LOG WITH TRUNCATE_ONLY is supported in sql 2000. Make sure to take a full backup after you destroy the LSN of your log. –  Kin Sep 29 '13 at 20:13
    
UPDATE: Ok, I think I found the cause. The database, at one point, was involved in replication even though there was no evidence of any subscriptions/publications or the presence of a distribution database. I basically started the replication configuration process and then turned it off. About 30 minutes later, all the VLFs were inactivated. –  user27810 Oct 5 '13 at 0:36
add comment

1 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.

share|improve this answer
    
-1 since the VLFs are marked "in use" (status 2) which means your first paragraph won't help. Your 2nd paragraph is not at all clear. –  Max Vernon Sep 30 '13 at 3:11
    
as the second option i was suggesting trying to restore the DB on another machine (sql2005). Perform Detach DB operation, remove the Logfile from original location, and perform AttchDB operation w/o selecting Logfile so the SQL server will create new with the default size which should give us clean new log file. –  Anup Shah Sep 30 '13 at 21:52
    
Also this was interesting to as well as I wanted to figure out what is holding that much of LOF log Active. i have come across this on different version but never dive in to the actual reason. thought I found interesting reasons for same issue on SQL 2005 version. stackoverflow.com/questions/779153/… social.technet.microsoft.com/Forums/sqlserver/pt-BR/… –  Anup Shah Sep 30 '13 at 21:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.