Tag Archives: LDF

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

Transaction Log File (.LDF) in SQL Server along with ACID property and recover mode of database

SQL Server databases created with two types of file:

Data File (File with extension .MDF/.NDF)

Log File (File with extension .LDF)

Data File can be one or more than one but physical log file will always remain one for each database. You have control over the size of log file but you can’t have more than one physical log file.

There are so many mysteries and myth roaming around log file in developer’s mind so I thought to take an initiative to break this ice.

What is the need of Log File?

Log file plays an important role for SQL Server databases. Any DML or schema change TSQL you apply, it first goes to log file and later on it will be save to data file. By having this kind of architecture, SQL Server ensures your data integrity. If anything wrong happens to your TSQL command and it doesn’t get finished then SQL Server can ROLLBACK it or even user can do it too if transaction was used.  Because of LOG (Transaction Log) file, SQL Server guarantees ACID (Atomicity, Consistency, Isolation, and Durability).

Before we move further, it is worth to look at the definition of ACID.

Atomicity ensures that either all operation in one transaction apply or none. It means that either 100% COMMIT or 100% ROLLBACK.

Consistency ensures that a transaction either creates a new and valid state of data, or returns all data to its state before the transaction was started. It means that you have guarantee that a transaction never leaves your database in a half-finished state.

Isolation ensures transaction separation until they are finished.

Durability ensures that the data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

Since we can have more than one database data file, we can either keep it on one drive or may distribute it over multiple disks as per our requirement of performance as well as disk space but we can have only one transaction log file (.LDF) so it is necessary to have proper size of .LDF file based on database requirement.

Note: Though we have only one transaction log file, we can have multiple Virtual Log File (VLF) inside our one transaction log file. This is different topic and out of the scope of this article so I will come up with separate article on this topic.

There is no rocket science which suggests you exact size of your database transaction log file. It is completely up to the requirement of business which vary from case to case. All I can suggest is neither keep log file too big then your requirement nor keep it too small.

Bigger transaction log file takes time to recover and somehow it affects the overall performance of database too at the same time, smaller log file size resulted into frequent trigger to auto grow transaction log file size.

Prevent transaction log file from frequent auto grow; keep its size reasonably big to accommodate your business transaction. No matter how big transaction log file you have, it used to be filled completely sooner or later and at that time, you have no option other than either to increase transaction log file size or truncation log file.

I personally against both the way I have mentioned in above paragraph. We shouldn’t truncate log file, not at least if we have other alternative because truncating the log file increase the risk of break up in log backup chain.

I would suggest taking transaction log backup frequently which helps you in two ways:

1.)    You can restore your database in point-in time whenever needed

2.)    It free-up unused part of log file so that new transaction can use that free space inside transaction log file and you don’t need to grow it much

Last but not the least, we have three recovery mode (Simple, Bulk-Logged and Full) for database which affect our transaction log file very much.

Simple: This recovery mode don’t use transaction log file too much. It writes the data in transaction log file and as soon as checkpoint runs and data transfers to data file, it truncate transaction log automatically so there is no possibility to take transaction log backup and hence in time of crisis, we can only restore the last full backup of data base available.

Bulk-Logged: This recovery mode uses transaction log file and log all DML operation except BULK Insert, BCP, CREATE INDEX,  SELECT INTO and text & image operation. We can take transaction log file backup in this mode. In time of crisis, we can have full database backup and all transaction log backup we have.

Full: This is one of my favorite recovery modes for mission-critical production database which logs each and every transaction. It helps to achieve Point-In time recovery.

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

Database file size in MB in SQL Server 2005

I seen people ask in many forums that how can they find the size of their MDF and LDF file in MB by T-SQL so I thought to publish it here in my blog.

select ceiling(size/128) as ‘fileSizeinMB’,name as ‘filename’,physical_name from sys.database_files

It will return all available files for your database.


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah