Category: SQL

DBA Utilities, SCRIPT GALLERY, SQL

A way to Shrink Data File in SQL Server

Problem Statement

We recently wanted to drop few obsolete tables those are really big ones in my SQL Server databases. Post clean up, meaning dropping the tables, we would like to reclaim the space it occupied to OS and reduce the cost associated with. What are the best options in such situations?

DBA’s Ponderings

Usually, DBAs would never support shrinking of data file due to several reason. They consider about their system performance than the disk cost, especially, now days, the worry on cost associated must be an outdated belief. Shrink can cause many bad on your databases and some of them are listed as below:

1. It increases the fragmentation.

2. It will have a system performance issues due to heavy fragmentation.

3. While running shrink a data file, it may have dangerous locking issues. This must be an offline activity.

4. If shrink file on a LOB data, that would slow down the process badly.

Due to all the above reasons, we should always keep a long distance to shrink file operations. Thats not our best friend for our system.

Then, How do we shrink data file?

Then, What we do if its mandatory to do shrink the data file as our initial Problem Statement?

If its mandatory or meaningful or sensible, then you can do with few extreme considerations. In one of my case, I had to clean up almost 1000 tables from a database and I had to do this for almost 2000 databases. It had almost 4GB data to be cleared from a single database resulting a whooping amount of cost save! This is a real example where shrink file is important or a valid reason to do so. Myself & one of my colleague “Pascal Benguigui” have come up with an approach here as below.

Let us look at the options/considerations to apply shrink file as below. It may vary for others, but its more of our deliberate thoughts.

1. Understand the size of data that you wanted to eliminate. To understand, I would suggest you to take backup and restore your database to local and remove the data followed by shrink file and then identify the current size of data file.

2. If the file size if more than 1000 MB, we can consider shrinkfile, otherwise, it may not be so efficient.

3. If the file size is more than 1000 MB, let us also consider the percentage of free space. If free space percentage is lesser than 10%, it may not be so efficient again.

4. Let us also shrink the file up to the used space and 10 percentage added to the space. This must be a good number, but depends!, it may or may not suitable for all cases. A detailed analysis can be carried out and define as per your environment leaving it to the respective DBA’s descretion.

Shrink Script


CREATE OR ALTER procedure Shrink_DataFile @db varchar(200)
as
BEGIN

declare @f_name varchar(200)
declare @f_file varchar(500)
declare @f_size int
declare @f_used int
declare @pct_free int
declare @f_newsize int
declare @sql_query nvarchar(2000)
declare @sql_param nvarchar(200)

print 'Datafile SHRINK requested on database ['+@db+'] : '
declare cur_name cursor for 
select name from sys.sysaltfiles where dbid=db_id(@db) and groupid=1 and CONVERT(sysname,DatabasePropertyEx(@db,'Updateability'))='READ_WRITE' order by fileid asc

open cur_name
fetch next from cur_name into @f_name

while @@fetch_status=0
       begin

       select @sql_param =N'@f_size_out int OUTPUT,@f_used_out int OUTPUT,@f_file_out varchar(500) OUTPUT'
       select @sql_query=N'use ['+@db+']; select @f_size_out=CAST(s.size/128.0 AS int) , @f_used_out=CAST(FILEPROPERTY(s.name, ''SpaceUsed'')/128.0 AS int),@f_file_out=filename from sys.sysfiles s where groupid=1 and name='''+@f_name+''''
       EXEC sp_executesql @sql_query, @sql_param, @f_size_out=@f_size OUTPUT, @f_used_out=@f_used OUTPUT,@f_file_out=@f_file OUTPUT ;
       select @pct_free=(@f_size-@f_used)*100/@f_size

       if @f_size >1000 and @pct_free>10
             BEGIN
             -- shrink should keep 10% free space and be a multiple of 100 higher
             select @f_newsize=@f_used*1.1
             select @f_newsize=ceiling(cast(@f_newsize as decimal(38,2))/100)*100
             if @f_newsize < @f_size
                    BEGIN
                    print 'File "'+@f_file+' (size '+convert(varchar,@f_size)+' MB) " will be shrinked to '+convert(varchar,@f_newsize)+' MB ...'
                    select @sql_query=N'USE ['+@db+']; 
                    DBCC SHRINKFILE (N'''+@f_name+''' , '+convert(varchar,@f_newsize)+')  WITH NO_INFOMSGS'
                    print @sql_query
                    exec sp_executesql @sql_query

                    select @sql_param =N'@f_size_out int OUTPUT'
                    select @sql_query=N'use ['+@db+']; select @f_size_out=CAST(s.size/128.0 AS int) from sys.sysfiles s where groupid=1 and name='''+@f_name+''''
                    EXEC sp_executesql @sql_query, @sql_param, @f_size_out=@f_size OUTPUT;
                    print '... New size for file "'+@f_file+'" shrinked is '+convert(varchar,@f_size)+' MB
'
                    END
             else
                    print 'NO SHRINK : New size estimated for file "'+@f_file+'" ('+convert(varchar,@f_newsize)+' MB) not lower than the current file size ('+convert(varchar,@f_size)+' MB)'

             END
       else
             print 'NO SHRINK : Size requirements not valid to shrink file "'+@f_file+' (size '+convert(varchar,@f_size)+' MB / free space '+convert(varchar,@pct_free)+' %)
       '

       fetch next from cur_name into @f_name
       end
       close cur_name
deallocate cur_name

if @f_file is null
       print 'database ['+@db+'] not existing or not in READ-WRITE mode
'

END
GO
Post shrinkfile, we would expect lots of fragmentations. This can severely impact the system performance. We need to make sure that we need to avoid fragmentation as quickly as possible by running INDEX rebuild/reorganize. There is a well defined index maintenance solution defined by Ola Hallengren that can be used for the same.

If you have to plan it for many number of databases, you need to consider this to happen in multiple batches to ensure that this process does not hurt the system. There may be a chance that your immediate backup jobs taking longer time than usual. Importantly, we need to make sure the shrink followed by index defragmentation is planned at application off peak hours, otherwise it might end up with larger number of blocking/locking scenarios.

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

FUN with SQL, SQL

How to identify Prime numbers in SQL Server
While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment. 
declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
    SELECT 2
    UNION ALL
    SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n 
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);

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

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!

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!