Getting Started with SQL Server Transaction Log

SQL server transaction log records of all the changes made on the database while the actual records are placed in a separate file. This file is known as transaction log file and the file extension for transaction log file is .ldf. Transaction log holds enough record to undo all the changes made on the database. It is the most critical component in the SQL server that can recover database if there is a system failure.

Note: Never delete or move transaction log unless you know the effect of this.

As we have seen above transaction log file uses a transaction log file (.ldf) to records all the changes made on the database and the size of this file grows as per every change on the database. SQL server offers two designed to counterbalance the size of transaction log: transaction log truncation & log file shrinking.

Transaction Log Truncation: SQL server automatically removes the entries from the transaction log file. The frequency of record removal depends upon the recovery model used in the database. If you have chosen full or bulk-logged recovery model for your database then SQL server truncates the transaction log file when you perform backup. In case of simple recovery model, sql server truncates the transaction log file at every transaction checkpoint.

Note: It is recommended to use full recovery model for production database.

Transaction Log File Shrinking: Transaction log truncation removes the entries only from the log file; it does not reduce the size of transaction log file. If you know there is a unused space in your transaction log file then you can shrink your log file and this process is know as transaction log file shrinking. Run below T-SQL command to shrink a transaction log file:

DBCC SHRINKFILE (, )

Sometime, you do not finish with above command. Try following code to shrink the transaction log file.

USE DatabaseName

GO

DBCC SHRINKFILE(, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(, 1)

GO

Note: You can't shrink a transaction log file when your database is offline.

Backup Transaction Log: If you are using simple recovery model for your database then you can't backup the transaction log, if you still want to backup then you have to change the recovery model for your database first. Read my article “Twoways to Change the Recovery Model”. After changing the recovery model for your database, you can perform backup by following T-SQL command:

BACKUP LOG Your-Database-Name

TO Your-desired-location;

GO

»»  Read More...

How to Prevent SQL Server LDF file growing unexpectedly problem?

Each database in SQL server contains at least one data file and one transaction log file. SQL server database stores data physically in the data file. A transaction log file contains all the modification related information performed on the database. It is logically divided into small segments that are known as virtual log files. Database administrator is responsible to maintain the size of transaction log file. Sometimes, size of transaction log file grows unexpectedly because database administrator has not planned for the transaction log files grow.

I have seen several related questions on the different forums like how to shrink transaction log file? My transaction log file is growing unexpectedly, what to do? How to reduce the size of transaction log file?  Before going to solve these problems, first we will discuss about how to prevent this problem because prevention is the best method to avoid problems. Following points are very helpful to prevent transaction log file growing unexpectedly….

• Avoid auto grow of transaction log file.
• Take back-up of transaction log file regularly and delete the inactive transaction log file of your database.
• Stop the uncommitted running transaction.
• Design the transaction log file to be small.
• Change the recovery model; there are three types of recovery models available in the SQL server, simple, bulk logged and full recovery model. Simple recovery model automatically shrink the transaction log file of your database while bulk-logged and full recovery model does not. If you use simple recovery model then you can recover SQL database from most recent backup. By the using of bulk-logged & full recovery method, you can recover your database to the point of failure.  

If you have followed above points before creating your database then I am quite sure you will not face above discussed problems. In case still facing same problem then run below command to shrink your transaction log files.

DBCC SHRINKFILE
(
 {File name | file id}
{[, emptyfile] | [[, tragetsize] [, {notruncate | truncateonly}]]}
)
[With no-infomsgs]

»»  Read More...

How to backup the Transaction log in SQL Server?

In this article, you can read about “how to backup the last transaction log” in Microsoft SQL server database 2000 & 2005, and importance of transaction log file.

A transaction log file in SQL server is a file that contains all the information related to the changes made on the database, like update, delete, rename, insert, drop, and all. The file extension of the transaction log file is .ldf. In MS SQL server 2000 and 2005, if the master database and data files of user database are corrupt, and the transaction log file of the database is safe then a database administrator is still able to backup the last active transaction log of the database. Transaction log file backup reduces the amount of data loss. It also plays an important role in SQL server database recovery process.

A database administrator can make the backup of last transaction log in MS SQL server 2000 & 2005 by following these steps:

• Rename the transaction log files of SQL server database.
• Rebuild the master (system) database.
• Create a new database with same number of data and log file.

Note:  The new database does have the same size.

• Stop the SQL server database.
• Delete all the data files of new database.
• Replace the log files of newly created database with old log files.
• Backup the transaction log files.
• Restart the SQL server database.
• Run given below command.

“Backup Log to Disk = With NO_TRUNCATE”

• Remove the database by using sp_dbremove stored procedure.
• Now you have backup of last transaction log.

Importance of Transaction Log: There are so many importance of transaction log in SQL server database. Some of them are as follows:

1. You can undo all the changes made on the data files with the help of transaction log.
2. It is helpful in restoring the database.
   Note: It helps in the restoring process but not restore database without data files.
3. You can reduce the size of transaction log easily.

»»  Read More...