Even with the SIMPLE recovery model, the trans log can still blow up if there's a long running transaction going on. I'm wondering if something on your Dev server was running while your script was doing its thing and cause the trans log to not be able to truncate since the MinLSN was from some long running transaction.
To answer the question though, unless you restrict access to the DB (like running your script using ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE or something similar like SINGLE_USER), there's no way you can force the log file to free space as other transactions may be running and the trans log has to stay consistent for those transactions.
Check TechNet (or BOL: Checkpoints and the Active Portion of the Log) for more information.
The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT
command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.
This is also why you can't rollback this INSERT
: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.
You can see what I mean directly from inside SQL Server.
Microsoft ships SQL Server with a DMF called sys.fn_dblog
that can be used to look inside the transaction log of a given database.
For this simple experiment I'm going to use the AdventureWorks database:
USE AdventureWorks2008;
GO
SELECT TOP 10 *
FROM dbo.Person;
GO
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO
SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO
Here I'm doing two inserts: one with and one without an explicit transaction.
In the log file you can see that there's absolutely no difference between the two:
The red one is the INSERT
within an autocommit transaction and the blue one is the INSERT
with an explicit transaction.
As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.
Best Answer
No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery.
Quote from the Data Loading Performance Guide:
Re trace flag 610, what that added was the ability to do minimally logged inserts to tables with clustered indexes ( and/or non-clustered indexes ) with data already in them. This was not previously available.
The thing to bear in mind about minimally logged transactions as that as soon as a transaction log backup contains one of these, you lose the ability to do point-in-time restore with that backup. So hopefully now you understand why these are not available in Full recovery model