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.

Import Data Using SSIS from an Excel Workbook which has Dynamic Sheets Using SSIS

Importing data from excel sheets is not difficult, but what is the sheet names are dynamic. here is a faq written by me for this.

Default Database for Login

Creating logins is not a rocket science in SQL Server. When creating a user, you might have seen an option where you can select default database.

 

So here you are assigning SampleDB to the user sql_user1

 

By assigning this, when use is logged in, he will be taken to SampleDB so that he he doesn’t have to change the database.

But what if this database is dropped later or permission for the user for this database is revoked.

So you have problems of logging to the SQL Server and you can change the default database to got away with this error.

In the login page, you can change the connection properties to connect to any database.

So what is the best database you should select as default database.

I will go for the tempDB for few reasons.

• All users has access to tempdb, so that logins will not failed.
• If default database is master, there is a change that mistakenly you will create objects like tables etc in that and most of the time you won’t be dropping them.  In case, those objects are created in Tempdb, they will be dropped when the SQL Server restarts again!

Adding a Column to SQL Server table

If you are DBA, I am sure you have added columns to tables 1000+ times. But, you know when you are adding a column to a large table, you need extra care. Read the new article on this subject.

Another reason why you should not trust UIs

 

SQL Server Management Studio (SSMS) is nice tool, but there are instances where it will mislead you. This is one of them I found.

Every DBA knows that @@SPID will returns the session ID of the current user process.

You can view the SPID by running following query.

SELECT @@SPID

Apart from this you can use the SSMS to view the SPID as well. If you look down the tool bar of the query window of the SSMS as shown below.

Now, theoretically @@SPID and above value should be same, which you can see from image below

Let me restart the SQL Server instance and let us check what will happen to the above query now.

Now then, it is a contradiction. Obviously, @@SPID cannot be wrong and it is the SSMS the culprit, where SSMS won’t refresh.

So the conclusion, don’t trust SSMS too much.

Extended Events – Speaking engagement in SS SLUG

The February meeting of SQL Server Sri Lanka User Group meeting was held on last Wednesday and sessions were done by Dinesh Priyankara and me. My session was on Extended Events.

Main aim was this presentation is to make awareness among the SQL Server community about the extended events. It was level 100 session.

The presentation and sample scripts are available for downloading:

Data Type for SET ROWCOUNT

You might know SET ROWCOUNT is used to return exact number of records from a table.

You can set the row count,
SET ROWCOUNT 50

When you execute following statment,
SELECT * FROM dbo.RowCountTable

you will get only 50 records regardless of number of rows you have.

But what if you execute a query like that.

SET ROWCOUNT 2147500000

When you execute this, you will get following error.

Msg 1080, Level 15, State 1, Line 1
The integer value 2147500000 is out of range.


Mind you, maximum value for interger, 2,147,483,647 since 2147500000 is more than the maximum of the integer and row count is expecting a value of a interger.

So when you are passing a value to a ROW COUNT, it has to be a integer value.

Do not use something like below,

DECLARE @rccount bigint
SET ROWCOUNT @rccount

Though there won't be any issues, if you pass values less than maximum of integer, but make sure those parameters are integer.