Home » 2012 » November (Page 3)

Monthly Archives: November 2012

How to save deadlock graph events as .xdl file in SQL Server ?

How to save deadlock graph events as .xdl file ?

  1. On the File menu, click New Trace, and then connect to an instance of SQL Server.
  2. In the Trace Properties dialog box, type a name for the trace in the Trace name box.
  3. In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.
  4. Do one of the following:

· Select the Save to file check box to capture the trace to a file. Specify a value for Set maximum file size.

Optionally, select Enable file rollover and Server processes trace data.

· Select the Save to table check box to capture the trace to a database table.

Optionally, click Set maximum rows, and specify a value.

  1. Optionally, select the Enable trace stop time check box, and specify a stop date and time.

  1. Click the Events Selection tab.
  2. In the Events data column, expand the Locks event category, and then select the Deadlock graph check box. If the Locks event category is not available, check Show all events to display it.

The Events Extraction Settings tab is added to the Trace Properties dialog box.

  1. On the Events Extraction Setting stab, click Save Deadlock XML Events Separately.
  2. In the Save As dialog box, enter the name of the file in which to store the deadlock graph events.
  3. Click All Deadlock XML batches in a single file to save all deadlock graph events in a single XML file, or click Each Deadlock XML batch in a distinct file to create a new XML file for each deadlock graph.

11. Deadlock graph captured by SQL Profiler

SQL Server – List of all the Error codes or messages

Question:

I want list of all error codes & messages of SQL server.

Answer:

Once some ask this thing to you or this though comes to your mind, I am sure most of us will start goggling or looking into BOL for the details

But SQL server also Contains one row for each system error or warning that can be returned by Microsoft SQL Server.

You can get the List of all the Error codes or messages as

In SQL Server 2000

USE MASTER

GO

SELECT * FROM SYSMESSAGES

Column name Description
error Unique error number.
severity Severity level of the error.
dlevel For internal use only.
description Explanation of the error with placeholders for parameters.
mslangid System message group ID.

In SQL Server 2005 & above

USE MASTER

GO

SELECT * FROM SYS.MESSAGES

Column name Description
message_id ID of the message. Is unique across server. Message IDs less than 50000 are system messages.
language_id Language ID for which the text in text is used, as defined in syslanguages. This is unique for a specified message_id.
severity Severity level of the message, between 1 and 25. This is the same for all message languages within a message_id.
is_event_logged 1 = Message is event-logged when an error is raised. This is the same for all message languages within a message_id.
text Text of the message used when the corresponding language_id is active.

Come Back From vacations…..

Hey Friends

I missed you so much in my vacations.

I come back from my leaves & hope now we have regular interactions.

%d bloggers like this: