SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Choosing the Column

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 sabbatical.

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

SSAS Error: Duplicate attribute key found when processing

aveek22 from SQLServerCentral

Learn how to resolve a duplicate value error when processing dimensions in SSAS.

Using Aliases in SQL Prompt

Additional Articles from Redgate

Phil Factor explores the role of table aliases, explaining when they are required, and their general purpose otherwise, the need for sensible naming of aliases, and how SQL Prompt handles them.

Using Calendars and Dates in Power BI

Additional Articles from SimpleTalk

You can use a host of time-intelligence functions in DAX to calculate everything from simple year-to-date totals to moving averages and average opening and closing balances. In this article, Andy Brown shows how to model various scenarios with calendars and dates, and how to get around some of the issues you may encounter.

From the SQL Server Central Blogs - PowerShell to Backup XE Session

SQLRNNR from SQL RNNR

Working with Extended Events will help you become a better DBA. Working with PoSh can also help you in many various tasks to become a better DBA. Combine the...

From the SQL Server Central Blogs - Previous SQL Server Updates

jsterrett from John Sterrett

Hello everyone, this is John your Austin SQL Server Consultant here and today I am going to answer a question that comes up often so I wanted to blog...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Choosing the Column
I set up a table like this: CREATE TABLE [dbo].[Employees] ( [EmpID] [int] NULL, [EmpSSN] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FirstName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Lastname] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Salary] [money] NULL ) ON [PRIMARY] GO INSERT INTO dbo.Employees VALUES ( 1, '12345', 'Steve', 'Jones', 120000.0000 ), ( 2, '55544', 'Andy', 'Warren', 130000.0000 ), ( 3, '77788', 'Brian', 'Knight', 140000.0000 ), ( 4, '98765', 'Tom', 'Thumb', 100000.0000 ) GO I now run this code: DECLARE @i INT = 3, @j int; SELECT CHOOSE(@i, FirstName, LastName, Salary) FROM dbo.Employees AS e GO What happens?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Commenting SQLCMD

I am writing a SQLCMD script in SSMS with SQLCMD mode. I want to add a comment as a new line in the script for other developers. What should I type for a single line comment?

Answer: --

Explanation: The single line comment character is the same as other queries: two hyphens. Ref: Edit SQLCMD Scripts with Query Editor - https://docs.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?view=sql-server-ver15

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 2017 - Administration

Finding experation date - Hi, We installed the SQL Server evaluation copy, and I am trying to find out when it will expire. Is there a way I can query the system to find this information? Thank you

Auditing logins, logoffs, better way to do it in 2017? - A bit of history - When the company I am doing work for implemented SOX practices about a decade ago, one of the things that came out was that for certain "critical" databases, all logins (and logoffs) were to be tracked, which program was used to connect, which workstation, os user and database account. At […]

SQL Server 2017 - Development

SELECT FROM sys tables does not work with 3 part database naming? - I am running a query from a utilities database, and I want to query a vendor database we have. I am running the query below from inside the vendor database and it works as expected. SELECT * FROM sys.dm_db_index_usage_stats xINNER JOIN sys.indexes iON x.object_id = i.object_idAND x.index_id = i.index_id Then when I change to […]

SQL Server 2016 - Administration

DBCC CheckDB causes TempDB grows massively ? - Hi Currently we are running DBCC CheckDB script in our server using SQL JOb every week Concern is it takes 3 hours and affect Tempdb Size grows massively after 4 times running in a row ( first time was successful and the rest is failed ) I am thinking to adjust the parameter so it […]

Replicate a Table Receiving Inserts From a Publisher and Replicate Back - Let me quickly explain my situation and I've also included a diagram. My company is in the process of migrating to a better and sound high availability design but we need to have a temporary solution for the design we have currently. For redundancy, we have a transactional replication setup from Primary to Subscriber. We […]

SQL Server 2016 - Development and T-SQL

How to add Computed column with URL (Fixed string + another column value) - Hi, I want to add a new column to the table. Eg Table A ID 1 2 3 Desire Output Table A ID URL 1 www.google.com/sessionid = 1 (i.e 'www.google.com/sessionid'+ = ID) 2 www.google.com/sessionid = 2 (i.e 'www.google.com/sessionid'+ = ID) […]

Dynamically Choose Columns Names - Hi, From the following script, I am able to choose one column rows from #TableB to columns Headers to #TableA. But I would like to dynamically choose which column rows needs to be select from #TableB CREATE TABLE #TableA (Type int, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int); CREATE TABLE #TableB […]

Administration - SQL Server 2014

DB Mirroring - Mirror server went down completely.Principal server is up and running. So once the Mirror server is back online, does the DB Mirroring will be re establish or the mirroring needs to be reconfigured? There is no witness and it is configured as async mode. Thanks

SQL Server 2019 - Administration

Sudden failure of Kerberos delegation with linked servers - This is not limited to SQL 2019, my issue is that suddenly on 1/8 all the linked servers on various SQL servers, that use Windows authentication, stopped working due to delegation failure ("Login failed for NT AUTHORITY/ANONYMOUS LOGON"). Kerberos authentication works fine on all servers, but not delegation. This applies to a variety of SQL […]

SQL Server 2019 - Development

group by ignore null value - Hi, I am not actually a programming but my job did need to write a bit of sql queries. Hope someone can help me here is my code select i.item_code,i.cost_standard,(select item_price.item_price where item_price.price_group = 'wholesale') as A,(select item_price.item_price where item_price.price_group = 'vnd')as B from item i left join item_price on i.item_code = item_price.item_code where i.item_code […]

How to Select Headers dynamically from another table - Hi, From the following script, I am able to choose one column rows from #TableB to columns Headers to #TableA. But I would like to dynamically choose which column rows needs to be select from #TableB CREATE TABLE #TableA (Type int, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int); CREATE TABLE #TableB […]

Integration Services

Excel Connection Error: "External table is not in the expected format" - I hope your are doing well. I've been breaking my head since yesterday. I hope you can help me. I have an error on the production server. SSIS can no longer connect to an Excel file. I have an error "external table is not in the expected format". I work with Excel 2010 32 bits […]

General

Where is the Forum FAQ? - Where can a forum user find the FAQ or details on how to perform forum actions like deleting a post? I assume you can delete a post but I can't figure out how to, there is no DELETE link like there is an EDIT or REPLY link and its not clear within the interface how […]

Performance Tuning

Why is this query slow? - #pastetheplan https://www.brentozar.com/pastetheplan/?id=SJF21m0lU I'm trying to figure out what's causing this query (part of a stored procedure) to be so slow. The NOLOCK hints were an earlier attempt to stop deadlocks, I'm guessing they are likely no longer necessary. I did add an index that helped a ton, but this is still pretty slow and I'm […]

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.