SQL Server Error Handling

A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

The following statements returns the error information.

 

SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

  • ERROR_NUMBER() returns the number of the error.

  • ERROR_SEVERITY() returns the severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

 

Lets try to generate simple error and catch in the exception block.

the following code is an example division by zero

 

USE test1
GO
BEGIN TRY
— divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

the output display the error information.

ErrorNumber      ErrorSeverity                          ErrorState                     ErrorProcedure                             ErrorLine                      ErrorMessage
8134                            16                                                       1                                            NULL                                            3                                       Divide by zero error encountered.

 

TRY…CATCH constructs do not trap the following conditions:+

  • Warnings or informational messages that have a severity of 10 or lower.
  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  • Attentions, such as client-interrupt requests or broken client connections.
  • When the session is ended by a system administrator by using the KILL statement.

    Compile errors, such as syntax errors, that prevent a batch from running.

  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

    These errors are returned to the level that ran the batch, stored procedure, or trigger.

 

The following SQL statement is not caught by begin try, because it is name resolution/statement level recompilation.

 

BEGIN TRY
— Table does not exist; object name resolution
— error not caught in try catch block
SELECT * FROM mytable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

 

The following stored procedure generates object resolution error, which is trapped in the begin and try block.

 

 

— Verify that the stored procedure exist. if exisit drop it
IF OBJECT_ID ( N’myProc’, N’P’ ) IS NOT NULL
DROP PROCEDURE myProc;
GO

— Create a stored procedure that will cause an
— object resolution error.
CREATE PROCEDURE myProc
AS
SELECT * FROM mytable;
GO

BEGIN TRY
EXECUTE myProc;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

— Clean up

IF OBJECT_ID(N’myproc’,N’P’) IS NOT NULL
DROP PROCEDURE myproc

 

ErrorNumber                                ErrorSeverity                          ErrorState                                ErrorProcedure                            ErrorLine                           ErrorMessage
208                                                        16                                                1                                                 myProc                                             6                                              Invalid object name ‘mytable’.

 

 

Error Handling with Transaction Examples.

 

BEGIN TRANSACTION;

BEGIN TRY
— Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

 

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

USE test1;
GO
IF OBJECT_ID(N’t2′, N’U’) IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N’t1′, N’U’) IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); — Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); — Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
— SELECT shows only keys 1 and 3 added.
— Key 2 insert failed and was rolled back, but
— XACT_ABORT was OFF and rest of transaction
— succeeded.
— Key 5 insert error with XACT_ABORT ON caused
— all of the second transaction to roll back.
SELECT *
FROM t2;
GO

 

SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION;
— A FOREIGN KEY constraint exists on this table. This
— statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;

— If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
— Execute error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;

— Test XACT_STATE:
— If 1, the transaction is committable.
— If -1, the transaction is uncommittable and should
— be rolled back.
— XACT_STATE = 0 means that there is no transaction and
— a commit or rollback operation would generate an error.

— Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N’The transaction is in an uncommittable state.’ +
‘Rolling back transaction.’
ROLLBACK TRANSACTION;
END;

— Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N’The transaction is committable.’ +
‘Committing transaction.’
COMMIT TRANSACTION;
END;
END CATCH;
GO

SQL Server T SQL Code Standards

 

USE [dbname]

GO

/****** Object:  StoredProcedure [dbo].[SPInsertCollectionCallDetails]    Script Date: 02/23/2016 11:14:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author:        <Hari Ere>

— Create date: <01/17/2014>

— Description:   This procedure input the package process steps

 

— Excute SP

/*USE [dbname]

GO

DECLARE     @return_value int

EXEC  @return_value = dbo.spinsert

@id = 1,

@ProcessStepInfo = NULL,

@Status = NULL

SELECT      ‘Return Value’ = @return_value

GO

*/–Edits History:

— =============================================

BEGIN TRY

— code here

SELECT  ‘hello world’

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT  @ErrorMessage = ERROR_MESSAGE() ,

@ErrorSeverity = ERROR_SEVERITY() ,

@ErrorState = ERROR_STATE();

 

EXEC sp_send_dbmail @profile_name = ‘Mail_ Profile’,

@recipients = ‘myemail@libertyutilities.com’,

@subject = ‘Error from procedreuname ‘, @body = @ErrorMessage;

END CATCH;

GO


If code required in Transaction, please make sure SET XACT_ABORT ON(rolls back the current transaction when a Transact-SQL statement raises a run-time error )

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION;

code

COMMIT TRANSACTION;

GO

 

 

 

TSQL best practices.

 

  • To improve code readability, use proper indention for the statements.
  • To understand code easily, please add comments with each begging of the logic.
  • Use CAPS for all Sql Server Keywords ( Ex: SELECT,ORDER BY).
  • Use SET NOCOUNT ON option at the top of procedure to avoid messages.
  • Do not use SELECT * all columns, instead specific column names in TSQL.
  • Write object names with fully qualified names.(Databasename.schema.objectname).
  • SYNONYMs provide a layer of abstraction over the referenced object with a simplified alias as a same server resident object.
  • Declare variables in required size, which eliminates consuming memory buffers.
  • Never create stored procedure name with SP, SP is reserved for system SQL server.
  • Try to avoid using cursors, use While loops.
  • Create views if the quires are complex.
  • Avoid sub quires and use CTE, CTE’S are reside on memory for faster access.
  • Use ORDER BY,DISTINCT,TOP only when required.
  • Avoid the temp table in the stored procedure. Stored procedures are usually use a cached execution plan to increase the performance. When you use the temp table it will do the compilation every time.
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.(ex: EXECUTE sp_executesql @Query)
  • Keep the transaction as short as possible, with SET XACT_ABORT ON. This will roll back when error raise.
  • USE TRY CATCH BLOCK to trap the errors, and send alerts.
  • Use table variables inside the SP, for small datasets. If the data sets are larger, use temp tables and add index for better performance.
  • Create backup tables into a separate database, it will help DBA’s for cleaning.
  • To avoid deadlocks Always access tables in the same order in all your Stored Procedures and triggers consistently, keep your transactions as short as possible.

Never, ever wait for user input in the middle of a transaction.

  • Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime.
  • Dropping a temp table
  • Ex:

IF OBJECT_ID(‘tempdb..#CpuCounters1’) IS NOT NULL

    DROP TABLE #CpuCounters1

GO

 

 

 

CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016

New CREATE OR ALTER support makes it easier to modify and deploy objects like Stored Procedures, Triggers, User–Defined Functions, and Views. This feature is introduced in SQL Server 2016 SP1.

 

OLD syntax Example

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = ‘spMyProcedure’ AND TYPE = ‘P’)
BEGIN
DROP PROCEDURE spMyProcedure
END
GO
CREATE PROCEDURE spMyProcedure (@Name VARCHAR(100))
AS

DECLARE @MyVar VARCHAR(10)
SET @MyVar = ‘Geek!’
BEGIN
SELECT @Name +’ Is a ‘ + @MyVar
END
GO

New syntax Example

 

CREATE OR ALTER PROCEDURE spMyProcedure (@Name VARCHAR(100))
AS

DECLARE @MyVar VARCHAR(10)
SET @MyVar = 'Geek!!!!'
BEGIN
 SELECT @Name +' Is a ' + @MyVar
END
GO

Sql Server Upgrade Plan

there are two types of Upgrade strategy

In-Place Upgrading.

Side-by-side Upgrading.

In-Place Upgrade:  An in-place approach allows to upgrade existing SQL Server instance to a higher version. the main benfit of this approach is you can still use existing hardware,instance name. there is no worry on application connection string configuration, logins,users,SQL agent jobs creation.

This approach requires downtime as SQL server instance is offline during upgrade, in place upgrade is not supported for all components and if any issue occur complex and manual rollback strategy .

 

Side-by-Side Upgrade : This approach quite simple as name suggest, a new sql server instance can be installed on the same server or different new sever. which means server can have SQL2005,2008,2008R2,2012,2014 on same box. one instance is default and others are named instance.

 

This approach has more granular control over upgrade component-level.

you can run sql server side by side for testing.

you can have rollback strategy  because the the original system is still intact.

 

 

 Pre-Upgrade Steps:

performing an upgrade can be a complex and critical process .to mitigating the risk of a failed upgrade or unexpected post-upgrade behavior, a proper research can be perform on instance using available tools.

SQL Server Upgrade Tools:  SQL server 2014 Upgrade Advisory is a free download available as part of MS 2014 and also available in install media.

