Month: January 2021

SQL Database Backup Error – File manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Backups are one of the critical and primary functions as a DBA. Recently I noticed a failed backup job that runs apart from our enterprise backups. We use these individual backups to refresh non-prod environments for developers to run their queries. I have had these jobs fail with the following error:

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

The job fails as soon as I initiated a re-run. So naturally I went to check what processes are running currently. To do this, I run sp_WhoIsActive. I noticed that a scheduled job from enterprise backup system has been running but ran a little longer than usual. This seems to be causing the error when I tried to run another backup through the SQL Agent job on the same database.

The resolution is that i just waited for the scheduled backup job to complete and re-ran the SQL Agent job and it completed successfully.

Another possible reason for this error is that if we perform a shrink operation in parallel to backup operation. In general, shrink is NOT recommended unless you know that you have deleted a large set of data releasing space that you would like to reclaim.

Parameter Sniffing in SQL Server

If you notice that one of your usual query sometimes runs fine and the same query sometimes runs slow. It may be due to a common phenomenon called parameter sniffing.

A query becomes a victim of Parameter sniffing when SQL server caches a plan for a particular parameter. The same query when run again with a different parameter runs using the cached plan instead of getting a brand new plan. In general, this is by design and is a good thing since every recompile burns more CPU cycles.

Folks usually try the following to deal with the above scenario

  1. Restart windows
  2. Restart the SQL Server Service
  3. Fail over the cluster
  4. DBCC FREEPROCCACHE
  5. Rebuild indexes, which really is just same as clearing out the cache
  6. Update your statistics, which really is just same as above
  7. DBCC FREEPROCCACHE’s for specific plans

The right way to deal with parameter sniffing emergency is:

  1. Find the ONE bad plan in cache
  2. Save the ONE bad plan to disk for troubleshooting later
  3. Free the ONE bad plan from memory

You could run the open source stored proc sp_BlitzCache to find the bad plan. This store proc lists the top 10 worst queries in the plan cache. You review each of them and pin point the query plan where the estimates versus actuals are off by more than 10x.

Can we use monitoring software to catch parameter sniffing?

Typically when we hear from developers or end users that a query is running long. We hop onto the terminal of monitoring tool and look for PLE (page life expectancy). However, the long running queries may not the root cause for flushing out the cache. It is sometimes the quick running queries with large memory grants that drop the PLE down abruptly.

When are we susceptible to parameter sniffing?

Anytime you have one piece of code that needs to handle a varying number of rows, we are probably gonna have to worry about parameter sniffing.

Changing the information for a database mail account

It happens so that sometimes your mail server gets moved from one instance to another, one platform to another. Recently we have had to move our in house exchange mail server to Office365.

Here is a script to update the mail box and account details on an existing database mail account:

EXECUTE msdb.dbo.sysmail_update_account_sp  
     @account_name = 'SMTP_database_mail'  
    ,@description = 'Mail account for administrative e-mail.'  
    ,@email_address = 'dba@Adventure-Works.com'  
    ,@display_name = 'Database Mail'  
    ,@replyto_address = 'SQLAlerts@Adventure-Works.com'  
    ,@mailserver_name = 'smtp.office365.com'  
    ,@mailserver_type = 'SMTP'  
    ,@port = 587  
    ,@timeout = 60  
    ,@username = 'SQL-Alerts@Adventure-Works.com'  
    ,@password = 'AStrongPasswordGoesHere'  
    ,@use_default_credentials = 0  
    ,@enable_ssl = 1;

Now lets verify what we configured above by sending a test email.

use master
declare @servername varchar(100)
declare @testmsg varchar(100)
set @servername = replace(@@servername,'\','_')
set @testmsg = 'Test from ' + @servername

--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'DBA_Profile',
@recipients = 'SQL-Alerts@Adventure-Works.com',
@subject = @testmsg,
@body = @testmsg

Copy members from one AD Group to another, get members from an AD group

Powershell is powerful and versatile to perform numerous operations. It has been recently useful for me when i needed to migrate users from one AD group to another. Below is a one line statement to do just that

Add-ADGroupMember -Identity 'New Group' -Members (Get-ADGroupMember -Identity 'Old Group' -Recursive)

The -Recursive switch is used in case the old group contains nested groups.

What if you need to get all the users contained in an AD group?

There is a one line powershell statement for that too.

Get-ADGroupMember -Identity 'SQLADGroup' -Recursive | select name | Sort-Object -Property name

Sort-Object will take the previous output and sorts the name column in ascending order.

%d bloggers like this: