CODEWITHSUNDEEP
sql-servertransaction-log
veljasije
veljasije

Reputation: 7092

Writing to transaction log when log comes to full size

Let's say we have database with defined transaction log initial size to 100MB and maxsize is UNLIMITED.

SQL Server will write into log sequentially from start to end. In one book I found next sentence:

When SQL Server reaches the end of the file as defined by the size when it was set up, it will wrap around to the beginning again, looking for free space to use. SQL Server can wrap around without increasing the physical log file size when there is free virtual transaction space. Virtual transaction log space becomes free when SQL Server can write the data from the transaction log into the underlying tables within the database.

Last part is really confusing to me. What last sentence means? Does it means that SQL Server overwrite old, committed transactions with new transactions?

As far as I know, that would not be the case, because, all transactions must be presented until backup is done.

I don't know if I was enough clear, I will updtae post if needed some explanations.

Upvotes: 2

Views: 340

Answers (2)

ughai
ughai

Reputation: 9890

The log records are no longer needed in the transaction log if all of the following are true:

The transaction of which it is part has committed. The database pages it changed have all been written to disk by a checkpoint. The log record is not needed for a backup (full, differential, or log). The log record is not needed for any feature that reads the log (such as database mirroring or replication).

Further Reads,

https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

https://technet.microsoft.com/en-us/library/jj835093%28v=sql.110%29.aspx

Upvotes: 0

Dan
Dan

Reputation: 10690

This only applies to SIMPLE transaction logging:

Virtual transaction log space becomes free when SQL Server can write the data from the transaction log into the underlying tables within the database.

This means, that once the transactions have actually been written to the physical tables, they are no longer needed in the transaction log. Because at this point, a power outage or another catastrophic failure can no longer cause the transactions to be "lost", as they have already been persisted to the disk.

No need to wait until a backup is done. However, if you need full point-in-time recovery, you would use FULL transaction logging, and in that case, no transaction logs will ever be overwritten.

Upvotes: 1

Related Questions