Press "Enter" to skip to content

Author: Kevin Feasel

Restoring a Database in Standby Mode

Published 2021-12-13 by Kevin Feasel

David Alcock points out a useful database restoration mode:

Here’s a scenario. A user has made several modifications to a database and now needs to restore the database back to a particular point. The problem is that they don’t know the particular time to restore back to, just that they need the database back to before a particular change was made.

If the database is in simple recovery then there’s no options to play with, the database can only go back to the last full and maybe differential backup if they’ve been taken. If the database is using full recovery (I’m skipping over BULK-LOGGED for this post) then we can then apply the transaction log backups taken after the full backup to get back to a point in time by restoring the database with NORECOVERY and then restoring the necessary log backup files until we reach a particular point.

But one of the disadvantages of NORECOVERY is that it doesn’t give us a readable database until we restore with RECOVERY and at that point we can’t restore further log backups to our database so if we have missed anything we’d need to start the whole restore process from the beginning.

Read on for an alternative restore mode which fits the bill.

Comments closed

Clearing a Data File with EMPTYFILE

Published 2021-12-13 by Kevin Feasel

Chad Callihan gets rid of secondary data files:

As I was working on a recent tempdb blog post, I encountered an error when trying to remove data files. Let’s look into the issue you may have removing data files and the solution to get those files cleaned up.

Click through to see how you can empty a data file and remove it without receiving error messages. I’m going to guess that this works better on lightly-used databases more than slammed ones.

Comments closed

Timeouts in Power Query Functions

Published 2021-12-13 by Kevin Feasel

Chris Webb reminds us to look at timeouts in Power Query functions:

In the first post in this series I showed how the Power BI Service applies a limit on the total amount of time it takes to refresh a dataset in the Power BI Service, except when you initiate your refresh via an XMLA Endpoint. In this post I’ll look at the various timeouts that can be configured in Power Query functions that are used to access data.

Every time a Power BI Import mode dataset connects to a data source it goes through a Power Query query, and inside the code of that Power Query query will be an M function that connects to a specific type of data source. Most – but not all – of these M functions have the option to set timeouts. 

Read on to learn more about these timeouts, as well as other Power Query functions which have timeouts by default.

Comments closed

Alerting on Blocking in SQL Server

Published 2021-12-13 by Kevin Feasel

Ajay Dwivedi sets up an alert:

Recently one of my LinkedIn friends contacted me for a blocking alert that would work on on-prem & cloud SQL Server instances alike. Previously I wrote https://ajaydwivedi.com/2018/08/how-to-setup-blocking-alerts-on-sql-server blog post for on-prem SQL Server blocking alert which makes use of WMI based event.

So I wrote the following blocking alert setup code for SQL Server that has the below features –

– Send mail notification to one or more recepients unlike SQL Agent job which is restricted to only one operator.

– Parameter to control the consistent blocking threshold. Nobody wants spontaneous blockings which comes/goes.

– Parameter to control the mail notification delay. Say, I want to be notified every X minutes (defult 15 minutes).

– Parameter to control the mail notification subject.

– Separate mail notification for Blocking & Script failure itself.

– Auto-clearing feature. Means, if the blocking is resolved, we should get an automated mail notification saying Blocking is cleared.

Click through for the instructions.

Comments closed

A Data Governance by any other Name

Published 2021-12-13 by Kevin Feasel

Matthew Roche wants a re-naming:

To successfully implement managed self-service business intelligence at any non-trivial scale, you need data governance. To build and nurture a successful data culture, data governance is an essential part of the success.

Despite this fact, and despite the obvious value that data governance can provide, data governance has a bad reputation. Many people – likely including the leaders you need to be your ally if you’re working to build a data culture in your organization – have had negative experiences with data governance in the past, and now react negatively when the topic of data governance is raised.

They now treat data governance as a four-letter word.

Read the whole thing, though I do disagree with Matthew. Changing the name does not change the underlying problems; all it does is make the new name just as hated as the old one because the problems are still there. Call it Data Enablement if you’d like, but if the process is the same and the tools are the same, the outcome is the same, regardless of the name.

Comments closed

An Introduction to BugLab

Published 2021-12-10 by Kevin Feasel

Miltos Allamanis and Marc Brockschmidt take us through a new paper:

Finding and fixing bugs in code is a time-consuming, and often frustrating, part of everyday work for software developers. Can deep learning address this problem and help developers deliver better software, faster? In a new paper, Self-Supervised Bug Detection and Repair, presented at the 2021 Conference on Neural Information Processing Systems (NeurIPS 2021), we show a promising deep learning model, which we call BugLab. BugLab can be taught to detect and fix bugs, without using labelled data, through a “hide and seek” game.

I think there’s a lot more research required before we get to the point where this is useful in practical circumstances, but it’s exciting to see.

Comments closed

Pattern Matching in Scala

Published 2021-12-10 by Kevin Feasel

Kuldeepak Gupta shows off pattern matching in Scala:

Pattern Matching is a mechanism of checking a value against a Pattern. It gives a way of checking the given sequence of tokens for the presence of a specific pattern. Here, we match expressions against a pattern.

Compared to the ‘switch’ in C++, C, JAVA, there’s no fall through to the next alternative in Scala pattern matching. A Match error is thrown when no pattern matches.

This is a powerful part of functional programming.

Comments closed

Understanding the Oldest Page Wait

Published 2021-12-10 by Kevin Feasel

Tom Collins explains a database wait:

SQL Server Log truncation deletes inactive Virtual Log Files (VLF) from the SQL Server database transaction log . The Log truncation process frees  space in the logical log for reuse by the Physical transaction log. If no truncation occurs , eventually it will fill all the disk space allocated to physical log files.

SQL Server Log truncation can be delayed for a range of different reasons.A good starting point is to  query the sys.databases log_reuse_wait and log_reuse_wait_desc columns. This will supply different waits describing the reason for a delay 

Read on for more info about the OLDEST_PAGE wait.

Comments closed

Using GREATEST and LEAST in Azure SQL DB

Published 2021-12-10 by Kevin Feasel

Aaron Bertrand preps us for SQL Server 2022:

In an earlier tip, “Find MAX value from multiple columns in a SQL Server table,” Sergey Gigoyan showed us how to simulate GREATEST() and LEAST() functions, which are available in multiple database platforms but were – at least at the time – missing from Transact-SQL. These functions are now available in Azure SQL Database and Azure SQL Managed Instance, and will be coming in SQL Server 2022, so I thought it was a good time to revisit Sergey’s methods and compare.

Read on to see how the workaround compares.

Comments closed