Dropping/creating procedure causes transaction log to empty Steps to reproduce:


Make sure database is in Full recovery mode
Run procedures (containing insert, updates) causing transaction log to grow
use DBCC SQLPERF(logspace) to check transaction log size
drop/create used procedures
Observe that the Log space used dropped (in my case from about 20% to 0.5% with Log size unchanged)


I am unable to determine what is going on.

NOTE: This is a dev machine, nothing escept management studio queries interact with the database
SQL Server acts like simple mode until you've established the log chain. This means that it will be happy to discard log information from already-committed transactions.

Paul Randal goes into a little more detail about the mechanics behind this here, but basically, this behavior will stop once you start proper backups:


New script: is that database REALLY in the FULL recovery mode?