Press "Enter" to skip to content

Category: Administration

Last Known Good DBCC CHECKDB In Powershell

Published 2017-04-05 by Kevin Feasel

Rob Sewell shows off a cmdlet to check DBCC DBINFO for each database to get the last known good CHECKDB run:

This time we get more information. The server name, database name, when the database was created, the last good DBCC Checkdb, how long since the database was created, how long since the last known good DBCC Checkdb, a status and a Data Purity enabled flag. If you look at the image above it shows that the DBA_Admin database has a status of “New database, not checked yet” even though it has a date for the last known good DBCC CheckDb. This is because it was restored after this server was upgrade from CTP 1.3 to CTP 1.4 and there has not yet been a DBCC CheckDb run yet. The system databases have a status of “CheckDb should be performed”. This is because the last known good DBCC CheckDb is more than 7 days ago. Lets run a DBCC CheckDb and check again

Do read the caveats, and also check out a previous Arun Sirpal blog post on DBCC DBINFO.

Comments closed

Troubleshooting Cluster Creation Errors

Published 2017-04-05 by Kevin Feasel

Mark Broadbent diagnoses an error which seems misleading at first:

One such problem is when you use the New-Cluster command to add all your nodes in one go.

New-Cluster -Name magrathea -node server5,server6,server7 
-staticaddress 192.168.1.70

Simple right? Well no. In this instance I ran into the following error:

New-Cluster : There was an error adding node 'server7' to the cluster

the node cannot be contacted. Ensure that the node is powered on and is connected to the network.

Read on for an example of piecemeal debugging.  Mark’s advice is to keep things simple, as in this case at least, you can’t count on the error messages coming back to be completely accurate.

Comments closed

Operating System Error 3

Published 2017-03-31 by Kevin Feasel

Stacy Brown provides common reasons for why you might get Operating System Error 3:

Sometimes the users of SQL Backup Master may face the following error while executing the database backup job:

Msg 3201, Level 16, State 1, Line 1
Job Execution Error: Cannot open backup device ‘’ Operating System error 3 (The system cannot find the path specified.)

Now, there can be the various possible reasons behind the occurrence of this error. Therefore, in the following sections, all possible reason with their respective solutions are discussed. A user can refer them to solve this SQL Server operating system error 3(the system cannot find the path specified.)

Click through for solutions to several potential causes of this error.

Comments closed

Cleaning Up LOB

Published 2017-03-30 by Kevin Feasel

Raul Gonzalez reminds us that rebuilding a clustered index onto a new filegroup doesn’t move LOB data:

In previous posts I have explained how having dedicated filegroups for user data can improve our RTO by, in case of disaster, recovering critical data first and then the rest.

The thing is when you deal with databases which were not created this way, you need to move the data from one filegroup to another before you can apply this kind of techniques.

Here is where this post can show you one of the gotchas you can find during this process.

Read on for a demo of this.

Comments closed

SQL Client Aliases

Published 2017-03-29 by Kevin Feasel

Andrew Pruski explains how to use a lesser-known feature in SQL Server, client aliases:

One of the problems that we ran into when moving to using containers was how to get the applications to connect. Let me explain the situation.

The applications in our production environment use DNS CNAME aliases that reference the production SQL instance’s IP address. In our old QA environment, the applications and SQL instance lived on the same virtual server so the DNS aliases were overwritten by host file entries that would point to 127.0.0.1.

This caused us a problem when moving to containers as the containers were on a separate server listening on a custom tcp port. Port numbers cannot be specified in DNS aliases or host file entries and we couldn’t update the application string (one of the pre-requisites of the project) so we were pretty stuck until we realised that we could use SQL client aliases.

This is definitely a place that you’d want to document changes thoroughly, as my experience is that relatively few DBAs would even think of looking there.

Comments closed

Database File Sizes In Powershell

Published 2017-03-29 by Kevin Feasel

Rob Sewell has a nice post on checking database file sizes using dbatools in Powershell:

As always, PowerShell uses the permissions of the account running the sessions to connect to the SQL Server unless you provide a separate credential for SQL Authentication. If you need to connect with a different windows account you will need to hold Shift down and right click on the PowerShell icon and click run as a different user.

Lets get the information for a single database. The command has dynamic parameters which populate the database names to save you time and keystrokes

It’s a great post, save for the donut chart…  Anyhow, this is recommended reading.

Comments closed

Cloned Database Security

Published 2017-03-28 by Kevin Feasel

Parikshit Savjani explains what happens when you run DBCC CLONEDATABASE on databases with various security measures activated:

Transparent Data Encryption (TDE)

If you use TDE to encrypt data at rest on the source database, DBCC CLONEDATABASE supports cloning of the source database but the cloned database is not encrypted by TDE. Thus, the backup of the cloned database will be unencrypted. If it is desired to encrypt and protect cloned database backup, you can enable TDE on cloned database before it is backed up as shown below

It’s a good read which covers several technologies.

Comments closed

CHECKDB For Read-Only Databases?

Published 2017-03-24 by Kevin Feasel

Erik Darling answers a reader’s question:

So, can you run DBCC CHECKDB on a read only database? Should you run DBCC CHECKDB on a read only database?

tl;dr: YES AND YES!

Here’s why:
Many forms of corruption that I’ve seen have come from storage. Sure, there have been bugs that were to blame, but yeah. Most of the time, it’s the storage going all yucky.

Erik also explains some gotchas, so read the whole thing.

Comments closed

Azure Managed Disks

Published 2017-03-23 by Kevin Feasel

Dave Bermingham explains what Azure Managed Disks are and why you might want to use them:

What’s Managed Disks you ask? Well, just on February 8th Corey Sanders announced the GA of Managed Disks. You can read all about Managed Disks here. https://azure.microsoft.com/en-us/services/managed-disks/

The reason why Managed Disks would have helped in this outage is that by leveraging an Availability Set combined with Managed Disks you ensure that each of the instances in your Availability Set are connected to a different “Storage scale unit”. So in this particular case, only one of your cluster nodes would have failed, leaving the remaining nodes to take over the workload.

Prior to Managed Disks being available (anything deployed before 2/8/2016), there was no way to ensure that the storage attached to your servers resided on different Storage scale units. Sure, you could use different storage accounts for each instances, but in reality that did not guarantee that those Storage Accounts provisioned storage on different Storage scale units.

Read on for more details.

Comments closed

SQL On Linux Backups

Published 2017-03-23 by Kevin Feasel

Rob Sewell shows how to use Ola Hallengren’s solution to back up SQL Server databases on Linux using the SQL Agent:

Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell

First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs

It’s not “point, click, done,” but Rob shows you certainly can do it.

Comments closed