Category: Database

  • Different options to check object definition

    SP_HELPTEXT system stored procedure is mostly used to check the object definition like definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

    OBJECT_DEFINITION – is BUILT IN function returns the source text of the specified object. It returns the definition of check constraint, default constraint, stored procedure, function, rule and views.

    Option 1

    EXEC sp_helptext 'Procedure Name'

    Option 2

    select object_definition(object_id('procedure name'))
go

    Option 3

    select    [definition]
from    sys.sql_modules
where    object_id = object_id('procedure name')
go

  • T-SQL Script to check SQL Server Instance TCP/IP Port Number

    SQL Server Error Log

    -- Check the error log for port number
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

    Executing DMV Script

    -- you must connect remotely using TCP/IP
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

    For Dynamic Port Number
    — Read the registry for port number

    -- Execute below script if SQL Server is configured with dynamic port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

    For Static Port Number

    -- Execute below script if SQL Server is configured with static port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

  • Error Fix: Microsoft SQL Server, Error: 14516

    Problem: Proxy (1) is not allowed for subsystem “SSIS” and user “Domain\UserName”. Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. (Microsoft SQL Server, Error: 14516)

    Solution:
    Above error occurs when the user with the minimum permission (i.e. SQLAgentReaderRole and SQLAgentUserRole) or the user is configured as job owner and trying to run the job which is running under the proxy account security context.

    You can execute below script to grant permission to the user and fix the error.

    
EXEC dbo.sp_grant_login_to_proxy
    @login_name = 'Domain\UserName',
    @proxy_name = 'Proxy Name' ;
GO

  • SQL Server – Backup Verification Script – Based on conditions

    Problem: There are scenarios where you have to verify the multiple SQL Server instances backup within limited time frame whether it meet your organization backup criteria standards or not.

    Solution:
    You can execute below T-SQL script to check the backup status as per your organization standard by assigning backup conditions value to backup criteria variable. You can execute below script either by connecting each SQL Server Instance or execute it by registering all the SQL Server Instances in Central Management Server (CMS).

    Below T-SQL Script work with all the versions of MSSQL Server and verify FULL, Differential and Transaction Log Backup. It will check the max backup date and evaluate result based on condition as backup is Passed or Not.

    Backup Verification Script will return below values. Column names are self-explanatory.
    • SQLInstanceName
    • DatabaseName
    • db_create_ddate
    • DB_Recovery_Model
    • dbstatus
    • windowsservername
    • productversion
    • productlevel
    • edition
    • current_datetime
    • last_full_backup_date
    • last_diff_backup_date
    • last_tran_backup_date
    • days_since_last_full_backup
    • days_since_last_diff_backup
    • hours_since_last_tranlog_backup
    • Full_Backup_Stauts
    • Diff_Backup_Stauts
    • Log_Backup_Stauts
    • full_backup_location
    • diff_backup_location
    • tlog_backup_location

    You have to specify the backup condition based on your organization standard by assigning values to below variables.

    <br />
declare @full_backup_criteria_in_days int<br />
declare @diff_backup_criteria_in_days int<br />
declare @log_backup_criteria_in_hours int</p>
<p>-- specify the backup criteria<br />
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br />
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br />
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass<br />

    Complete Backup Verification Script

    <br />
declare @full_backup_criteria_in_days int<br />
declare @diff_backup_criteria_in_days int<br />
declare @log_backup_criteria_in_hours int</p>
<p>-- specify the backup criteria<br />
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br />
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br />
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass</p>
<p>select<br />
           serverproperty('servername') as SQLInstanceName,<br />
           quotename(bkup_full.[database_name]) as DatabaseName,<br />
            ( select    sdb.crdate<br />
              from      [master]..[sysdatabases] sdb<br />
              where     sdb.name = bkup_full.[database_name]<br />
            ) as [db_create_ddate],<br />
           databasepropertyex(bkup_full.[database_name],'recovery')  as DB_Recovery_Model,<br />
           databasepropertyex(bkup_full.[database_name],'status')  as dbstatus,<br />
           case serverproperty('isclustered')<br />
                        when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)<br />
                        when 0 then cast(serverproperty('machinename') as varchar)<br />
           end as windowsservername,<br />
           serverproperty('productversion') as productversion,<br />
           serverproperty('productlevel') as productlevel,<br />
           serverproperty('edition') as edition,</p>
<p>           current_timestamp as current_datetime,</p>
<p>		   bkup_full.[backup_finish_date] as [last_full_backup_date],<br />
		   bkup_diff.[backup_finish_date] as [last_diff_backup_date] ,<br />
		   bkup_log.[backup_finish_date] as [last_tran_backup_date] ,</p>
