Press "Enter" to skip to content

Author: Kevin Feasel

Managing the SQL Server Error Log

Guy Glantser has some tips for managing the SQL Server error log:

SQL Server maintains its own log, also called “SQL Server Error Log”. This log contains messages describing informational and error events, similar to messages that you can find in Windows logs. In fact, many of the messages found in the SQL Server Error Log can also be found in the Windows Application Log. The SQL Server Error Log is a great place to find information about what’s happening on your database server.

SQL Server uses 7 log files to store these messages. One file serves as the current log file, and every new message is written to that file. The other 6 files are archived files, and they contain previous messages. Each time SQL Server is restarted, it recycles the files. What does it mean? First, it means that a new log file is created and becomes the new current log file. Second, the oldest log file (“Archive #6”) is deleted. And third, all the other log files are pushed back one step. The previous current log file becomes “Archive #1”, the previous “Archive #1” log file becomes “Archive #2”, and so on.

Read on to see how you can change this, manage the size of log files, and retain data for a longer time.

Comments closed

Performance Impacts of Computed Columns

Robert Sheldon takes us through a few scenarios:

In this article, I walk you through the process of applying these strategies so you have a better sense of the available options. For the examples, I created four similar tables and populated them with identical data, which comes from the WideWorldImporters sample database. Each table includes the same computed column, with the column persisted in two tables and indexed in two tables, resulting in the following mix:

– The Orders1 table includes a non-persisted computed column.
– The Orders2 table includes a persisted computed column.
– The Orders3 table includes an indexed, non-persisted computed column.
– The Orders4 table includes an indexed, persisted computed column.

For each table, I show you the execution plan that’s generated when querying the computed column. The column’s expression is a relatively simple one, and the data set very small. Even so, this should be enough to demonstrate the principles of creating persistent and indexed computed columns and how they can help address performance-related issues.

Click through for Robert’s results.

Comments closed

Finding the earliest and latest date across all Power BI Tables

Soheil Bakhshi has an interesting challenge:

Many of you may already thought that we can use CALENDARAUTO() in DAX and we are good to go. Well, that’s not quite right. In many cases there are some Date or DateTime columns that must not be considered in our Date dimension. Like Birth Date or Deceased Date. More on this later in this post.

In this post I share a piece of code I wrote for myself. I was in a situation to identify the Start Date and the End Date of the date dimension many times, so I thought it might help you as well.

Read on for a solution when not all date columns are relevant.

Comments closed

Passing an Array of Arrays as a Parameter in Azure Data Factory

Rayis Imayev has a list for us:

In my previous blog post – Setting default values for Array parameters/variables in Azure Data Factory, I had helped myself to remember that arrays could be passed as parameters to my Azure Data Factory (ADF) pipelines. This time I’m helping myself to remember that an array of other arrays can also exist as ADF pipeline parameters’ values.

Read on for the example.

Comments closed

Indexing S3 Data with CDP Data Hub

Eva Nahari, et al, show how to perform indexing and serving of S3 data in Cloudera Data Platform:

This blog post will present a simple “hello world” kind of example on how to get data that is stored in S3 indexed and served by an Apache Solr service hosted in a Data Discovery and Exploration cluster in CDP. For the curious: DDE is a pre-templeted Solr-optimized cluster deployment option in CDP, and recently released in tech preview. We will only cover AWS and S3 environments in this blog. Azure and ADLS deployment options are also available in tech preview, but will be covered in a future blog post.

We will depict the simplest scenario to make it easy to get started. There are of course more advanced data pipeline setups and more rich schemas possible, but this is a good starting point for a beginner. 

Read on for the instructions.

Comments closed

A Review of KaDeck: Kafka Management Tool

Guy Shilo reviews KaDeck:

If you want the real web version then you need to register in their website and create a team first. Yes, KaDeck is aimed at teams of people working together. You create a team online and shortly you receive a mail with team id and a secret key that you will use when running KaDeck web..

It seems that there is no host installation of KaDeck right now (rpm package, binaries or so) but only a Docker image. This forces the user to run it in Docker or a Kubernetes cluster (or one of it’s commercial distributions such as OpenShift). Another thing is that checks your license online each time you start the container. If you want to use t offline you have to do a process of offline activation. You can reach it from the administration menu.

Click through for Guy’s thoughts on the product.

Comments closed

Building an HTML Report with Failed Agent Job Info

Garry Bargsley continues a series on alerting when SQL Agent jobs fail:

Welcome to Part 2 in the series about SQL Server Agent Job Failures. In this part you are going to learn how to build an HTML report with Failed Agent Job information and send the report via e-mail to the interested team(s).

Let’s get started putting the pieces together to build our report.

Click through for the details, as well as a full code sample in Powershell.

Comments closed

Spark SQL in Delta Lake

Kundan Kumarr walks us through some of the basic SQL operations you can perform with Delta Lake in Apache Spark:

Nowadays Delta lake is a buzz word in the Big Data world, especially among the spark developers because it relegates lots of issues found in the Big Data domain. Delta Lake is an open-source storage layer that brings reliability to data lakes. Delta Lake provides ACID transactions, scalable metadata handling, and unifies streaming and batch data processing. It is evolving day by day and adds cool features in its every release. On 19th June 2020, Delta lake version 0.7.0 was released and this is the first release on Spark 3.x. This release involves important key features that can make the spark developer’s work easy.

One of the interesting key features in this release is the support for metastore-defined tables and SQL DDLs. So now we can define Delta tables in the Hive metastore and use the table name in all SQL operations. We can perform SQL DDLs to create tables, insert into tables, explicitly alter the schema of the tables, and so on. So in this blog, we will learn how we can perform SQL DDLs/DMLS/DQL in Delta Lake 0.7.0.

Click through for the examples.

Comments closed

A Review of Distributed Availability Groups

Joey D’Antoni shares some thoughts on distributed Availability Groups in SQL Server 2016 and later:

I’m writing this post because I’ve been mired in configuring a bunch of distributed availability groups for a client, and while the feature is technically solid, the lack of tooling can make it a challenge to implement. Specifically, I’m implementing these distributed AGs (please don’t use the term DAG as you’ll piss off Allan Hirt, but more importantly its used in Microsoft Exchange High Availability, so it’s taken) in Azure which adds a couple of additional changes because of the need for load balancers. You should note this feature is Enterprise Edition only, and is only available starting with SQL Server 2016.

Read on for some of the positives around distributed AGs, as well as some negatives (mostly around the lack of tooling).

Comments closed

Starting and Stopping Services Things with dbatools

Mikey Bronowski continues a series on dbatools:

OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple instances is easier with dbatools.

Mikey covers services, SQL Agent jobs, endpoints, traces, and Extended Events sessions, so check it out.

Comments closed