Category Archives: Database

Database Backup WITH CHECKSUM

Backup WITH CHECKSUM

When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
In case backup completes successfully it means no bad checksums.

Example

backup database sqldba to disk = 'c:\jspace\sqldba1.bak'
with checksum

You can also use the 3023 trace flag for the check sum option, when 3023 trace flag is on WITH CHECKSUM option will be applied all the backups by default.

DBCC TRACEON(3023)
GO                              

backup database sqldba to disk = 'c:\jspace\sqldbatraceflag.bak'

DBCC TRACEOFF(3023)
GO                              

Let’s check if the 3023 trace flag applied the page checksum to above backup command or not. Execute below query for it.

RESTORE HEADERONLY
FROM DISK = 'c:\jspace\sqldbatraceflag.bak'
GO  

HasBackupCheckSums value is 1, it means that WITH CHECKSUM option applied to backup using 3023 trace flag.
checksum

OpenDataSouce function – to query OLEDB Data Source

OPENDATASOURCE
OpenDataSouce function helps you to get ad hoc connection information as part of a four-part object name as an one time alternative of linked server. You don’t have to specify or create the linked server to query other data sources (i.e. MS Excel, MS Access, MSSQL Older version to newer version etc.) if you are querying it infrequently.

You can use OPENDATASOURCE for the OLEDB data sources those are accessed infrequently, for several time use linked server as it provides more functionality.

You can get more information about the arguments of OpenDataSource function on MSDN site.

To use the OPENDATASOURCE you have to enable the ad hoc distributed queries. You required to have execute permission to use OPENDATASOURCE fucntion.

Execute below query to enable it

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

Make sure Provider AllowInProcess and DynamiceParameters value is checked. For example lets enable it for SQLNCLI10 provider.

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'DynamicParameters', 1
GO

OpenDataSource Examples

-- SQL Server 2000/2005/2008
-- You can use SQLNCLI10 provider for SQL Server 2008 as well
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  
   

-- SQL Server 2012
SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI11', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName
   
   
SELECT *
FROM OPENROWSET('SQLNCLI11',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  

--Access DB
SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 
                              'Data Source=D:\MyDB\MyAccessDB.accdb')...TableName   

Common Errors
Error 1#
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI11” has not been registered.
Solution: You will get above error if you have mentioned SQLNCLI11 while running OPENDATASOURCE query on SQL Server 2008 or lower version, it will work fine on SQL Server 2011. You can check list of registered provider by browsing Server Objects -> Linked Servers -> Provider in SSMS
Error 2#
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

Solution: Enable the ad hoc distributed queries by executing above SP_CONFIGURE query.

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.

declare @full_backup_criteria_in_days int
declare @diff_backup_criteria_in_days int
declare @log_backup_criteria_in_hours int

-- specify the backup criteria
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass 
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass 
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass

Complete Backup Verification Script

declare @full_backup_criteria_in_days int
declare @diff_backup_criteria_in_days int
declare @log_backup_criteria_in_hours int

-- specify the backup criteria
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass 
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass 
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass

select     
           serverproperty('servername') as SQLInstanceName,
           quotename(bkup_full.[database_name]) as DatabaseName,
            ( select    sdb.crdate
              from      [master]..[sysdatabases] sdb
              where     sdb.name = bkup_full.[database_name]
            ) as [db_create_ddate],           
           databasepropertyex(bkup_full.[database_name],'recovery')  as DB_Recovery_Model,
           databasepropertyex(bkup_full.[database_name],'status')  as dbstatus,       
           case serverproperty('isclustered') 
                        when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
                        when 0 then cast(serverproperty('machinename') as varchar)
           end as windowsservername,
           serverproperty('productversion') as productversion,
           serverproperty('productlevel') as productlevel,
           serverproperty('edition') as edition,

           current_timestamp as current_datetime,
		   
		   bkup_full.[backup_finish_date] as [last_full_backup_date],           
		   bkup_diff.[backup_finish_date] as [last_diff_backup_date] ,
		   bkup_log.[backup_finish_date] as [last_tran_backup_date] ,

           datediff(dd, bkup_full.[backup_finish_date], current_timestamp) as [days_since_last_full_backup] ,
           datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) as [days_since_last_diff_backup] ,
           datediff(hh, bkup_log.[backup_finish_date], current_timestamp) as [hours_since_last_tranlog_backup] ,
            
           case 
                        when datediff(dd, bkup_full.[backup_finish_date], current_timestamp) <= @full_backup_criteria_in_days  
                        then 'Pass'
                        else 'Fail'
                        end as Full_Backup_Stauts,
            
            case 
                        when datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) <= @diff_backup_criteria_in_days  then 'Pass'
                        else case when quotename(bkup_full.[database_name]) IN ('[master]') then 'N/A' else 'Fail' end                        
                        end as Diff_Backup_Stauts,

            case 
                        when datediff(hh, bkup_log.[backup_finish_date], current_timestamp) <= @log_backup_criteria_in_hours  then 'Pass'
                        else case when databasepropertyex(bkup_full.[database_name],'recovery') = 'SIMPLE' then 'N/A' else 'Fail' end                        
            end as Log_Backup_Stauts,            

            ( select top 1 [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_full.[media_set_id]
            ) as [full_backup_location] ,
            ( select top 1  [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_diff.[media_set_id]
            ) as [diff_backup_location] ,
            ( select top 1  [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_log.[media_set_id]
            ) as [tlog_backup_location]
         
                             
    from    [msdb]..[backupset] as bkup_full
            left join [msdb]..[backupset] as bkup_log on bkup_log.[database_name] = bkup_full.[database_name]
                                                       and bkup_log.[server_name] = bkup_full.[server_name]
                                                       and bkup_log.[type] = N'L'
                                                       and bkup_log.[backup_finish_date] = ( (select  max([backup_finish_date])
                                                                                            from    [msdb]..[backupset] b2
                                                                                            where   b2.[database_name] = bkup_full.[database_name]
                                                                                                    and b2.[server_name] = bkup_full.[server_name]
                                                                                                    and b2.[type] = N'L') )
            left join [msdb]..[backupset] as bkup_diff on bkup_diff.[database_name] = bkup_full.[database_name]
                                                       and bkup_diff.[server_name] = bkup_full.[server_name]
                                                       and bkup_diff.[type] = N'I'
                                                       and bkup_diff.[backup_finish_date] = ( (select max([backup_finish_date])
                                                                                            from    [msdb]..[backupset] b2
                                                                                            where   b2.[database_name] = bkup_full.[database_name]
                                                                                                    and b2.[server_name] = bkup_full.[server_name]
                                                                                                     and b2.[type] = N'I') )
    where   bkup_full.[type] = N'D'
            and bkup_full.[backup_finish_date] = ( (select  max([backup_finish_date])
                                                 from   [msdb]..[backupset] b2
                                                 where  b2.[database_name] = bkup_full.[database_name]
                                                        and b2.[server_name] = bkup_full.[server_name]
                                                        and b2.[type] = N'D') )
            and exists ( select [name]
                         from   [master]..[sysdatabases]
                         where  [name] = bkup_full.[database_name] )
            and bkup_full.[database_name] <> N'tempdb'

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