<p>           datediff(dd, bkup_full.[backup_finish_date], current_timestamp) as [days_since_last_full_backup] ,<br />
           datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) as [days_since_last_diff_backup] ,<br />
           datediff(hh, bkup_log.[backup_finish_date], current_timestamp) as [hours_since_last_tranlog_backup] ,</p>
<p>           case<br />
                        when datediff(dd, bkup_full.[backup_finish_date], current_timestamp) &lt;= @full_backup_criteria_in_days<br />
                        then 'Pass'<br />
                        else 'Fail'<br />
                        end as Full_Backup_Stauts,</p>
<p>            case<br />
                        when datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) &lt;= @diff_backup_criteria_in_days  then 'Pass'<br />
                        else case when quotename(bkup_full.[database_name]) IN ('[master]') then 'N/A' else 'Fail' end<br />
                        end as Diff_Backup_Stauts,</p>
<p>            case<br />
                        when datediff(hh, bkup_log.[backup_finish_date], current_timestamp) &lt;= @log_backup_criteria_in_hours  then 'Pass'<br />
                        else case when databasepropertyex(bkup_full.[database_name],'recovery') = 'SIMPLE' then 'N/A' else 'Fail' end<br />
            end as Log_Backup_Stauts,            </p>
<p>            ( select top 1 [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_full.[media_set_id]<br />
            ) as [full_backup_location] ,<br />
            ( select top 1  [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_diff.[media_set_id]<br />
            ) as [diff_backup_location] ,<br />
            ( select top 1  [physical_device_name]<br />
              from      [msdb]..[backupmediafamily] bkup_media_family<br />
              where     bkup_media_family.[media_set_id] = bkup_log.[media_set_id]<br />
            ) as [tlog_backup_location]</p>
<p>    from    [msdb]..[backupset] as bkup_full<br />
            left join [msdb]..[backupset] as bkup_log on bkup_log.[database_name] = bkup_full.[database_name]<br />
                                                       and bkup_log.[server_name] = bkup_full.[server_name]<br />
                                                       and bkup_log.[type] = N'L'<br />
                                                       and bkup_log.[backup_finish_date] = ( (select  max([backup_finish_date])<br />
                                                                                            from    [msdb]..[backupset] b2<br />
                                                                                            where   b2.[database_name] = bkup_full.[database_name]<br />
                                                                                                    and b2.[server_name] = bkup_full.[server_name]<br />
                                                                                                    and b2.[type] = N'L') )<br />
            left join [msdb]..[backupset] as bkup_diff on bkup_diff.[database_name] = bkup_full.[database_name]<br />
                                                       and bkup_diff.[server_name] = bkup_full.[server_name]<br />
                                                       and bkup_diff.[type] = N'I'<br />
                                                       and bkup_diff.[backup_finish_date] = ( (select max([backup_finish_date])<br />
                                                                                            from    [msdb]..[backupset] b2<br />
                                                                                            where   b2.[database_name] = bkup_full.[database_name]<br />
                                                                                                    and b2.[server_name] = bkup_full.[server_name]<br />
                                                                                                     and b2.[type] = N'I') )<br />
    where   bkup_full.[type] = N'D'<br />
            and bkup_full.[backup_finish_date] = ( (select  max([backup_finish_date])<br />
                                                 from   [msdb]..[backupset] b2<br />
                                                 where  b2.[database_name] = bkup_full.[database_name]<br />
                                                        and b2.[server_name] = bkup_full.[server_name]<br />
                                                        and b2.[type] = N'D') )<br />
            and exists ( select [name]<br />
                         from   [master]..[sysdatabases]<br />
                         where  [name] = bkup_full.[database_name] )<br />
            and bkup_full.[database_name] &lt;&gt; N'tempdb'<br />

    This script is the “Sustain” dimension

    A backup you haven’t verified is a hope, not a plan — and this script is exactly the kind of continuous, boring verification that keeps a data platform honest. In my AI risk framework, this is Sustain: monitor and govern, so the other controls hold up in production. The framework: R-I-S-K-S: The 5-Dimension Framework for AI Risk Management

  • Spinlocks lightweight synchronization primitives

    Spinlocks are lightweight synchronization primitives which are used to protect access to data structures. Spinlocks are used to access data structures for a very short period of time. When a thread attempting to acquire a spinlock and if it is unable to obtain access it executes in a loop periodically checking to determine if the resource is available instead of immediately yielding.

    After some period of time a thread waiting on a spinlock will yield before it is able to acquire the resource in order to allow other threads running on the same CPU to execute. This is known as a backoff.

    When a large number of threads are contending for access to a single spinlock and it can lead to performance problems

    You can use below DMV to get the SPIN LOCK information

    select * from sys.dm_os_spinlock_stats

    Examples
    FGCP_PRP_FILL
    OPT_IDX_STATS
    BUF_FREE_LIST