What goes in the Log file? 1

[monkeylizard]

I'm doing the transaction log shipping that mrdenny posted in the FAQ section. (BTW - Thanks, it works great).

I'm trying to get the size of the log backup down. Any tips besides running the T-Log backup more often?

Does SQL log ALL transactions in the log file or just change transactions? Does it store SELECT statements in the log?

What's the difference between Full logging and Bulk logging? I have a paragraph explaining it from my MS training book, but I don't understand it.

Monkeylizard
If I had only known, I would have been a locksmith. -Albert Einstein-

 

[mrdenny]

All data changes are logged in the transaction log. Bascially all your insert/update/delete statements as well as the DDL.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

I'm trying to get the size of the log backup down. Any tips besides running the T-Log backup more often?

Does SQL log ALL transactions in the log file or just change transactions? Does it store SELECT statements in the log?

What's the difference between Full logging and Bulk logging? I have a paragraph explaining it from my MS training book, but I don't understand it.

First of all, doing a translog backup does not make the physical size of the t-log smaller, just the virtual size. That is, it removes inactive virtual logs only, but does not reduce the size of the log file itself.

Secondly, only statements that actually CHANGE data are logged to the t-log, not select statements. The t-log is what is known as a write-ahead log, meaning any insert, update, delete statement is logged there first, and WRITTEN to the log file (for a permanent physical record), BEFORE it is written to the actual db file. This includes even UN-committed transactions... which will get rolled back in the case of a db failure, BECAUSE the actions were physically logged in the t-log, even if they never made it to the db log itself. However, a checkpoint flushes all changes in the t-log to the db file, both committed and uncommitted.

Full logging logs everything to the t-log, even BULK INSERT statements, etc. This can cause the t-log to grow very large. Bulk-logged does NOT write BULK INSERT, etc statments to the log file... just the results of the transaction... so that if you were to lose the db file for some reason, you can NOT recover the data that was entered... you would have to run the BULK INSERT statement again to recover the data.

T-logs are a complex subject, be prepared to learn about them using BOL and be confused for a while!

I am pretty sure what I wrote here is accurate, anyone correct me if I'm wrong!

 

[mrdenny]

icemel, everything you typed looks correct. I just hadn't gotten around to typing that all out. You've saved me a bunch of typing, thanks .

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com