Category: SQL

DBA Utilities, SQL

How to re-queue an email in SQL that has been successfully sent at first time?

Let’s say you send an email from inside a SQL instance using something like the following:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLZEALOTS_Manager',  
    @recipients = 'Lkiran@xxx.com',  
    @body = 'This email Sent',  
    @subject = 'SqLZeaLoTs' ;

And recipient DOES successfully receive the email.

Now for one reason or another, they want the original email sent again (not an execution of msdb.dbo.sp_send_dbmail, but instead to basically re queue the original email to be sent again).

Step 1: Execute the following:

SELECT mailItem_id
       FROM [msdb].[dbo].[sysmail_mailitems]
       WHERE subject = 'SqLZeaLoTs'
       ORDER BY sent_date desc

Copy the mailItem_id value for the specific email you want to requeue, in this example 2738274.

Use msdb
GO
DECLARE @rc INT
,@sendmailxml VARCHAR(max)
,@mailID INT = 2738274 -- This is from the mailItem_ID column in [msdb].[dbo].[sysmail_mailitems] for the mail you want to resend. 2738274 is just an example.

SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailID) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = sp_SendMailQueues @sendmailxml
    IF @rc <> 0
    BEGIN
       RAISERROR(14627, 16, 1, @rc, 'send mail')
END

If the query completes successfully, it has queued the original email again and the original recipient will receive another email with exactly the same subject and body.

Step 2 has only been confirmed to work on SQL 2008 R2 SP3.

Note: If you want to try it for later SQL Server versions, you may need to verify the code of step 2 by following the steps.

Script out the system procedure: msdb.dbo.sp_send_dbmail and look for the section near the bottom that executes sp_SendMailQueues. The most important piece to verify is the “SET @sendmailxml…” statement. The reason for this is that sp_SendMailQueues sends the email to a queue that is monitored by a broker that calls DatabaseMail.exe. It is entirely possible that different versions of SQL may have different versions of DatabaseMail.exe that requires the XML to be formatted differently so you want to make sure that the XML is formatted for the version required.

Please try this out on your environment and feedback if you have any comments.

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

DBA Utilities, SCRIPT GALLERY, SQL

How to get FQDN (fully qualified domain name) from SQL Server?
A fully qualified domain name (FQDN) is referred to as an absolute domain name, is a domain name that specifies its exact location in the tree hierarchy of the Domain Name System (DNS).

You can see this information easily throughput Control Panel – > System and Security – > System – > Look out “Computer name, domain and workgroup settings”

But, I was looking for some way to identify this information without switching my windows (actually I was working on something when I was asked this information) . 

Select DEFAULT_DOMAIN()
EXEC master..xp_loginconfig 'Default Domain'

DECLARE @rootkey varchar(100) = 'HKEY_LOCAL_MACHINE',
@key varchar(100) = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\',
@value_name varchar(100) = 'Domain',@Domain_Name sysname -- output parameters
--Using xp_regread
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name
--Using xp_instance_regread
EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name

/* Incase you need to assign to a variable*/
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 
SELECT @Domain_Name 'Domain Name'

Set @Domain_Name = ''

EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 

SELECT @Domain_Name 'Domain Name'
Since, its domain information, I am not providing the screenshot, you may try it out yourself and share your thoughts.

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

DBA Utilities, Powershell Scripts, SCRIPT GALLERY, SQL

How to get Disk space information in SQL Server

Problem Statement:

While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since we did not have access to the server, we have come up with a simple script as below. I am sharing the script here hoping this helps you in similar situation.

Script:

declare @svrName varchar(255)
declare @sql varchar(400)
--User Options(By default Server name)
set @svrName = Cast(SERVERPROPERTY('MachineName') as varchar(255))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') 
	+ ' -Class Win32_Volume -Filter ''DriveType = 3'' '+
	'| select name,capacity,freespace |foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table
CREATE TABLE #Details
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #Details
EXEC xp_cmdshell @sql

--Retrieve the Capacity values in GB from PS Script Details
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #Details
where line like '[A-Z][:]%'
order by drivename

--Drop the temporary table
drop table #Details

