So… I'm not a SQL Server DBA but was asked to look at a customer's situation where they have a 2GB database that has a 60GB transaction log. I've read up on a whole lot of articles on the web (Aaron Bertrand's information comes up frequently) but am still unable to explain the reason or the fix.
The database is in SIMPLE recovery mode. The log file has 0 available space. I've tried to shrink it and only reclaimed ~6GB — I know this is bad but the customer is at 1.5GB of free space on the drive so they're concerned about the other DBs on the instance.
I know the application does a huge number of imports but not sure if that's the reason for the huge log size and performs replication. I ran looked at the sys.databases information:
[REDACTED-DBNAME] 46 NULL 0x01 2014-04-30 17:43:52.713 100
SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0
OFF 0 3 SIMPLE 2 CHECKSUM 0 1 0 0 0 0 0 0 0 0 0 0 0
0 1 0 0 0 0 1 0 0 0 0 {REDACTED-GUID} 0 6
REPLICATION 0 0 0 0
The only different/possibly-interesting information for this DB (relative to the others on this instance):
is_auto_create_stats_on = 1, where almost all other DBs are 0
is_published = 1, where almost all other DBs are 0
log_reuse_wait = 6, where almost all other DBs are 0
log_reuse_wait_desc = REPLICATION
The logs file was set to 10% growth with a max size of 2GB but it's long since blown that out of the water. I reduced the 10% auto-growth size down to 25MB but left the 2GB — even though the max size apparently has no effect.
A full backup was performed in troubleshooting and it made no effective change to the log file size.
A lot of what I seem to have read indicates that logs grow because of applications or operations and that you should just let it happen but a 2GB database and a 60GB log file seems absurd?
Or are my minimal SQL skills just incorrect in thinking this is bad? Any guidance would be greatly appreciated.
Best Answer
If either of the comment-helpers post an answer, I'll re-mark the answer for them. I keep checking and will eventually forget to come back to check so I'm going to answer in case someone else runs into this:
The problem was exactly what Kenneth Fisher, 8bit, and Kin thought, the Transaction log was gigantic on my small database (60GB/2GB, respectively) because of failed replication.
Replication had been configured on the database but disabled for about 7 months. I assume SQL was queuing up all of the changed-data for replication once the the repl-configuration became re-enabled.
This was absolutely a case of misconfiguration. The customer had disabled replication in testing, moved away from using replication, but never went back and deleted the config, thereby, over time, creating the problem.
After going into SQL Replication in SSMS and deleting the configuration, ~3 mins later, the log file went from 60GB to 43MB.
Now, I'm not sure if it would've done that on it's own. I ended up running 'Checkpoint' on the database 2x as was previously suggested with no immediate effect. Immediate checks after those operations yielded no results. Spot-checking the log file size a few minutes later saw the dramatic difference so again, I'm not sure if I needed to run the checkpoints but ultimately, the transaction log effectively disappeared as a result.