Sql-server – Defragmenting Indexes Bloats Log Files

I think this is kind of the same question as the other one you just posted. there are a couple of new points you address:

Bulk logged mode doesn't make the logs shipped over the wire smaller. It just makes writing to the local log file less IO intensive. Once the log has to be backupped, alle changed pages haved to be included in the backup. The end result is that your trans log backup will be just as big.

If you already have High latency, changing to mirroring will not change that. the only way to spread the load is: stop doing rebuild and change to a spread reorganize strategy (as I mentioned in your other question.)

Or as somebody else there mentioned, start doing more frequent log backups and ship smaller log backups in a higher frequency.

Just based on what you are telling us now. (the high latency) I personally would not advise to change to mirroring. But the choice between mirroring and log shipping should be based on many more items then only latency.

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.