A recent problem I encountered on two different, yet unrelated, locations (a client and a forum) caused me some grief. As an introduction to the problem, observe the life cycle of a particular database common to a lot of production installations. As customary to a lot of ad-hoc built systems a database gets created by a team-leader or some other non-dba person in a dev server environment with little concern for the concept of transaction logs. Over time some funky voodoo magic happens to this log which eventually gets transferred as detached mdf/ldf combination and gets reattached in a production environment, after all who wants to create database by script and then have to import all those pesky preloaded lookup tables and other stuff, its much easier to just dump the whole thing on a prod server and turn transaction log into SOP (e.g. somebody else's problem). How rude...

Lets observe the genesis of this database, default log usually boils down to a full recovery model log with default size setting of 1Mib:+10%:unrestricted growth. I wont even start with why this is wrong, the blogs are full with wisdom on how you should use simple recovery if you don't really need a full recovery; however, eager beavers the devs are, they want to go straight to creation of tables and procedures, not waste precious creative time think about logs; thus one next, next and finish later we have a default database. If you run a dbcc loginfo on such a database, you'll get something like this:

FileId  FileSize  StartOffset  FSeqNo  Status  Parity  CreateLSN
2       253952    8192         20      2       64      0
2       253952    262144       0       0       0       0
2       253952    516096       0       0       0       0
2       278528    770048       0       0       0       0

Essentially, the SQL server split the initial size into 4 VLFs (virtual log files) since the initial size is below 64MiB and started consuming the first (status=2), each VLF is about 256KiB is size. Anyway, all's well so far and as the work on the system progresses there are a lot of bulk updates, inserts all sorts of voodoo magic we spoke of and the transaction log starts growing and start slowing them down. Nobody is backing it up since its a dev system (who the hell needs a log backup there?) therefore no truncing occurs and the ldf file grows and grows and then someone sees it and decides to do something about it. Now devs wouldn't be good devs if they didn't optimize whenever and wherever possible, and without a deeper understanding of log file operations they come up with a magic formula which you can also find on the "SQL Authority" blog:

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Let’s take a moment and examine this, what exactly is happening to our log file here? First shrink file dumps all status=0 VLFs from the end of the file (initial shrink), then it pads the last active VLF with fake transactions until it causes log file to rewind back to the first VLF in the log file, effectively placing the transaction pointer the the very beginning of the file and allowing all other VLF's to be inactivated if possible. The backup log statement does exactly that, backups the transaction log (actually discards it here because of TRUNCATE_ONLY, a big blunder that only dev server should ever see) and inactivates/truncs all VLFs. Finally the second shrink can shring the log file to a desired size (1MiB) because all (except the first) VLFs are status=0. Nice and dandy, problem solved, or is it?

Now the database goes to production. Let's assume the dev at least did the upper shrinking process before detaching the database (if for no other reason than to fit the mdf/ldf on a flash drive ;)). It gets to production and gets attached. The problem is so are the log file settings and the potential for problems that was seen in development. Users start pounding the database, the percent growth kicks in, each 10% increment causes a new chunk of 4 VLFs to be added to the log file, and by the time the dev leaves the server room there are already hundreds of VLFs, by the time the first log backup kicks in to inactivate the VLF's and allow SQL server to rewind the log file we could be dealing with few hundreds even thousands of VLFs, heavily fragmented by anyone's standards. The clients are not happy with the performance (nor should they be) however the database is already in prod, one can not just delete it and start over, even if one could, upon single file attach the ill mannered SQL server will auto-create a log file, one even worst than the one brought along. The smart-ass DBA comes along and shares the wisdom:  "the log file needs to be created with larger initial size and with fixed growth increments of appropriate size and it should not be shrank unless it goes above a certain value, blah, blah" and goes ahead and does it. Want to know how and why you should do it differently? Read part 2 and impress the DBA smart-ass ;)