SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Operator Precedence Order

The Voice of the DBA

What Write Ahead Logging Is and Why It Matters

Today we have a guest editorial from Kendra Little as Steve is away on his vacation. This editorial was originally published on Jan 20, 2020.

Every so often, I like to return to the concept of “Write Ahead Logging” (WAL) and write or talk about why understanding it is essential for those who work with relational databases. This isn’t a new concept, but that’s exactly why I go back and write about it: this is an old thing that many folks may not learn about if they tend to learn on the job and don’t have a computer science background (or if they have that background but it’s faded into the recesses of their memory). And when looking at the internet, it’s not always easy to tell which older blog posts and topics are still relevant.

Write-ahead logging is still extremely relevant: it’s a core concept for SQL Server and many other database systems.

The “ahead” in “write-ahead logging” means that SQL Server writes to the transaction log ahead of other database files. When a modification occurs, the change will be made in memory and then written to the transaction log file. If the write to the transaction log file doesn’t complete, the transaction cannot commit.

SQL Server’s database recovery model controls how the write-ahead transaction log is managed in important ways, including how much information is written to the transaction log, and how long data will remain in the transaction log.

There are a few reasons why understanding transaction log basics and recovery models is essential for both database administrators and developers:

Understanding these concepts is needed to properly implement and maintain working backups and high availability/disaster recovery protection
Understanding write-ahead logging and recovery models helps you quickly identify risky troubleshooting advice which may cause data loss – essential for DBAs in a high-pressure scenario
Performance of write operations can vary dramatically with minimal logging, but you need to understand how to get it as well as the implications of your recovery model

If you haven’t yet spent time learning about recovery models and transaction logs in SQL Server, carve out a few hours of time in 2020 to deepen your knowledge on these essential concepts. A good place to start is our Stairway to Transaction Log Management.

Kendra Little

Join the debate, and respond to today's editorial on the forums

Featured Contents

Memory Grant Feedback Persistence in SQL Server 2022

Almighty from SQLServerCentral

By using input from real memory consumption during query execution, Memory Grant input Persistence in SQL Server 2022 is a potent feature that helps optimize query performance. In order to improve the accuracy of memory grant calculations for subsequent executions of the same or comparable queries, SQL Server gathers and stores this information.

PostgreSQL is Now Faster than Pinecone, 75% Cheaper, with New Open Source Extensions

Press Release from SQLServerCentral

Two new open-source extensions from Timescale make PostgreSQL even better for AI applications, unlocking large-scale, high-performance AI use cases previously only achievable with specialized vector databases like Pinecone.

Snake draft sorting in SQL Server, part 4

Additional Articles from SQLServerCentral

In the previous posts in this series (part 1, part 2, part 3), I described how I have optimized a long-running set of routines by processing databases, tables, and even subsets of tables in parallel. This leads to many separate jobs that all kick off at roughly the same time

From the SQL Server Central Blogs - ERD Your Existing Databases

hellosqlkitty from SQLKitty

There are several tools out there to make your life easier by creating an ERD for your existing db. Everything works pretty well when you have a small number...

From the SQL Server Central Blogs - Microsoft Build event announcements on Fabric

James Serra from James Serra's Blog

There were a number of Microsoft Fabric announcements at Microsoft Build yesterday that I wanted to blog about. Everything announced at Build can be found in the Microsoft Build 2024 Book of...

Learn T-SQL Querying

Additional Articles from SQLServerCentral

Troubleshoot query performance issues, identify anti-patterns in your code, and write efficient T-SQL queries with this guide for T-SQL developers

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Operator Precedence Order
What is the correct order of operator precedence from highest (evaluated first) to lowest?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Collins)

Is date zero valid and if so what day is it?

You run this code in SQL Server

select datename(weekday, 0);

What is the result?

Answer: Monday

Explanation:

In SQL Server the integer value '0' represents the base date of '1900-01-01' which was a Monday. Positive integers represent dates after this base date.

The code below returns 'Tuesday'

select datename(weekday, 1);

Ref: datename - https://learn.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 - Development and T-SQL

SQL Plan Guide usage - Is it possible to see how many times a SQL plan guide has been used?

Transferring millions of records in live production - Hi we have req of transferring huge data from live table to another table in production env. Live table means simultaneous insert will be going on when transfer of data is happening. For e. G table A at 11 in the night has 5 million records. At 11 it is decided to shift this 5 […]