See Also:

Using windows PowerShell to get the server disk space

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

SQL, SQL Server 2016

Compress and Decompress in SQL Server 2016
Recently, to enforce a very small level of masking of data, I was thinking of different ways and finally used COMPRESS & DECOMPRESS functions introduced in SQL Server 2016.

Beware, This is a very bad example of implementing as a solution for data encryption! Since, it was just so trivial functionality and I do not really want to spend more time on encryption and do not bother on its performance, also it was not for a full time production application and just for demo purpose, it has been taken as an option.But, again, I would like to reiterate this is a wrong example! Do not apply !

If you are looking for a full fledged solution for encryption/data masking – SQL Server has many options, you can refer the options here.

Now, Lets quickly look into the two functions introduced in SQL Server 2016 – COMPRESS & DECOMPRESS.

COMPRESS function quickly compresses the input using GZIP algorithm. More details here.

DECOMPRESS function otherwise, it decompresses the compressed value using GZIP algorithm. More details here.

Since Microsoft covers a good explanation on the subject, let us not try to re-invent the wheel, instead, let us quickly see some of characteristics and its usages.

Storage and its significance

Storage is important factor, hmm, not really in modern world, but yes since if you want to save cost. This is really matters a lot if you have any plan for cloud migration or something like “you pay for what you use”. So COMPRESS and DECOMPRESS makes more sense in such situations. Let us demonstrate a simple example as below.

I am going to create a table with varchar and nvarchar columns to store values. 

drop table if exists SQLZealot_Compress_Test
CREATE TABLE SQLZealot_Compress_Test
(
ID int identity(1,1) NOT NULL Primary Key,
Varchar_Value VARCHAR(MAX),
NVarchar_Value NVARCHAR(MAX)
)

Now, let us insert some sample data as below. 

INSERT INTO SQLZealot_Compress_Test(Varchar_Value, NVarchar_Value)
VALUES 
 ( 'This is a Compress test by SQLZealot!', N'This is a Compress test by SQLZealot!!')
,( '1234567890', N'1234567890')
,( REPLICATE('X', 9000), REPLICATE('X' , 4500))
,( REPLICATE(CAST('X' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'X' AS NVARCHAR(MAX)), 4500))
,( REPLICATE(CAST('SQLZealot|' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'SQLZealot|' AS NVARCHAR(MAX)), 4500))

Now, let us insert some more big sized data as below. I used a sample table from my database, you can use any of your big table in your environment by replacing “tablename”. 

Insert into SQLZealot_Compress_Test
Select (Select * From tablename FOR JSON PATH, ROOT('Tables')) varcharval,
		(Select * From tablename FOR JSON PATH, ROOT('Tables')) nvarcharval

Here comes our testing script. We are going to see the length of the values using DATALENGTH as below. 

Select ID,
		Varchar_Value,
		DataLength(Varchar_Value) DL_NonCompress_Varchar,
		Datalength(Compress(Varchar_Value)) DL_Compress_Varchar,
		NVarchar_Value,
		DataLength(NVarchar_Value) DL_NonCompress_NVarchar,
		Datalength(Compress(NVarchar_Value)) DL_Compress_NVarchar
From SQLZealot_Compress_Test

Complete Demo Script

drop table if exists SQLZealot_Compress_Test
CREATE TABLE SQLZealot_Compress_Test
(
ID int identity(1,1) NOT NULL Primary Key,
Varchar_Value VARCHAR(MAX),
NVarchar_Value NVARCHAR(MAX)
)

INSERT INTO SQLZealot_Compress_Test(Varchar_Value, NVarchar_Value)
VALUES 
 ( 'This is a Compress test by SQLZealot!', N'This is a Compress test by SQLZealot!!')
,( '1234567890', N'1234567890')
,( REPLICATE('X', 9000), REPLICATE('X' , 4500))
,( REPLICATE(CAST('X' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'X' AS NVARCHAR(MAX)), 4500))
,( REPLICATE(CAST('SQLZealot|' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'SQLZealot|' AS NVARCHAR(MAX)), 4500))

