aiDeeva.com

Category: Archive

Engineering notes from 2008–2014: SQL Server scripts, DBA quick-tips, and field notes from the trenches. Preserved as written — the foundation everything since was built on.

  • Online Discussion

  • How to Capture DeadLock Graph Using SQL Profiler

    You can follow below steps to capture the deadlock graph using profiler. First we will setup the profiler and deadlock events and later on we will run the deadlock scenario.

    Step 1: Open the SQL Profiler. You can start the SQL Profiler from the SSMS.

     

    Step 2: Configure the trace, in General tab give the name to trace file.

    Step 3: Select the below events from the Event Selection tab and Run the trace.

    Deadlock Graph

    Deadlock Graph event captures deadlock in both XML format and graphically, a graph that shows us exactly the cause of the deadlock.

    Lock:Deadlock

    This event is fired whenever a deadlock occurs.

    Lock:Deadlock Chain

    This event is fired once for every process involved in a deadlock.

    Step 4: Run the deadlock scenario queries as per http://sqldbpool.com/2012/02/12/steps-to-create-the-deadlock-scenario/ article.

    Step 5: You can see the below graph once the deadlock occurred.

     

     

  • How to insert value into IDENTITY column?

    If you will try to insert the value into Identity column you will get the one of the below error.

    Error 1:
    Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table ‘Employee’ when IDENTITY_INSERT is set to OFF.

    Error 2:
    Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

    Solution:
    Write SET IDENTITY_INSERT table name ON before the insert script and SET IDENTITY_INSERT table name Off after insert script.

    Example,

    use db1

create table Employee
(
	myID int identity(100,1),
	name varchar(20)
)

insert into Employee(name) values('Jugal')

--if i will try to insert the value into Identity column it will fail
insert into Employee(myID,name) values (101,'DJ')

--you can add the data into identiy column by turning on the IDENTITY_INSERT ON

SET IDENTITY_INSERT Employee ON
	insert into Employee(myID,name) values (101,'DJ')
SET IDENTITY_INSERT Employee OFF

  • Happy New Year 2012

    Dear Readers,

    Wish you all very happy and prosperous New Year 2012.

    Thanks,
    Jugal Shah

  • SQLDBPool Forum

    Dear Readers,

    Now you can post your question/queries related to SQL into SQLDBPool forum. Please register your account with the SQLDBPool forum.

    Forum URL
    http://sqldbpool.forumotion.in/

    Thanks,
    Jugal Shah