Setup SQL Agent alerts for User Error Messages

Setup a simple alert with SQL agent


When I test the alert it does not trigger the alert

Capture 2


Since error is not being logged, we can verify in the sys.messages

Capture 3

Lets alter the message to log

Capture 4


SELECT * FROM sys.messages WHERE
language_id = 1033 AND severity = 16
and message_id = 2812
EXEC sp_altermessage @message_id = 2812, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘True’

Test the alert again, now we can see number of occurrences increases to 1

Capture 5


Log SQL Errors using Extended Events

First create the extended event session to monitor for any errors with severity grater than equal to 11

CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported (ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE ([severity] >= (11))) ADD TARGET package0.event_file (
	SET filename = N'ErrorCapture'
	,max_file_size = (100)
	,max_rollover_files = (5)


How to view them in TSQL

DECLARE @error_log VARCHAR(max) = convert(VARCHAR(max), SERVERPROPERTY('ErrorLogFileName'))
DECLARE @error_log_dir VARCHAR(max) = reverse(substring(reverse(@error_log), charindex('\', reverse(@error_log)), len(@error_log)))

FROM sys.fn_xe_file_target_read_file(@error_log_dir + 'errorcap*.xel', NULL, NULL, NULL)


Clone your SQL Database instantly with new DBCC command CLONEDATABASE


Based on MS documentation this is a quick way to create a copy of database (only Schema) including statistics and Indexes of source database this was released in SQL 14 service pack 2.

When this command is issued SQL Server creates an internal snapshot of source database just like how it creates for checkdb and drops this snapshot when the cloning process is done but during the cloning process it holds a shared lock on source database and X lock on target database and it leaves target database in read only mode although you can change the state of the target database if you intent to add data or modify.

So why do you need to clone database.

According to MS DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues.”. The original intention of the feature is to diagnose any performance issues of a production database with out needing to effect the production database. Although this is so late in the game its never to late for new feature.

Don’t confuse with Database Snapshots that’s totally different concept.
dbcc clonedatabase([AdventureWorks2014],[AdventureWorks2014_Clone])


dbcc clone 1


dbcc clone 2

Bottom line: Saves a lot of time when you are debugging execution plans and performance related issues.

FIX[solved]:Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime, Fatal error: cannot create ‘R_TempDir’

I was trying out new R feature in SQL 2016 and encountered this error so I thought I would document this issue.

Resolution steps:

  1. Locate rlauncher config file: (this is used by the MSSQL Launch pad service)
    1. Its normally located in the Binn folder which is available in instance root directory in my case D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn
  2. View the contents of rlauncher and check the working directory which is where we are having issues creating temp dir.
    1. R does not like spaces in the working directory

R launcher 1






3. Create a folder in C:\ called temp “C:\temp”

4. Change the working directory to c:\temp


R launcher 2





5. Grant permissions for the service MSSQLaunchpad service account in my case its NT Service\MSSQLaunchpad

R launcher 3













Restart the SQL server services and SQL Launch pad services.


We can also fix this by using short paths by replacing the working directory with a short path

Eample below cmd will give us the short path for the location

cmd /c for %A in (“D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData”) do @echo %~sA


SQL 2016 DB engine features Compress and Decompress functions

SQL server added new function Compress and Decompress scalar valued function that take char/varchar as input and output a GZIP compressed varbinary data. This feature allows us to use column level compression of char/varchar data and reduces the cost of IO.

sql2016 compress

we can see we achieved almost 99% please note these results varies when data is stored in a table (row in flow)

Lets look at this in a detail example.

sql2016 compress2


sql2016 compress3



IO and time test

sql2016 compress4


Summary of comparision matrix between compress function and regular table we achieve 99% savings on storage on certain situations and achieve even more space when used in conjunction with row and page compression.

sql2016 compress5