Press "Enter" to skip to content

Category: Administration

Ring Buffer CPU Over 100%

Thomas Rushton ran into an oddity with sys.dm_os_ring_buffers:

Wait! Process Utilization + Idle adds up to waaaaay over 100…What’s going on?

My first thought was that the CPU was being throttled, so I checked the windows power options – for some really irritating reason, Windows defaults to a “balanced” power setting, which is rubbish for SQL Server.

That looks OK, but… if you check the CPU page of the Task Manager, things are a bit more interesting:

Unfortunately, the story doesn’t have a conclusion, but Thomas’s conjecture does make sense.

Comments closed

Azure Kubernetes Service Max Volume Count

Chris Taylor explains an error message in Azure Kubernetes Service:

Whilst playing around with my session for Techorama.nl I encountered an error I hadn’t seen previously whilst deploying SQL Server on Linux in Azure Kubernetes Service (AKS)

0/1 nodes are available: 1 node(s) exceed max volume count

The yaml I used was only slightly modified (mainly names) from scripts used on minikube and docker-desktop so I was a little confused as to why I was getting this in AKS.

Read on to understand what’s happening here and how you can fix it.

Comments closed

Read-Only versus Read-Write and SQL Server

Jack Vamvas takes us through what it takes to turn a read-write database in SQL Server read-only and vice versa:

There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database

1) Requires ALTER permission on the database. This is an elevated privilege – and may break the organisations sql server security policy

2) Is the developer on the hook for maintaining Production data? What is the developer’s role in supporting production data ?

Read on for the commands as well as additional considerations before you grant these permissions.

Comments closed

Version Store and ONLINE Operations

Josh Darnell takes us through how SQL Server manages ONLINE = ON operations (such as index building and rebuilding) using the version store:

The votes table has about 10 million rows in it, so this takes a bit of time (10-15 seconds if nothing else is happening). If I check sys.dm_tran_version_store_space_usage and sp_WhoIsActive, I can see that:

– the version store is not growing, and
– the ALTER statement is chugging along making progress

There are costs to setting ONLINE = ON. I think they’re almost always worth it, but it’s important to remember that they are there.

Comments closed

Starting SQL Server in Single-User Mode

Ranga Babu has a few methods for starting SQL Server in single-user mode:

It is advisable to use SQLCMD when you want to query SQL Server that is started in single user mode as connecting directly and query using SQL Server Management Studio that uses more than one connection. To query SQL Server single user mode using SQL Server Management Studio, open SQL Server Management Studio, and do not connect to SQL Server directly. Close the connection window and click on New Query as shown in the below image which opens a query editor in SQL Server Management Studio:

I recommend practicing this a few times, as the only time you’d actually start SQL Server in single-user mode is during an emergency and that means people breathing down your neck (figuratively if not literally).

Comments closed

Reading SQL Server Logs from Kubernetes

Anthony Nocentino shows us how we can use kubectl logs to read the SQL Server Error Log:

We can use follow flag and that will continuously write the error log to your console, similar to using tail with the -f option. If you remove the follow flag it will write the current log to your console. This can be useful in debugging failed startups or in the case below, monitoring the status of a database restore. When finished you can use CTRL+C to break out and return back to your prompt.

Read on for a brief demonstration.

Comments closed

Hot Patching Azure SQL Database

Hans Olav Norheim has an interesting paper on a technique Microsoft uses to release SQL Server patches for Azure SQL Database while minimizing downtime:

The SQL Engine we are running in Azure SQL Database is the very latest version of the same engine customers run on their own servers, except we manage and update it. To update SQL Server or the underlying infrastructure (i.e. Service Fabric or the operating system), we must stop the SQL Server process. If that process hosts the primary database replica, we move the replica to another machine (requiring a failover).
 
During failover, the database may be offline for a second and still meet our 99.995% SLA. However,  failover of the primary replica impacts workload because it aborts in-flight queries and transactions. We built features such as resumable index (re)build and accelerated database recovery to address these situations, but not all running operations are automatically resumable. It may be expensive to restart complex queries or transactions that were aborted due to an upgrade. So even though failovers are quick, we want to avoid them.

Read on to see how they do it. There’s no on-prem analogue yet, though perhaps that will come in time.

Comments closed

Dealing with Thousands of Databases

Andy Levy wraps up a Q&A series on dealing with thousands of databases:

When you started, did you know what your position was going to look like 1 month, 6 months, 1 year, 5 years from then? How accurate has that been so far?

I’ve only been at my current job for about 2 1/2 years, but I can speak to the shorter intervals. I’m going to be intentionally vague in spots here as I don’t want to disclose too much.

And if you’d like to hear Andy talk about migrating 8000 databases, Carlos Chacon and I interviewed Andy for the SQL Data Partners podcast.

Comments closed

Determining Instant File Initialization Status

Dave Mason gives us a couple of methods for determining whether we turned Instant File Initialization on:

Here’s a little tidbit I wanted to share regarding the Perform Volume Maintenance Tasks security setting. In the SQL Server world, this is often referred to as IFI. On more recent versions of SQL (SQL 2012 SP4 or later, I believe), you can verify if IFI is enabled or not for the database engine logon account by checking the error log.

That’s one, but click through for the technique you can easily script out.

Comments closed

Intellisense and the DAC

Slava Murygin doesn’t like severity 20 errors just popping up for no good reason:

Yesterday I’ve needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an “Severity 20” alert.

As you probably know, Severity 20 Errors “Indicate system problems and are fatal errors” (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though “Severity 20” does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

Read on to see the cause of Slava’s problem and how there’s no way to fix it in SSMS.

Comments closed