I was asked to look at a database server for the first time. One of the tasks I was given was to shrink the log file of a database as the previous owner could not. The DB was only 10 MB, but the log file was 50GB. No problem, I say to myself–like taking candy from a baby. This baby; however, was trained in the art of MakeMeCrazy.
Most of the advice out there prompts you to find the root cause of the growth as the log is likely to grow again so shrinking the log file is not a fix, just a band-aid. I should have pondered this question longer. This server was running SQL 2008, so the truncate_only option wasn’t available so I changed the recovery model to simple and was then going to shrink the log–only the database was already is simple mode. I tried shrinking the log and the command executed but did not change the size of the file.
Strange–why would 10 MB DB need a 50 GB log file? Can you think of a reason? I admit I was dumbfounded and actually had some strange ideas pop into my head.
The reason?? It was setup as a publisher at one point and had been forgotten about. I removed the publisher and bam–the log shrunk right down.