Setup SQL Agent alerts for User Error Messages

June 1, 2018Leave a comment

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

July 25, 2017Leave a comment

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)
	)
	WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = ON
)
GO

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)))

SELECT *
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

July 14, 2016July 14, 2016Leave a comment

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

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

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’

January 5, 20167 Comments

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

Resolution steps:

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
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

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

4. Change the working directory to c:\temp

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

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

December 23, 2015December 25, 2015Leave a comment

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.

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.

IO and time test

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.