Parse Double Quoted comma separated data with comma - I've used this function before and I'm sorry I do not recall it's origination. (DelimitedSplit8K) It works as expected however there is a comma in the data that I am trying to parse. I've poste here the situation that I am trying to resolve and would greatly appreciate any help this accomplish the task of […]

SQL 2012 - General

Replication help, please! - I cannot get a snapshot to generate for one of my five publications. The snapshot gets to 99% complete and then goes to status: "The process is running and is waiting for a response from the server." Eventually, it restarts from 0% again. I've been playing this game for hours, and now I don't know […]

SQL Server 2019 - Administration

Run SSIS package asynchronously from SQL Agent job step - I'd like to run an SSIS package asynchronously from a SQL Agent job step through the the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures. I need the target package to be run by a specific login, so that login has been set as the owner of the SQL Agent job. However, the job step returns the error: […]

Creating an Index with a lot of Columns - Hi all. I have been working on SQL Environment that are really not well due to legacy system mixed with new systems. Need to ask bit of stupid question. I have run few different SQL Scripts to recommend Creating of indexes including BrentOzar FirstResponderKit. Mostly I use FirstResponderKit scripts. Here is where I am bit […]

What is re-enabling Ole Automation Procedures? - Hello experts, I'm trying to disable (0) Ole Automation Procedures via sp_configure. But each time I do so, the next day the setting has been re-enabled (1). I've tried a SQL trace but didn't catch it. Maybe I need to revise the trace. And there may be a Windows policy in place doing this but […]

SQL Server 2019 - Development

Index Scan instead of index seek - Hello guys, I have the following query: SELECT COUNT(*) FROM "dbo"."STXL" INNER JOIN "dbo"."STXL_RET" ON "STXL_RET"."MANDT" = "STXL"."MANDT" AND "STXL_RET"."RELID" = "STXL"."RELID" AND "STXL_RET"."TDOBJECT" = "STXL"."TDOBJECT" AND "STXL_RET"."TDNAME" = "STXL"."TDNAME" AND "STXL_RET"."TDID" = "STXL"."TDID" AND "STXL_RET"."SRTF2" = "STXL"."SRTF2" AND "STXL_RET"."SPRAS_ISO" = "STXL"."SPRAS_ISO" WHERE "STXL_RET".ProcessName = 'ISAM_EKKO_T1' AND "STXL_RET".BoName = 'MM_EKKO' And the following indexes: STXL_RET […]

SQL Azure - Administration

SQL DB migration - Hi I am New To Azure Sql. Can some one please share If we can migrate sql 2017 db with symmetric key and certificate in it on Prem to Azure SQL database? how to restore db from one azure sql database to another azure awl database? Thanks

Database Engine Tuning Advisor in Azure - Hello, all We have several performance issues, which I think could be mitigated by proper indices, better queries or even partitioning as some tables have over 1B records. I was hoping to use Database Engine Tuning Advisor, as I would do on my local copy of the DB in my previous roles, but seems it […]

Azure Data Factory

deploying datafactory using YAML script - Error - Hi All, First foray into deploying adf using YAML scripting from Azure Devops from a dev resource to a test resource group..am using boilerplate code YAML code cribbed from the internet. The artifacts have been created correctly so that bit works. Deployment the second half requires creating in my azure project settings a service connection […]

SQL Server 2022 - Administration

Servers being moved go new location - new IP addresses. - Hi Our Physical servers are going to be moved to a new location and new IP addresses will need to be assigned to the SQL servers. We use High Availability so will the listener sub net and IP need to be changed? I cant think of any other pain points - am I missing something? […]

Contained Availability Groups - Failover Questions. - Hi I am considering creating Contained Availability Groups in my production environment. Thinking I need to drop disabled jobs that live on both servers first? I've searched and read allot of Contained Availability Groups articles but none of them that I can find describe the failover process once the Contained Availability Groups are setup. During […]

SQL Server 2022 - Development

Using USE does not set DB context - Hi Experts, I have a Sql file with a bunch of DDL statements (Create Tables/Views). I start with a DB then change DB context to another DB followed by 2nd set of DDLs. In Sql file, I have: DECLARE @DB1 NVARCHAR(MAX) = 'DevDB', @DB2 NVARCHAR(MAX) = 'TestDB', @ENV NVARCHAR(MAX) SELECT @ENV = 'USE ' + […]

blocking due to cuncurrency problem. - hi, CREATE TABLE [dbo].[bridge]( bridgeid [bigint] NOT NULL, [idpool] bigint NULL, status int null CONSTRAINT [PK_bridge] PRIMARY KEY CLUSTERED ( [bridgeid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] Go bridgeid is pk , and […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.