the purpose of this tool is to identify known upgrade issues and provide workarounds for fixes.(https://msdn.microsoft.com/en-us/library/ee210480(v=sql.120).aspx)

identify backward compatibility and discontinued features.

perform impact analysis on applications and inform to the application owners, this is very critical steps. some vendor applications may not be compatible , this is required further analysis on workarounds

Upgrade Execution Process:

Document each step of upgrade process, This  includes upgrade steps,document locations,server IP,Resources information,Application owners.

Attache tests results on Dress rehearsals and analyze the ares of issues identified during upgrading on test servers.

communicate with the vendors if any alarms are raised.

Post-Upgrade Process: 

Document all post upgrade tasks, user creation,user access, connection string configurations with third party applications,backups and maintenance plans,job scheduling etc.

sanity checks with QA before system handover to business for validations.

 

once the above process is completed and tested on development/QA/UAT servers and time to go for Production upgrade in a smooth way.

i did not touch on hardware selection inthis article as name implies it is only for upgrade steps.

 

 

 

 

 

 

 

 

 

 

SQL Server Installation Planing,configurations and checks

Base Line of current work loads.

Number of user,logins,connections at a given point.

Estimated growth in work load.

Minimum software and Hardware Requirements.

Storage system sizing and I/O requirements.

Choosing SQL Server editions/License.

Service Accounts Selection.

Password Policy.

Limit using of SA during Upgrades with third party vendors.

Hardening systems with company security policy.

Isolate drives for system databases,Temp db and user data and log files for better performance.

The number of allocated data files in the TempDB database depends on the logical or physical CPUs number on your server.

Configure Min and Max SQL Server Memory, initially sql server acquires minimum memory.  as work load requests from users it will consume memory to max limit.

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

define Databases default paths.

Configure MAX Degree of parallelism according to your work needs, please refer Microsoft Best Practices. (https://support.microsoft.com/en-ca/kb/2806535)

configure Sql Server error logs,

Configure Database Mail with public and private profiles.

Configure SQL Server agent operator for Alerts purpose.

Create default alerts for severities (16-25).

Database maintenance,backup and recovery steps.

Offsite backup plans.

Minimum up time and SLA’s.

Store SA/Service accounts password in vaults/key pass.

Create complete document if High available solution is required (Cluster,Always on groups, Log shipping).

Disaster Recovery Strategy.

Configure Daily health checks Sql server Systems. If required buy third party tools.

Configure Performance Monitoring tools. Store DMV’s in a single location of network share for peridoic helath checks by DBA’s

Create scripts for Daily refresh on DEv and QA environments.

Secure Backups by enforcing Encryption.

Develop plan on periodic backup validationand admin passwords rest

install Service packs and CU’s

for more information please refer to the Microsoft

https://msdn.microsoft.com/en-us/library/bb500442(v=sql.120).aspx

 

 

SQL Server System Databases

SQL Server system Databases are very critical. Lets talk one at a time.

Resource Database.

Resource database is read-only database and that contains all the system objects. like sys.objects…

it does not contain any user data or user meta data. Resource database makes u[grading to a new version much easier and faster procedure. Resource database is located under

<drive>:\programfiles\Microsoft SQL server\MSSQL\<version>.<instance_name>\mssql\binn  (mssqlsystemresource.mdf)

SQL Server cannot backup and restore resource database. you can perform only file based copy. Resource database should be modified only with the direction of Microsoft support.

 

The id of the resource database is 32767.

 

/*Resource Database*/

–To determine the version of the resource database

SELECT SERVERPROPERTY(‘ResourceVersion’)
GO;
— To determine twhen the resource databse was last updated

SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’)
GO;
— To access SQL definations of system objects

SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects’))

 

master database:

the master database contains information about your databases logins,configurations,file locations and information about the instance.

main difference between master and resource db’s are. master database contains information about the instance and resource contains information about schema and stored procedures that needed to run your instance.

do not create any objects in master database.

temp Database:

temp database is used to store user temporary objects needed by the database engine and the rwo version. indexing sorting.

model database: 

model database is a system database that creates a template when ever sql server creates a database. but this will not make an entry when database is restored or attached.

 

msdb databse:

msdb datasbe contains inforation  on sql agent jobs,agent job history,log shipping,SSIS and backup restore information., job alerts and policies.

 

master,msdb,model database should be backup regularly.

 

 

 

 

 

DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

Microsoft released SQL Server 2014 Service Pack 2, which contains a new command: DBCC CLONEDATABASE. This new command creates a clone of a database’s schema and statistics (not the data of tables). Since it has the statistics, it is useful for troubleshooting, investigating, and diagnosing performance issues.

This will eliminate your production database to diagnose query performance tuning on problematic procs or TSQL.

 

in the following example, I have cloned adventureworks 2014.

Syntax: DBCC CLONEDATABASE (source_database_name, target_database_name)

dbcc clonedatabase([AdventureWorks2014],[AdventureWorks2014_Clone])

for more information please read Microsoft KB article.

https://support.microsoft.com/en-us/kb/3177838

 

dbcc clone