Write process in TRASACTION LOG (.LDF) file in SQL Server

I have written one article about Transaction Log File (.LDF) yesterday, today I am presenting one more article on the same subject.

There is a myth going on in the mind of many developers that only DML statement like INSERT/ UPDATE & DELETE are being written in the log file but the truth is that SQL Server writes DDL operation like CREATE TABLE or CREATE INDEX etc. in the log file too.

Let us see this fact by small demonstration.

  • Open SQL Server Management Studio
  • Keep two query window open
  • In First Query window, execute following TSQL Code which will begin one transaction & create one SQL Server table but wouldn’t COMMIT or ROLLBACK
Begin Tran
Create Table TestTable
(
Col1 INT
)
  • Since our CREATE TABLE script ran successfully in first query windows, we will execute simple SELECT statement on this table in second Query Window
SELECT * FROM testtable
  • Your SELECT statement won’t finish execution until you either COMMIT or ROLLBACK. So let us now go to first query window and execute following statement
ROLLBACK
  • If you go to your second query window, you will observe that your SELECT statement would have finished execution with error something like “Invalid Object name ‘Test Table”
  • Now again go to First query window and execute following command once again.
Begin Tran
Create Table TestTable
(
Col1 INT
)
  • Since our CREATE TABLE script ran successfully in first query windows, we will execute simple SELECT statement on this table in second Query Window
SELECT * FROM testtable
  • Your SELECT statement won’t finish execution until you either COMMIT or ROLLBACK. So let us now go to first query window and execute following statement
COMMIT

Now look at your second query window and you will see your SELECT query has successfully finished execution.

Isn’t this proves that DDL commands are written in LOG file too?

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn


Note: Microsoft Books online is a default reference of all articles but examples and explanations

2 thoughts on “Write process in TRASACTION LOG (.LDF) file in SQL Server

  1. Bihag Thaker

    That is the feature “Transactional DDLs”. I liked the PostgreSQL most with respect to this feature. It fully supports and gives control of rollbacking of full DDL scripts which is very good feature for deploying the scripts on production server. If some error occurs at half of the scripts, the database is not left half-updated.

Comments are closed.