Denali CTP3 Now Available

Much anticipated Denali CTP3 is now available for download from Microsoft. This is the third Community Technology Preview of codenamed Denali.

According to Microsoft, SQL Server 2012 codenamed Denali will enable customers to:
“•Deliver required uptime and data protection with AlwaysOn
•Gain breakthrough & predictable performance with Project Apollo
•Help enable security and compliance with new User-defined Roles and Default Schema for Groups
•Enable rapid data discovery for deeper insights across the organization with Project Crescent
•Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
•Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified dev experience across database, BI, and cloud functions.”

Though there is no official yet, it is believe that this version of SQL Server will be named as 2012 not as 2011 as said some time back. Nice way of postponing the release!

SQL Server 2012 Codenamed Denali CTP3 is available for download here.

How long does it take to fix a bug

In the development cycle, it is needless to less we all in some part of our carrier, have injected  bugs in different scale. However, when we were told it is a bug, I am sure most of you try to fix it or prioritize it to fix it.

But can you imagine, if there is a bug in SQL Server for more than 5 years, ranging three SQL Server Products (SQL Server 2005, SQL Server 2008 and R2).

This bug is in CTE.

Now CTE was introduced primarily to support recursive queries.

Let’s say I have a table and data like this.

So I have a hierarchy as following.

Let’s assume that, you need to retrieve, only two levels starting from Richard, here is the query you should use.

so number of levels are handled by , MAXRECURSION = 2 option. In this of course, you will get the result but also and ERROR!!!,

yes, error says,

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

You can say that since it giving the correct result, what is the big deal about this. But the problem here is, since it is a Level 16 error, if you are using this inside of your code, your code will throw an exception.

You have a workaround which I have given in my sample code at the end of this post.

Now this error first appeared in SQL Server 2005 and Microsoft has make sure that they don’t fix this so you can see the same bug in SQL Server 2008 and in R2. I got to know that this was initially reported in 2002 when SQL Server 2005 CTP was released. So it is nine years now.

To confirm the stuff, I checked this in Denali CTP1. Guess what, still this issue in the backlog.

Download sample code from here

Altering a Column Which has a Default Constraint

If you are altering a column with default column, you need to drop it. but the problem will be if you created a default constraint without explicitly specifying  constraint name. Here is a faq from me.

xp_msver

xp_msver is a extended stored procedure, to get some valuable information in SQL Server.

EXEC master.dbo.xp_msver

Will return,

Most important parameters that I can think of is, ProductVersion (not a big deal since you can get this from ServerProperty function), ProcessorCount, Physical Memory.

Next question is, why you would need this?

Let’s say, you need to set the Maximum Degree of the Parallelism (MAXDOP) as a function of number of Processor count. For example, some DBAs want to have MAXDOP to, ProcessorCount – 2.

 

-

Similarly you can set the memory allocation as well.

Download relevant script from here

New Look SQL Server Performance

www.sqlserverperformance.com has got new look. Hope you will like it.

 

Whether to use UNION or OR in SQL Server Queries

I got this idea of writing this article while reading the IBM Database a.k.a. DB2 magazine.

Capturing Time-Out Procedures using Extended Events

Due to various reasons, your procedures will fail after time out period elapsed.

 

But with the above generic error message, it is difficult to find out which procedure was failed.

Obvious way to find out timing out procedures are to debug your code. However, sometimes you might not be able to create this issue in your environments, since time-out error might depend on the environment setting or environment data.

So let us create a procedure which will time out.

Since this proc is having a delay for 20 hrs, it will obviously will fail with time out.

Let us call this procedure from C# application.

Though profiler can be used to capture this, running profiler may decrease your system performance. Microsoft introduced Extended Events to SQL Server, calming it is a light weight monitoring

In extended event, there is an event type called Event pairing, shows incomplete events. There are some events which are pairs such as lock acquired and lock released and from this event type we can capture incomplete events.

In our scenario, paired events are sp_statement_starting and sp_statement_completed and in case of a timing out, sp_statement_Completed event will not be completed.

You can create a extended event like following/

 

Then we can capture the event by running following query.

 

This result will give you

 

This means, though there are 11127 matched events, there is one orphan event which is what we are after.

If you expand the XML, it will give you the database id and the object id from which can find the culprit.

Interesting thing here is, sql_text is shown as Unable to retrieve SQL text, but if you execute this proc in SSMS, you will see the relevant proc in this XML it self.

How To Change the Default Backup Path in SQL Server

Do you know how to change the default backup folder in SQL Server version. Here is my latest FAQ.

Using SQL Server Resource Governor to Control Resource Utilization

This article introduces SQL Server Resource Governor and provides a detailed walkthrough of using Resource Governor to manage the resource utilization of SQL Server.

Removing History of Database Mail Log

You might know that it is possible to remove history of an Agent Job history.

 

 

If you right click the Agent properties and select History. There you have a configuration to limit you job history.

If you go to the Database mail history, you will see that above setting does not delete the database mail log.

So question is how we can delete the database mail log so that it can be maintained.

well, there is no user interface for that.   However, in SQL Server there is a in built procedure in MSDB database called sysmail_delete_log_sp

This proc has two default parameters, @logged_before (datetime)  and @event_type (varchar(15)) and both has default values of null.

so, if you simply execute sysmail_delete_log_sp it will simply delete entire log.

if you pass the date for the @logged_before  , your database mail log data before that will be deleted.

sysmail_delete_log_sp @logged_before = '2010-12-31' 

Above will delete all the log data before 2010-12-31.  

If you want to delete data until up to last seven days, you can execute following.

DECLARE @dt DATE =  DATEADD(d,-7,GETDATE())

EXEC sysmail_delete_log_sp  @logged_before = @dt
 

Also, you can delete database mail log based on the event type as well. You can pass Success, Information or Warning. 

if anything else other than NULL is passed it will be treated as Error.