Wednesday, 25 November 2015

Analyzing SQL Server Error Logs / Agent Logs using T-SQL

Even though you design our SQL Scripts with the best methods using best practices, or configure the SQL Server to perform correctly and in the optimized manner, you cannot prevent things going wrong. Luckily SQL Server does a great job on logging all the issues which we will be encountering during the course. Things could have been worse if you need to go through the error log file using only a text editor like the ‘Note Pad’ application (Favorite text editor of majority people). But fortunately SQL Server had provided us some help when you need to dig deep into Error Log.


But things could get more complicated if the Error Log contains lots of records and in those records if you require to swim for the issue which you are looking for.


Even though it provides you some searching and filtering capabilities, it could still be very challenging and time consuming.



However we do have another workaround which might come in handy. That’s to query the Error Logs using T-SQL. This can be done using the system procedure ‘sys.sp_readerrorlog’. This consists with few parameters.
USE [master]
/****** Object:  StoredProcedure [sys].[sp_readerrorlog]    Script Date: 24/11/2015 7:11:36 PM ******/
ALTER proc [sys].[sp_readerrorlog](
    @p1        int = 0,
    @p2        int = NULL,
    @p3        nvarchar(4000) = NULL,
    @p4        nvarchar(4000) = NULL)

    if (not is_srvrolemember(N'securityadmin') = 1)
       raiserror(15003,-1,-1, N'securityadmin')
       return (1)
    if (@p2 is NULL)
        exec sys.xp_readerrorlog @p1
        exec sys.xp_readerrorlog @p1,@p2,@p3,@p4   

  1. @p1 –> This represents the error log which you need to inspect (0 ~ Current | 1 ~ Archive #1 etc..)
  2. @p2 –> Type of the error log which you want to inspect (NULL or 1 ~ Error Log | 2 ~ SQL Agent Log)
  3. @p3 –> 1st Search Parameter (A value which you want to search the contents for)
  4. @p4 –> 2nd Search Parameter (A value which you want to search to further refine the result set)

**Please note: Aforementioned parameters are optional. Therefore if you don’t provide any parameters, it will return the whole contents of the current/active Error Log.

Few Examples

1. This will return all entries in the current Error Log
EXEC sys.xp_readerrorlog @p1 = 0

2. This will return all the entries in the current SQL Agent Log
EXEC sys.xp_readerrorlog @p1 = 0, @p2 = 2

3. This will return all the entries in the current SQL Error log where ever the value ‘CLR’ consist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR'


4. This will return the entries in the current SQL Error log when the value ‘CLR’ and ‘Framework’ exist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR', @p4='Framework'


When we execute the stored procedure ‘sys.sp_readerrorlog’, inside it will call an extended stored procedure which will accept 7 parameters, which is  ‘sys.xp_readerrorlog’. The parameter details are as follows:

Param #ParameterDetails
1Log Number0 – Current / 1 – Archive #1 / 2 – Archive #2 etc…
2Log Type1 – SQL Error Log / 2 – SQL Agent Log
3Search Text 1Search term which will be searched on the Text column
4Search Text 2Search term which will be searched on the Text column. **If both search texts are supplied it will return rows containing both texts.
5Start DateLog entries which the ‘Log Date’ is newer than the date provided. (including the date provided)
6End DateLog entries which is between the Start Date and End Date
7Sort OrderASC – Ascending / DESC - Descending


EXEC sys.xp_readerrorlog 0,1,N'',N'', '20151124','20151125','DESC'    

I hope this information will help you when you need to query the Error Log in order to troubleshoot an issue.

Sunday, 22 November 2015

Enabling Instant File Initialization in SQL Server

Technorati Tags:

Every time the SQL data file or log file expands, it fills the newly allocated (expanded) space with zero. There are few good and bad having this feature (Zeroing the allocated space). One downside of this is, this process will block all the sessions which are writing to these files (data and log files), during this initialization period. One might debate this time period will be very small, but it could be an extremely critical one for some process.

However enabling the Instant File Initialization behavior will make sure that the aforementioned issue will not have any effects when SQL Data file is expanded. But there will be a security risk enabling this feature. When this is enabled, it could be a possibility that unallocated part of the SQL data file could contain information related to previously deleted files (OS related information). There are tools which can examine this data and people who will be having access to the data file (most probably DB Administrators) can easily see the underlying data of these unallocated areas.

Before enabling this we will check how SQL will behave with this option disabled:

DBCC TRACEON(3004,3605,-1)


EXEC sys.sp_readerrorlog

DROP DATABASE Sample_Database

DBCC TRACEOFF(3004,3605,-1)



You can clearly see that SQL had an operation to zero out both data and the log file. (I have only highlighted the data file [.mdf])

Enabling Instant File Initialization can be done by adding a ‘SA_MANAGE_VOLUME_NAME’ permission (also know as ‘Perform Volume Maintenance Task’) to the SQL Server Startup account. This can be done as follows:

Open the Local Security Policy Management Application. (execute secpol.msc from the run command or from command line). Double click on the ‘Perform Volume Maintenance Tasks’ which can be found In Security Settings –> Local Policies –> User Rights Assignment.


And add the SQL Server start up account to the list.



SQL Server startup account can be found in ‘Log On’ Tab in ‘SQL Server <Instance>’ in the Services.



*** Please note: SQL will check whether this feature is enabled or disable during start up. Therefore we need to restart the service once it’s enabled or disabled. Also we can only enable this for SQL data files only. We cannot enable this for log files.

After restarting the SQL Service we will execute the code snippet which we executed earlier. You will be able to see that SQL is only zeroing the log file now.


Thursday, 19 November 2015

Capturing and Analyzing deadlocks in SQL Server

No matter how perfect your code and how optimized the way you have written is, facing a situation of getting a deadlock is inevitable. And it would make the situation even more complex when you know that a deadlock had occurred but you have no clue which the victim and perpetrator was.

There are few ways to capture and analyze a deadlock. I will explain two ways in this post.

  1. Enabling trace flags to capture/log deadlock information into SQL Log
  2. Use SQL Profiler to capture a deadlock.


Enabling trace flags to capture/log deadlock information into SQL Log

This method will make sure that the deadlock details are logged in the SQL log file (not the transaction log). Even though the details which are captured via this method is textual  it gives you the option of inspecting the details at a later time.

First you need to enable the trace flags using the following syntax:

DBCC TRACEON (1222, 1204, -1)


Afterwards we will simulate a deadlock : (in reality you don’t need to do this if you are facing such issue)

First create two sample tables and insert few records which we will use to produce a deadlock.

-- Create a Sample Tables --

CREATE TABLE SampleDeadLock_1(
Item_Code INT
,Item_Desc VARCHAR(100)
,Qty INT

CREATE TABLE SampleDeadLock_2(
Item_Code INT
,Item_Desc VARCHAR(100)
,Qty INT

-- Insert couple of records to each table --

INSERT INTO dbo.SampleDeadLock_1(
(1,'CPU', 10)
,(2, '20 GB - Hard Disk', 20)

INSERT INTO dbo.SampleDeadLock_2(
(3,'Monitor', 15)
,(4, 'Keyboard & MOuse', 25)


Now open two query windows in SQL Server Management Studio (Window 1 & Window 2) and paste the following code to ‘Window 1’


UPDATE dbo.SampleDeadLock_1 SET Qty = 100 WHERE Item_Code = 1

WAITFOR DELAY '00:00:30'

UPDATE dbo.SampleDeadLock_2 SET Qty = 200 WHERE Item_Code = 3

And paste the following code to ‘Window 2’


WAITFOR DELAY '00:00:10'

UPDATE dbo.SampleDeadLock_2 SET Qty = 300 WHERE Item_Code = 3
UPDATE dbo.SampleDeadLock_1 SET Qty = 400 WHERE Item_Code = 1


Afterwards execute the code in Window 1 and immediately execute the code in Window 2. After few seconds (30+) you will see a deadlock error message in Window 1.


And when you checked on the ERRORLOG file, you will be able to find out the relevant details which relates to the deadlock. (I have highlighted the deadlock victim details in Red and the perpetrator details in blue)



Using SQL Profiler to capture a deadlock (Deadlock Graph)

One of the issues we have in the aforementioned method is that we need to look into lots of textual information in order to extract the details which is relevant for the deadlock. In this example it’s somewhat easier since we had chosen a simple situation. But things could get real hard during an actual situation where the processes are bit complex.

In such case (any case), we can use the SQL Profiler to detect the deadlock in a more user friendly manner.

** Please Note: Down-side of this method is you need to have the profiler running during the time that the deadlock had occurred. And this isn’t the best way if you need to troubleshoot in a Production Environment.

How ever for the sake of understanding we will see how we can achieve this.

Open the SQL Profiler and connect to the relevant SQL Server. And in the ‘Trace Properties’ window choose ‘TSQL_Locks’ as the template.


And in the ‘Event Selection’ tab, please choose the following options.

  • Deadlock graph
  • Lock:Deadlock
  • Lock:Deadlock Chain


Before click on running the profiler, Rollback the transaction which is running from the first example (The one which didn’t become the Deadlock Victim)

Start the profiler by clicking the ‘Run’ button. And once the profiler is running, execute the code on Query Window 1 and afterwards execute the code in Query Window 2.

Once the deadlock occurs the Profiler will display the relevant information in a graphical manner. This will contain all the relevant details which is required in order to troubleshoot the deadlock. And if you hover the cursor over the processes in the graph (shown in ellipses) a tooltip will be displayed along with the statement which has been executed, which caused the deadlock to occur.


I hope this will help you in order to troubleshoot a deadlock situation.

Monday, 26 October 2015

Elevate permission on a SQL login having read only access to Execute Stored Procedures and to View Database Schemas

Due to an unavoidable situation, it was decided that all the write access to a particular server (SQL Server) was required to be removed immediately. And only few people should be able to access the server with full privileges. How ever this wasn’t sound very friendly to the developers, since they were using this server for various kinds of activities during their development and testing. Once the write access was removed we ran into various kinds of trouble since majority couldn’t view the contents of the database objects such as Stored Procedures, Views etc.

So a requirement came to allow them to access the schemas so that they can view the contents, also to execute the procedures (required when debugging a flow using Profiler) without allowing anyone to change or add any new database objects nor any data updates to existing tables directly.

The easiest workaround was to create a SQL user login with read permission and grant execution access to that login.

1.  Create a SQL Login.



2. Only keep the ‘public’ server role selected for this SQL Login.



2. Select ‘db_datareader’ role membership.



Afterwards if you connect to the SQL Server using the above created login, you can see that only read only operations are allowed. You will be able to see the tables, views (cannot see the underlying SQL Code). Stored procedures will be hidden





I have created the following stored procedure in my sample database (AdventureWorks) for testing purpose.

    SELECT GETDATE() AS CurrentDate

And when the aforementioned procedure is executed as the ‘readonlyuser’ we will get the following error:

EXEC ReadOnlyUserProc


Now lets just provide the necessary access to this login so that it can be use to execute the procedures and to see the schemas of database objects. Use the following code to grant the execution access to the previously created user.

USE AdventureWorks
GRANT EXECUTE TO readonlyuser


And now when we execute the stored procedure we will get the desired result.

EXEC ReadOnlyUserProc


However still we are unable to see the schema of the SQL Objects, which is a part of what we want in this exercise.


If you try to generate the script by right clicking the object you will get an error message:


We will try to fix that issue as well. Please follow these steps:

1. Right click the login and go to the properties window.

2. Go to the ‘Securables’ tab and check/tick the ‘Grant’ check box for ‘View any definition’.

3. Click ‘OK’


Now you can see the definitions of the database objects.




Hope this might be useful to you as well.

Saturday, 17 October 2015

Elevate permission on a SQL login having read access to run the SQL Profiler

Sometime it’s necessary to allow SQL logins only to have read access to the respective SQL Servers. But it becomes a necessity to allow the developers to run the SQL Profiler when it comes to troubleshoot or to debug a flow and to find the exact point where the problem is generating. How ever it’s a very common opinion that a login having a read only access cannot run the profiler unless it’s included into the ‘sysadmin’ SQL role.
Well this isn’t entirely true. We could allow a SQL login to start and monitor SQL activity using the SQL Profiler and yet keeping the read only access to the SQL schemas. Follow these steps to achieve this:
1. Connect to the SQL Server and expand the Security->Logins node


2. Right click the desired login and go to ‘Properties’ from the context menu


3. Go to the ‘Securables’ tab and check/tick the Grant checkbox on ‘Alter Trace’. And click ok to save the details.


Now you can run the profiler and monitor details using that SQL Login.



Wednesday, 3 June 2015

Error occurs when CTE Alias is ‘RESULT’ - Incorrect syntax near 'RESULT'

Few days back one of my colleague ran into a very strange issue when he was asked to do an alteration to an existing procedure. The procedure was having a SELECT statement which included few Common Table Expressions. It has been running without an issue, until we tried to save the alteration. The alteration was a pretty simple one which was just to add couple of more columns. The query was similar to the one shown below

    /* Your logic  */

How ever when we tried to save the changes, it was throwing the following error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RESULT'.

But when we go through the query we couldn’t find any issues, and it seems the syntax was quite accurate. So we tried a different server and was able to save the changes without any error. The only difference was one server was running SQL Server 2012 which is having a higher build

Works fine on the following build

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Throws an error on the following build

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

And when googled we could find that ‘RESULT’ is a future reserved keyword:


But we were still left out with a question of why it failed on an earlier build but succeeded on a most recent build. Please feel free to comment on this if you have further information.

Wednesday, 20 May 2015

Conflict in log4net on GAC when a Crystal Report is viewed through .Net Code

We faced an issue sometime ago, which put us into a very tight situation. The issue was when a Crystal Report file is loaded through .Net code it threw the following error. This is the first of a series of error which lead to another and so forth.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> System.TypeInitializationException: The type initializer for 'CrystalDecisions.ReportSource.ReportSourceFactory' threw an exception.
---> System.TypeInitializationException: The type initializer for 'CrystalDecisions.Shared.SharedUtils' threw an exception.
---> System.IO.FileLoadException: Could not load file or assembly 'log4net, Version=, Culture=neutral,
PublicKeyToken=692fbea5521e1304' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)
File name: 'log4net, Version=, Culture=neutral, PublicKeyToken=692fbea5521e1304'
   at CrystalDecisions.Shared.SharedUtils..cctor()


And the confusion grew even further when we checked the GAC (Global Assembly Cache). Because we could see that in GAC, the mentioned assembly (log4net) is being registered with the version and with the correct ‘PublicKeyToken’ (692fbea5521e1304).


And we didn’t really worry about the ‘Processor Architecture’ column, since our development environment is 64-bit and all the Crystal Reports development pre-requisites were there in the development environment and Crystal Runtime was already installed (version 13 – 64-bit). And in our application code we were using the same log4net and it was already presented in the bin folder.

Then when we googled the issue we could be able to come across with similar incidents, where the majority of the people were able to resolve the issue by installing the Crystal Runtime. But it was sort of a puzzle to us since we already had it installed it. But later we tried by un-installing the runtime and re-installing it again, assuming some corruption might had occur during the previous installation or afterwards. But the result was same.

Then we came across with the following article which gave us some kind of a hint of the issue we are facing:

And it occurred to us that even though we had the same assembly which the error is pointing installed in GAC, still the Crystal assemblies were expecting some other log4net assembly to be presented in GAC.

Therefore we installed the Crystal Runtime version 13 for 32-bit. Afterwards the GAC was having both the assemblies registered.


And this resolved the issue. And we came to a conclusion that even our development environment is 64-Bit, we need to have the log4net assembly for 32-bit since crystal is referring to that internally.

Hope this would help you if you are facing the same problem.