Category: SQL

DBA Utilities, SQL

How to read and write backup directory in SQL Server
There is a requirement for me to change the backup directory of a SQL Server instance. Here are few tips related to the sobject and hope it will be useful as a future reference for all of us.

How to read the value of BackupDirectory? 

DECLARE @path NVARCHAR(4000)

EXEC master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            @path OUTPUT, 
            'no_output'

How to write/set a new value of BackupDirectory? 

EXEC master.dbo.xp_instance_regwrite
            N'HKEY_LOCAL_MACHINE',
            N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
            REG_SZ,
            N'\\xx.xxx.xx.xxx\C$\data\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup';

Few other important information: 

DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)

--Installation Root Info
EXEC master.dbo.xp_instance_regread 
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\Setup',
	N'SQLDataRoot',
@SQLDataRoot OUTPUT
Select @SQLDataRoot

-- SQL Data file Info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
@DefaultData OUTPUT
Select @DefaultData

-- SQL Default Default Log file info
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
@DefaultLog OUTPUT
Select @DefaultLog
xp_instance_regread & xp_instance_regwrite are undocumented features, so it may be deprecated any time, however, can be used for non-production or DBA specific tasks at own discretion.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Performance, SQL

WITH RETURNS NULL ON NULL INPUT in SQL Server

While I was going through a review of a sql server function, it is observed there are many checks implemented if any of input parameter is null then return null. Then, it got strike on mind about “WITH RETURNS NULL ON NULL INPUT” in SQL Server. This is available in SQL server from 2005 version, however, I have not seen people used it efficiently. So thought of sharing about this intelligent way of handling such situations.

Let us start with a sample function as below:

Mighty Sample function

function using WITH RETURNS NULL ON NULL INPUT

The above is a sample function that we can see in many places to check the null param check. There is an option introduced in SQL Server 2005 “WITH RETURNS NULL ON NULL INPUT” to handle this with a grace as below. This way, if any of your input parameters is passed as NULL value, then the function will immediately return NULL value without actually invoking the body of the function. That clears the theory and its time for us to check with sample code as below.

Sample output

The below screenshot explains that the option returns without getting into execution of the function whenever one of the parameter is having NULL value. (Please note I used “Wrong value” to assign to the output variable to understand it actually returns in the beginning or only after the execution.)

If we have not used this technique so far, start using this one, it helps to avoid lots of junk codes in the definition that makes it clean. In addition, it has also observed a small factor of performance improvement for the function.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SQL

Palindrome in SQL Server

What is Palindrome?

From Books OnLine, “A palindrome is a word, number, phrase, or other sequence of characters which reads the same backward as forward, such as madam, racecar. There are also numeric palindromes, including date/time stamps using short digits 11/11/11 11:11 and long digits 02/02/2020. Sentence-length palindromes ignore capitalization, punctuation, and word boundaries.”

Code implementation

The below function eliminates characters other than alphanumeric characters. The function is modified to accommodate alphabets to the old function written to extract only numbers. 

create or alter function dbo.fn_extractalphanumeric (@ValueString nvarchar(max))
returns nvarchar(max)
as
Begin

Declare @retvaluestring nvarchar(max)

;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4)
Select @retvaluestring =(
SELECT  (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9a-z]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH( '' )) AS stringout
FROM (Select * from (values(@ValueString)) A(string))A)
return @retValuestring
End

Test Script

DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('aA1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),
('    Eddie     '),('Noon'),('My Gym'),('malayalam'),('11:11'),('11/11/1111'),('racecar')

Select string, dbo.fn_extractalphanumeric(string),
	case when reverse(dbo.fn_extractalphanumeric(string)) = dbo.fn_extractalphanumeric(string) 
			then 'Palindrome' else 'Not Palindrome' End stringtype
From @Sample

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SQL

Fibonacci series in SQL Server
Today, I noticed that my elder kid was learning about fibonacci series. While observing her, I was just trying to write SQL script to produce the series just for fun and sharing it with you all.

What is fibonacci series?

A series of numbers in which each number ( Fibonacci number ) is the sum of the two preceding numbers. The simplest is the series 1, 1, 2, 3, 5, 8, etc. Fibonacci was not the first to know about the sequence, it was known in India hundreds of years before! Leonardo Pisano Bogollo is known as fibonacci man, and he lived between 1170 and 1250 in Italy. “Fibonacci” was his nickname, which roughly means “Son of Bonacci”.

Fibonacci Day is November 23rd, as it has the digits “1, 1, 2, 3” (11 represents month number-23 represents day part) which is part of the sequence. So next Nov 23 let everyone know about this fact.(Honestly, I never noticed this until now.)

Formula & Code Implementation

The simple formula is Fn = Fn-1 + Fn-2

The code implementation as below :

I used Common Table Expression (CTE) to generate it easily over while loop. If you guys are comfortable with while loop, you can even generate with loop.

Script: 
Declare @UntilNo integer = 100
;With Fibonacci (N, NextN) AS
(
 SELECT 0, 1
    UNION ALL
    SELECT NextN, NextN+ N
    FROM Fibonacci
    WHERE N < @UntilNo
)
SELECT Substring(
    (SELECT cast(', ' as varchar(max)) + cast(N as varchar(max)
)
FROM Fibonacci
FOR XML PATH('')),3,10000000) AS list

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

The transaction log for database ‘database name’ is full due to ‘REPLICATION’
Today, we are going to see an exception from SQL Server “The transaction log for database ‘dbname’ is full due to ‘REPLICATION'”.

Recently, we received a backup of a database for a troubleshooting purpose from the production. The database had configured with CDC in Production environment. While we get this database and restored in our local environments, it is observed that the size of the database is very huge and if you look at the size in details, its log that is majorly contributing the size.

Since its huge in size, we tried to shrink the file, please note this is a non-production environment, shrinkfile is not advised to run without a careful consideration and validation. Few references on shrink file written earlier. Since, in our case it was a testing environment, we were free to use this command to reclaim the space.

Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

It is also observed that log_reuse_wait_desc was showing “REPLICATION” for the database. 

Select log_reuse_wait_desc,* From sys.databases
As we know this db was enabled with CDC in Production environment, the first attempt was to disable CDC on restored database. 

use Restoredbname
sys.sp_cdc_disable_db

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 2] Could not update the metadata that indicates database Restoredbname is not enabled for Change Data Capture. The failure occurred when executing the command ‘(null)’. The error returned was 9002: ‘The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.’. Use the action and error to determine the cause of the failure and resubmit the request.

We also noticed checkpoint was also not successful due to serious disk issue. 
checkpoint

Could not write a checkpoint record in database Restoredbname because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files. Msg 5901, Level 16, State 1, Line 10 One or more recovery units belonging to database ‘Restoredbname’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

Finally, we decided to apply sp_repldone on the database as below. When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log. sp_removedbreplication stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Once we executed the commands, we were able to shrink the file and the size has been reclaimed to os. 

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
sp_removedbreplication

The solution discussed is NOT a general solution nor for Production environments. This should ONLY be used for non-production or lower environments where the restored database is used for testing purpose and truncating log is not a concern!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!