Truncate a large table in Availability Group

Recently, I had to truncate 3 TB table from the availability group. Since truncate deallocates extents, it does not generate as much log compared to delete (logs individual records). However, I wanted to research this as we have synchronous AG and did not want to flood the secondary replicas with too much log.

I came across an article from Paul Randal about this exact issue. Thanks Paul. Link to Paul’s article here https://www.sqlskills.com/blogs/paul/the-curious-case-of-log-generated-during-a-drop-table/

To confirm this, I executed the truncate command on test server ( it does not have AG) and tracked the log backup size. Since, there was no database activity at that time, all the growth should be related to this event. The math worked out very well and it was exactly in the range as mentioned in Paul’s blog. I also read the log backup contents to verify.

So, we executed this task on prod during non-peak hours and secondary replicas were able to catch up without any hiccups.

This article is for future reference. Thanks Paul for the wonderful article. Timing could not have been more right and the math was exact.

Master database in single user mode after restore

Recently I had to restore master database on a SQL instance. One way to restore master database is

  1. Restore the master database as user database on same version of SQL.
  2. Detach the database from the instance.
  3. Copy the files to original instance.
  4. Restart the original instance.

Make sure the logical and physical file names are same as original when the database was restored in step 1.

Anyways, after I did the above steps, I noticed the master database was coming up in “single user” mode on the original instance ( Step 4).

I, eventually, realized that it was due to how I was detaching the database. So, when I was detaching the database, I selected the option to “drop connections” in the UI and SQL accomplishes this by putting the DB in single user mode before it detaches.

That’s the reason why it was coming up in single user mode on the original instance when the files were copied over.

Custom SQL Job Failure Notifications

Job failure notifications is a common way to send notification when a SQL job fails. Setting up job notifications is fairly straight forward. However, sometimes there may be a need to do custom logic on when these notifications need to be sent. Since the job history is logged in the msdb database, we can query the tables and build out of the box solutions. One solution I had to do recently was to send notification email only if the job failed 5 successive times.

In order to do this, whenever the job fails, it should execute a step( part of the job) to check the last 4 run’s job final output (step id = 0) and if they all failed, send an email notification.

Below code shows how this can be done.

Declare @JobID uniqueidentifier
SET @jobID = CONVERT(uniqueidentifier,$(ESCAPE_SQUOTE(JOBID)))

IF ( SELECT MAX(run_status)
FROM (SELECT A.run_status
FROM Msdb.dbo.sysjobhistory A 
INNER JOIN Msdb.dbo.sysjobs B ON A. Job_id=b.job_id
WHERE step_id=0 AND B.Job_ID = @jobID
ORDER BY A.instance_id DESC OFFSET 0 ROWS  FETCH First 4 ROWS ONLY) A)= 0

BEGIN
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = '<<SQLMailProfile>>', 
    @recipients = 'youremail@email.com', 
    @body = '<<email body>>', 
    @subject = '<<email subject>>' ; 
END

Registering SPN for SQL Server Service Accounts

When setting up a new SQL Server, one of the important step is to register the SPN of the service account.This registration is not required if the service account is domain administrator or if you give explicit permissions to self register the SPN for the service account. Both options are not wise, so anytime a new SQL Server is set up or service account is changed – we have to manually register the SPN on the domain. This task needs to be done by someone who has domain admin rights.

Registering SPN’s enables kerberos authentication for delegation and for double hop scenarios such as linked server, you can impersonate the actual user other wise you have to specify SQL Account and this can become security loophole in your system.

Below are the steps to enable kerberos delegation:

1. Register SPN for serviceaccount with all possible combinations
SetSPN -A MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA:1433 domain\serviceaccount

In case of a clustered instance, specify the Virtual SQL Cluster Name(without the instance name). You have to mention the TCP port the SQL is running on.

2. Enable the service account to trust for delegation. This is a setting in the AD.You can choose either to trust for all delegation or if you want, you can specify which service to delegate.

3. Make sure TCP/IP protocol is enabled and named pipes is disabled.

If you have any other SQL components such as Analysis service or Reporting service, you can register them as well to use Kerberos.

Example of SSRS SPN registration:
http/computername.domainname.com:80 domainname\serviceaccount
http/computername.domainname.com domainname\serviceaccount
http/computername domainname\serviceaccount
http/computername:80 domainname\serviceaccount

If you use performance dashboard reports, you need to have kerberos authentication for SSRS. These dashboards are very useful and you can download from github.

Also, If you want to list all the SPN’s registered for a service account, you can use
SetSPN -L domainname\serviceaccount

If you want to delete a spn, you can use
SetSPN -D MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA:1433 domain\serviceaccount