Insert into SQLZealot_Compress_Test
Select (Select * From OC_TABLEDEFS FOR JSON PATH, ROOT('Tables')) varcharval,
		(Select * From OC_TABLEDEFS FOR JSON PATH, ROOT('Tables')) nvarcharval

Select ID,
		Varchar_Value,
		DataLength(Varchar_Value) DL_NonCompress_Varchar,
		Datalength(Compress(Varchar_Value)) DL_Compress_Varchar,
		NVarchar_Value,
		DataLength(NVarchar_Value) DL_NonCompress_NVarchar,
		Datalength(Compress(NVarchar_Value)) DL_Compress_NVarchar
From SQLZealot_Compress_Test

Screenshot

Observations

1. Do not use COMPRESS & DECOMPRESS as a replacement for encryption/data masking.

2. If table has less size of data, then COMPRESS will not have a benefit, instead there may be a small fraction of overhead of long value of varbinary datatype.

3. If table has big size data, then COMPRESS seems to be a good option.

4. If table has unicode character datatype, the benefit seems to be lesser than character datatype.

5. Need to evaluate the CPU cycles effort (if you do COMPRESS & DECOMPRESS) in SQL Server.

6. It would be a good option to consider doing COMPRESS & DECOMPRESS functions in application layer.

7. If you have audit feature, it is a good option to consider the compressed data as part of audited info instead of actual data (if audited data is not being frequently used).

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

Always On Availability Group, DBA Utilities, SQL

Handy Troubleshooting T-SQL queries for Always On Availability Groups in SQL Server
Today, as part of a database migration from (one server to another server), wanted to check few things related to Always On Availability Groups to make sure all are intact. I am sharing those troubleshooting queries prepared for my verification with this blog post, hope this helps you as well.

How to identify available AGs to the server as Primary replica?

SELECT Groups.[Name] AS [AGname], [Primary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed, count(AGDatabases.database_name) [# Databases]
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
WHERE primary_replica = @@Servername
Group by Groups.[Name] , [Primary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed

How to identify available AGs to the server as Secondary replica?

SELECT Groups.[Name] AS [AGname], [secondary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica != @@Servername

How to identify AG – Databases distribution of a Primary replica?

SELECT
Groups.[Name] AS AGname,
AGD.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id
WHERE primary_replica = @@Servername
ORDER BY AGname ASC, Databasename ASC;

How to identify AG – Databases distribution of a Secondary replica?

SELECT
Groups.[Name] AS AGname,
AGD.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id
WHERE primary_replica != @@Servername
ORDER BY AGname ASC, Databasename ASC;

A quick health check of your AG

SELECT DISTINCT
Groups.name AS AGname,
AGReplicas.replica_server_name,
AGStates.role_desc,
AGStates.synchronization_health_desc,
ISNULL(AGReplicaStates.suspend_reason_desc,'N/A') AS suspend_reason_desc
FROM sys.availability_groups Groups
INNER JOIN sys.dm_hadr_availability_replica_states as AGStates ON AGStates.group_id = Groups.group_id
INNER JOIN sys.availability_replicas as AGReplicas ON AGStates.replica_id = AGReplicas.replica_id
INNER JOIN sys.dm_hadr_database_replica_states as AGReplicaStates ON AGReplicas.replica_id = AGReplicaStates.replica_id
Order by Groups.name

A quick health check of your AG – DAG


SELECT 
   ag.[name] AS [AG Name], 
   ag.is_distributed, 
   ar.replica_server_name AS [Underlying AG], 
   ars.role_desc AS [Role], 
   ars.synchronization_health_desc AS [Sync Status]
FROM  sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar 
   ON  ag.group_id = ar.group_id        
INNER JOIN sys.dm_hadr_availability_replica_states AS ars       
   ON  ar.replica_id = ars.replica_id

A quick health check of your AG – DB 

select DB_NAME(database_id),* from sys.dm_hadr_database_replica_states 
where synchronization_health_desc != 'HEALTHY'

In addition, SSMS provides a good dashboard to understand the Always On Availability Group and health status as below. I would highly recommend to refer for more details.

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