Tag: transaction log

Basics of SQL Server Transaction Log

Kenneth Igiri Leave a comment
Total: 1 Average: 5

What is a Transaction Log?

There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.

In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.

Read More

Configure SQL Server Log Shipping

Nisarg Upadhyay Leave a comment
Total: 1 Average: 5

This article explains the step by step deployment process of the SQL Server Log shipping. It is the database-level disaster recovery solution that is easy to set up and maintain.

The log shipping involves three steps:

  1. Generate the log backup on the primary database.
  2. Copy the backup to the network location or the specific directory on the secondary server.
  3. Restore the log backup on the secondary server.

The log shipping technology performs the steps described above by using SQL Server agent jobs. During the configuration process, the log shipping wizard creates those jobs on primary and secondary servers.

Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Kenneth Igiri Leave a comment
Total: 3 Average: 4.3

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More

Move Datafiles in SQL Server – Part 2

Kenneth Igiri Leave a comment
Total: 12 Average: 3.9

Introduction

In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different. Read More

Importance of transaction log in SQL Server

Prashanth Jayaram Leave a comment
Total: 7 Average: 4

Transaction logs are a vital and important component of database architecture. In this article, we’ll discuss SQL Server transaction logs, importance, and their role in the database migration.

Introduction

Let’s talk about different options for taking SQL Server backups. SQL Server supports three different types of Backups.
1. Full
2. Differential
3. Transaction-log

Before jumping into transaction-log concepts, let’s discuss other basic backup types in SQL Server. Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Kenneth Igiri Leave a comment
Total: 8 Average: 3.8

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

SQL Server Database Backup Encryption

Andrey Langovoy Leave a comment
Total: 1 Average: 3

To manage the data security that has been backed up by the file system as a database backup files, SQL Server provides the backup encryption feature. In this article, we will talk about encryption options available in SQL Server for database backups. We will have a closer look at the usage details, benefits, and recommended practices for encrypting SQL Server database backup during the backup process. Read More

Dive Into SQL Server Transaction Log — Part 3

Andrey Langovoy Leave a comment
Total: 5 Average: 4.4

This is the third article in a series of articles about SQL Server transaction log. In this article, we will have a look at log record details.

Log Records

Log records are the core of the logging and recovery mechanisms. A log record describes a single change in a database. Thus, every change to a database has a log record or log records that help to describe that particular change.  Although you do not need to understand log record details, to understand what is going on with logging and recovery, those details are extremely interesting. Read More

Dive Into SQL Server Transaction Log — Part 2

Andrey Langovoy Leave a comment
Total: 3 Average: 4

This is the second article in a series of articles about SQL Server transaction log file architecture. In this article, we will have a look at two SQL Server statements that allow examining transaction log details. If you would like to get an introductory information about how SQL Server transaction log works, read Dive Into SQL Server Transaction Log — Part 1 Read More