Query to search for a particular value in database

The following query should help search for a particular value in the database. This is particularly useful, when you have troubleshoot an issue and you do not any clue where the data might be and do not have to access to launch trace session. This script helped me few times when I inherited a database( with no documentation\guidance) and had to reverse engineer the process with very limited choices.

In the below example, we are searching for value “Toyota”.

CREATE TABLE #output ( schema1 VARCHAR(500),tablename VARCHAR(500),column1 VARCHAR(500),Columnvalue VARCHAR(2000))

SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA,
'Select top 1 '''+TABLE_SCHEMA+''','''+Table_Name+''','''+Column_Name+''','+quotename(COLUMN_NAME)+' as [ColumnValue] from '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+ '(nolock) where '+quotename(COLUMN_NAME)+' like ''%Toyota%''' AS SQL1
INTO #Test
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Data_Type IN ('char','varchar','text','nchar','nvarchar','ntext')

DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @TABLE_SCHEMA VARCHAR(500)
DECLARE @SQL1 VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA,SQL1
FROM #test

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME,@TABLE_SCHEMA,@SQL1

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @SQL1
INSERT INTO #output
EXEC (@SQL1)

FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME,@TABLE_SCHEMA,@SQL1
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #output

Issues when migrating stored procedures to native compiled

Recently, I started working on a project to migrate all disk based tables to InMemory and regular stored procedures to native compiled stored procedures.

While migrating stored procedures, I encountered several issues, that needed code changes. The native compilation advisor will alert on the issues that needs to be addressed before making the stored procedure native compiled.

This is not complete list but some of the issues I came across in my project

1. Cannot use Select * .
2. Cannot refer user defined functions.
3. Cannot refer tables without schema name.
4. Cannot use CASE expression, Choose, IIF.
5. Cannot use functions such ‘%’,Like.
6. Cannot have debug statements like Print.
7. Cannot have Create\Drop\Truncate Table.
8. Cannot use table variables.(use inmemory table variable)
9. Cannot refer objects in other databases.
10. Cannot use Linked Server Queries.
11. Cannot have explicit Begin Transaction\Commit\Rollback.
12. Lock Hints are not allowed.

I will add more exceptions here as I come across them.

Pending Replication Transactions in the Transaction Log

The below query gets the count of pending replication transactions from the transaction log. The log reader agent sends these txns to the distribution database and high no.of pending txns in the log is not a good sign of replication performance as well as affects database performance due to log growth. The log reader agent is not present in snapshot replication.

The VLFs in the log are marked as active and cannot be reused until these txns are sent to the distribution database. This can make the log file to grow and since log file is not instant file initialized, it affects the performance.

Select instance_name as DBName,cntr_Value as [PendingTransInTransactionLog]
from sys.dm_os_performance_counters A inner join Sys.databases B on A.instance_name=B.name and B.is_published=1 where LTRIM(counter_name) like '%Repl. Pending Xacts%'

SQL 2016 SP1

The recently released service pack(SP1) for SQL 2016 includes very big enhancements. The biggest of all is providing premium features such as inmemory, columnstore to standard edition. Of course, there are some restrictions but it is definitely good news for people using standard edition or looking to upgrade.

One interesting enhancement is support to “Create or Alter” syntax while creating objects such as functions\procedures\triggers.

Sample Syntax :

Create or Alter procedure usp_test
as
begin
select 1 as id
end

XACT_ABORT – Why it should be set to ON

Certain errors cannot rollback a transaction. That’s why XACT_ABORT ON should be included in the code to prevent unhandled scenarios erroring out without rolling back the transaction and leaving an open transaction. In the example below, in the first scenario – you can see the session 1 query errored out leaving an open transaction and it blocks session 2 query( under read committed isolation). In the second scenario with XACT_ABORT ON, it will rollback the transaction.

--Set up Test Table
Create Table Test(id int,Name varchar(20))

--Scenario 1 with XACT_ABORT OFF
--Session 1
Begin
Set XACT_ABORT OFF
Begin Try
Begin Tran
Insert into test values(1,'test')
Select * from Idonotexist
If (@@ERROR=0)
Commit
End Try
Begin Catch
If (@@TranCount>0)
Rollback
End Catch
End

--Session 2
select * from test

--Scenario 2 with XACT_ABORT ON
--Session 1
Begin
Set XACT_ABORT ON
Begin Try
Begin Tran
Insert into test values(1,'test')
Select * from Idonotexist
If (@@ERROR=0)
Commit
End Try
Begin Catch
If (@@TranCount>0)
Rollback
End Catch
End

--Session 2
select * from test

--Clean Up
Drop Table Test