Sunday, October 12, 2014

Automated notifications on SQL Server errors

Can SQL Server send a notification to me when a deadlock is occurred? Can SQL Server alert me when a database starts suffering with resource issues? I am sure that you have already gone through these type of requirements and I am sure that you have already implemented a mechanism for getting alerts. However, how it has been implemented? Using set of codes? Using Agent Jobs? You may have used a different approach but that is what I have seen with many, nothing wrong with it unless you need more details, run through some logic and have a great flexibility. If all you want is, just to get an "Alert", why do not you use "SQL Server Alerts"?

SQL Server Alerts
SQL Server Alert is a predefined response to an event. You can create an alert configuring it as a "SQL Server event alert", "SQL Server performance condition alert", or "WMI event alert". In order to create an alert for errors, "SQL Server event alert" should be selected. It allows us to create a response to individual error or to all errors of a specific severity level. For example, if you want to get an alert on file growth issues, you can create the Alert for error number 1105 which come under 17 severity level. If you want to get this alert not only for 1105 but for all errors that come under 17, create the alert for severity 17. Here is the way of creating an alert for 1105;
























Once the alert is created, an action has to be added for responding to the event. There are two types of actions that can be configured; Execute a Job, Notify Operators. Since the post is speaking about notifications, let's see how to configure the action for notifying a person.

SQL Server Operators
The easiest way of notifying a person is, setting it up via an operator. The SQL Server Agent Operator is a person or group that can receive notifications from an alert or job. Let's create an operator.























As you see, "Database Administrator" operator has been configured with "Email name" notification option. Note that, in order to send emails by SQL Server, Database Mail has to be configured. Let's go back to the Alert and configure the "Response".



























We need to do one more configuration for receiving alerts. You need to enable "Enable mail profile" of "Alert System" that comes under SQL Server Agent properties.




























All okay now. Let's test and see whether administrator gets the error via an email. Below code creates a database with 4mb size data file and disables "File Growth" of it. Next it inserts bunch of records until the data file gets fully filled. Once the data file is full, SQL Server throws an error with 1105.

-- Create new DB called 'AlertTestDB"
USE master;
GO
CREATE DATABASE [AlertTestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AlertTestDB'
 , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AlertTestDB.mdf' 
 , SIZE = 4096KB 
 , MAXSIZE = UNLIMITED
 , FILEGROWTH = 0)
 LOG ON 
( NAME = N'AlertTestDB_log'
 , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AlertTestDB_log.ldf' 
 , SIZE = 1024KB 
 , MAXSIZE = 2048GB 
 , FILEGROWTH = 10%)
GO

-- Connnect and create a table
USE AlertTestDB;
GO

CREATE TABLE TestTable
(
 Id int identity(1,1) PRIMARY KEY
 , Column1 char(2000)
);
GO

-- Insert record until Sever throws an error
SET NOCOUNT ON;

WHILE 1=1
BEGIN

 INSERT INTO TestTable (Column1) VALUES ('test data');
END;
GO

Once the code is executed, following error will be thrown













and administrator automatically receives an email like this.

















Monday, October 6, 2014

MDX: Get last 24 hours periods

Date and Time Dimensions Most of analysis and reporting requirements include a need to aggregate values over time period and this leads us to have date and time dimension tables in our data warehouses. Some designers combine attributes related date and time into one dimension table but most recommended implementation is having two tables; one for date and another for time. Most importantly, no foreign key relationship exist between these two tables.

image

Surrogate Key and Granularity
Surrogate key is very common with data warehouse dimension tables (See why we need surrogate keys). However, specifically for Date dimension table, rather than the general integer value with no semantic meaning, it is always better to have a numeric which is concatenation of each date part. For example, the key of 7th of July, 2006 is 20060707. Time dimension can use general integer values for keys as other usual dimensions with one exception which is the starting value. Make sure that the starting value of the key is 0 for time 00:00:00. Here are sample data for both tables;

image

Last 24-hours periods
One of the common reporting requirements related to date and time is, getting values for last 24 hours period.  Although there are many ways of getting the required result, having the above structure for both Date and Time Dimensions definitely offers most easiest ways. Here is one way;
Assume that today is 7th of July 2006 and time is 09:34:45. If I need “Sales Amount” for last 24 hours, all I have to do is, add a slicer that represents last 24 hours.

image

As you see, the slicer is formed with two tuples and each has two coordinates. First tuple represents today’s date for time periods starting with 00:00:00 to 09:34:45. The second tuple represents yesterday with time periods starting with 09:34:46 to 23:59:59. See the result;

image

Now all we have to do is, build the slicer dynamically. Here is the way. Note that I have used Now function for getting current date and time, Cstr function for converting string values to numeric and StrToMember for converting a string to MDX member.

SELECT  {[Measures].[Sales Amount] } ON 0
 , NON EMPTY {([Order Date].[Year - Quarter - Month - Date].[Date], [Time].[Time].[Time]) }  ON 1
FROM 
 (SELECT {(StrToMember( "[Order Date].[Date].&[" + Format(Now(), 'yyyyMMdd')   + "]") 
   , [Time].[Time].&[0]:StrToMember( "[Time].[Time].&[" 
     + Cstr((Format(Now(), 'HH') * 60 * 60 ) + (Format(Now(), 'mm') * 60 ) + (Format(Now(), 'ss') ))
     + "]"))
  , (StrToMember( "[Order Date].[Date].&[" + Format(Now() -1, 'yyyyMMdd')   + "]") 
   ,StrToMember( "[Time].[Time].&["
    + Cstr((Format(Now(), 'HH') * 60 * 60 ) + (Format(Now(), 'mm') * 60 ) + (Format(Now(), 'ss') ) + 1)
    + "]"):[Time].[Time].&[86399])} ON 0
  FROM [Sales]);