Transaction log

Applying Transaction Logs to the Secondary Replica in SQL Server Always On Availability Groups

July 12, 2019 by Rajendra Gupta

Introduction

SQL Server Always On Availability Groups provides HADR solutions for the SQL databases. Here HA refers to high availability and DR refers to disaster recovery. The priority of this feature is to keep the database highly available and then provide Disaster recovery. Due to this reason, if the secondary replica goes down in a synchronous data commit mode, SQL Server changes commit mode to Asynchronous so that users can continue run the transactions and a secondary replica can be in sync later once it bought up. SQL Listener also points to the primary replica and continues redirects connection to the primary replica.

How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File

May 15, 2019 by Ahmad Yaseen

This is the last article, but not the least one, in the SQL Server Transaction Log series. In this series of articles (see the TOC below), we described the Transaction Log concept from four different aspects.

Recovering Data from the SQL Server Transaction Log

May 14, 2019 by Ahmad Yaseen

If this is your first visit to this SQL Server Transaction Log series, I recommend you first review the previous articles (see the TOC below), in order not to be familiar with the concepts that we will discuss in this article. Reviewing the previous articles, you will have a good idea about the following:

SQL Server Transaction Log Administration Best Practices

April 22, 2019 by Ahmad Yaseen

In this article, we will go through a number of SQL Server Transaction Log recommendations and best practices that each database administrator needs to follow in order to enjoy his holidays and have a pleasant sleep without nightmares or calls during the night.

SQL Server Transaction Log Backup, Truncate and Shrink Operations

April 16, 2019 by Ahmad Yaseen

In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed

SQL Server Transaction Log Growth Monitoring and Management

April 1, 2019 by Ahmad Yaseen

In the previous articles of this series on the SQL Server Transaction Log, we discussed the importance of the SQL Server Transaction Log and the role that it plays in maintaining the database consistency, by ensuring that the committing transactions data will be preserved and the failed transaction will be rolled back. It also helps to recover the database to a specific point in time in case of system or hardware failure. This is achieved by writing a log record to the SQL transaction log file before writing the data pages to the physical data file, using Write-ahead Logging process.

Log shipping on a mirrored database

March 26, 2019 by Ranga Babu

Both log shipping and mirroring are high availability and disaster recovery options available in SQL Server. This article will review on how to configure log shipping on a mirrored database.

Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth

March 21, 2019 by Rajendra Gupta

In my previous article in this series Accelerated Database Recovery; Instant Rollback and Database Recovery, we talked about a potential DBA painkiller to resolve long waiting times for database recovery and rollback scenarios using Accelerated Database Recovery. In this article, we will look at one more painful challenge for DBAs, Long Running Transaction with Transaction log growth.

SQL Server Transaction Log and High Availability Solutions

March 21, 2019 by Ahmad Yaseen

In the previous article of this series on the SQL Server transaction log, we discuss the SQL Server database recovery models, Full, Simple and Bulk-Logged, and the how the recovery model option of the database affects the way the SQL Server Engine works with the transaction logs. In this article, we will discuss the different types of high availability and disaster recovery solutions and the role of the SQL Server transaction log in these technologies.

SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping

March 5, 2019 by Rajendra Gupta

This article is the continuation of the SQL FILESTREAM series.

Transaction log backups in a SQL FILESTREAM database

March 5, 2019 by Rajendra Gupta

In the continuation of our SQL FILESTREAM article series, we’ll be covering transaction log backups

In SQL Server, we take transaction log backups regularly to have a point-in-time recovery of a database. It is essential to define the backup policy with the combination of full, differential and transaction log backups. A standard configuration of backups for large databases in the production database environment is as follows.

SQL Server Transaction Log and Recovery Models

February 27, 2019 by Ahmad Yaseen

In the previous articles of this series (see TOC at bottom), we discussed the main concept of the SQL Server transaction, the internal architecture of the SQL Server Transaction Log and finally the Virtual Log File and the best practices in managing the VLFs. In this article, we will go through the relationship between the SQL Server Transaction Log and the three database recovery models.

Auditing by Reading the SQL Server Transaction Log

February 25, 2019 by Ahmad Yaseen

In the previous articles of this series, we discussed the reasons behind auditing the different SQL Server instance and database events, how to audit the SQL Server instance manually and using the Extended Events and Triggers methods. In this article, we will show how to audit your SQL Server instance by reading the content of the SQL Transaction Log file of the user database.

What are SQL Virtual Log Files aka SQL Server VLFs?

February 18, 2019 by Ahmad Yaseen

In this article we’ll review SQL Virtual Log files aka SQL Server VLFs, providing an overview and review topics related to size, performance, monitoring and troubleshooting.

SQL Server Transaction Log Architecture

January 31, 2019 by Ahmad Yaseen

This article will cover SQL Server transaction log architecture including file topography, basic overview, review of LSN and MinLSN, and log truncation

SQL Server Transaction Overview

January 8, 2019 by Ahmad Yaseen

In general, a Transaction is a single unit of work consists of multiple related tasks that should succeed or fail as one atomic unit. To make the concept of the transaction more familiar and why it should go all or none, imagine one of the most critical transaction examples in our daily life, which is withdrawing money from the ATM.

SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup

September 13, 2018 by Prashanth Jayaram

A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

SQL Replication: Basic setup and configuration

September 12, 2018 by Prashanth Jayaram

This is article is a continuation of the previous: SQL Server replication: Overview of components and topography.

By now, you’re familiar with the components of replication. So far, we’ve seen a lot of theory about replication. It’s a time for practical walkthrough of setting up a basic transactional SQL Replication system. The best way to get a feel for how SQL Replication is implemented and how it works is to see it in action.

How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases

September 12, 2018 by Prashanth Jayaram

In this data-driven era, replication is often a critical requirement for achieving a modern, agile database management environment. It is believed designing an enterprise-grade dataset is the to achieving this requirement but building datamarts from datasets always presents certain challenges

In this article, we’ll discuss what it takes to setup “central subscriber with multiple publishers” replication model, to create an aggregate dataset from multiple sources, and you’ll also see how to scale with the data.

SQL Server replication: Overview of components and topography

September 11, 2018 by Prashanth Jayaram

The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

August 31, 2018 by Prashanth Jayaram

In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases

August 29, 2018 by Prashanth Jayaram

In this article, you’ll learn how to setup a simple, custom distributed database replication system.

Introduction

In general, a typical setup of transactional replication model of a central publisher with multiple subscribers includes the creation of a replica database(s) which may serve multiple purposes including:

How to Add/Drop articles from existing publications in SQL Server

August 24, 2018 by Prashanth Jayaram

This article discusses the challenges of meeting the availability, and performance requirements of high ended transactional replication environments. In addition, you’ll learn a new innovative approach that can be used to add/drop articles from an existing replication environment while maintaining replication system up and running.

How to continuously audit transaction log file data directly into a SQL Server database

August 23, 2018 by Prashanth Jayaram

Business transformation requires solid tools to automate complex integration to seamless deployments. In today’s modern data-rich world, nothing is more important than data management, making it critical to know how to safeguard and meet compliance requirement is very critical and the key to the business success.

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

August 23, 2018 by Prashanth Jayaram

Outline

In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?