SQLTeam.com | Weblogs | Forums

Transaction log growth

SQL Server Administration
#1

I work for a government agency. Our backups are handled by OIT. But we also keep a couple backups of a crucial database on hand in case we need it and do not want to wait for OIT.

The database is configured as Simple recovery model. The perform a COPY_ONLY backup each night so it does not interfere with the OIT backup. As I understand it that should clear the log file each night. Yet on our development server for one database the log file has grown to 31GB. The log file on production server for the same database is about 250MB and is empty. There are no open transactions and I have tried shrinking the file but the file is 90% full on the development server.

Any suggestions on how to reduce the size of the transaction log to around 250MB like it is on our production server?

#2

so which one is in Simple recovery mode?

#3

The other possibility is that the log file has used records at/near the end of the physical file. Since a log file is sequential, SQL can't free records earlier in the file while later records are in use.

You can see which rows are active by using the command:
DBCC LOGINFO

#4

Both

#5

So crucial government database in Simple Recovery mode? Scary unless you are doing differentials in between evenings.
Try to do a full backup of the development server and see what happens.

As you said "As I understand it that should clear the log file each night"

#6

You don't have to take a full backup in SIMPLE recovery model for SQL Server to truncate the log file, it will do that automatically at every CHECKPOINT unless an open transaction or something else explicitly prevents it.

Which record(s) does DBCC LOGINFO show as "in use" (status <> 0)?

#7

Thanks. The issue was with replication on that database. I ran sp_repldone and then ran a backup and all is good.