Jun 30, 2014

Sql Server track or know all queries which executes and throw syntax error

In this post we are going to discuss how to track or get the list of all sql queries which executes on sql server but has some error may be syntax error or execution error.

To track the sql error we are going to create an event session. 

Step 1: Create an event session by executing following script:

CREATE EVENT SESSION [Track Sql Errors] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(
  sqlserver.client_app_name,
  sqlserver.client_hostname,
  sqlserver.database_name,
  sqlserver.sql_text,
  sqlserver.username
 )
    WHERE (
  [package0].[greater_than_equal_int64]([severity],(15))
  --AND [sqlserver].[database_name]= N'master'
  --AND [sqlserver].[is_system]= 0
  --AND [sqlserver].[client_app_name]<> N'Microsoft SQL Server Management Studio - Query'
 )
)

Note: You can modify your file conditions according to your requirement. 

[sqlserver].[database_name]: To tack only specified database. To allow in multiple database use IN clause or remove filter condition.

[sqlserver].[is_system] = 0 : To not track the sql errors due to system events.

[sqlserver].[client_app_name] <> N'Microsoft SQL Server Management Studio - Query': To no track sql errors which has generated from SSMS
   
Step 2: Start the event session [Track Sql Errors]

ALTER EVENT SESSION [Track Sql Errors]
ON SERVER
STATE = start

Step 3:  To get the tracking data execute following script:

DECLARE @EventXML AS XML

SELECT
      @EventXML = CAST(target_data AS XML)
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON st.event_session_address = s.[address]
WHERE s.name = 'Track Sql Errors'

SELECT
       T.c.value('(./@timestamp)[1]','datetime') AS [Execution Time],
    T.c.value('(data/value)[1]','int') AS [Error Number],
    T.c.value('(data/value)[2]','int') AS [Error Severity],
    T.c.value('(data/value)[8]','varchar(max)') AS [Error Message],
    T.c.value('(action/value)[1]','varchar(200)') AS [Executed By],
    T.c.value('(action/value)[2]','varchar(max)') AS [Query],
    T.c.value('(action/value)[3]','varchar(100)') AS [Database Name],
       T.c.value('(action/value)[4]','varchar(max)') AS [Host Name],
       T.c.value('(action/value)[5]','varchar(max)') AS [Program Name]
    
FROM @EventXML.nodes('RingBufferTarget/event') T(c)

Note: Data of event session will be updated after some regular intervals, so to get new data, execute the above script again and again.

Step 4: To drop the above event session execute following script:

DROP EVENT SESSION [Track Sql Errors] ON SERVER

Increase or decrease font size in the query execution plan in Sql server Management Studio (SSMS)

Sometimes we need to increase or decrease font size of text in the query execution for presentation etc purposes in sql server management studio (SSMS) 2014, 2012, 2008 and 2005. We can change it permanently from settings:

How to increase or decrease font size in the query execution plan in Sql server management studio (SSMS):

Step 1: Go to Tools -> Options as shown in following diagram:
 
SSMS Setting Options
Step 2: Go to Environment -> Fonts and Colors

EXECUTION Plan Font Size Change Setting SSMS

Step 3:
a. In Show Settings for drop down choose Execution Plan.
b. In the display items drop down choose Text
c.  In the size drop down choose any size which you want to set.
d.  After setting click on OK button.

Step 4: A warning message will pop up:

Execution Plan Font Size change wraning Message
Click on Ok button.

Step 5: Restart your sql server. If you don't know how to restart sql server go through this link:


Re-generate the graphical execution plan. After this your font size in the graphical query execution plan will increase or decrease permanently:

SSMS Execution Plan With Larger Font Size

Restart Sql Server Using Sql Server Management studio (SSMS)

In this post we will discuss how to restart sql server by using Sql Server Management studio (SSMS) 2014, 2012, 2008 and 2005. To restart it, follow following steps:

Sept 1: Right click on sql server instance and choose restart option as shown in the following screenshot:

SQL SERVER Restart USING SSMS
Steps 2: After few seconds a confirmation dialog box will appear up. Click on Yes button.

Sql Server Restart Confirmation Dialog
Step 3: Sql Server restart will be in process:
 
SSMS Qql Server Restart Processing
It may take around one minute. After some completion of process bar server will be start once again.


Note: Don't click on Close button. 

Jun 29, 2014

Change the font size or foreground background color of line numbers in sql editor in sql server management studio

Sometimes we need to change font size or color of line numbers in sql server management studio 2014, 2012, 2008 and 2005. To set this

Step 1: Go to Tools -> Options as shown in following diagram:

SSMS Setting Options
Step 2: Go to Environment -> Fonts and Colors

Font Size Background color of Line number SSMS
Step 3:

a. In Show Settings for drop down choose Text editor.
b. In the display items drop down choose Line Numbers
c. In the size drop down choose any size which you want to set.

d. After setting click on OK button.

Show line numbers in transact sql in Sql server management studio (SSMS)

Sql server management studio supports many types of editors like transact sql query editor, XML Editor, Text Editor Etc. Sometimes we need to display line number in different editors. Here we will set to show line numbers in different editor in sql server management studio 2014, 2012, 2008 and 2005.

Step 1: Go to Tools -> Options as shown in following diagram:

SSMS Setting Options
Step 2:

To Show line numbers in all languages text editor

Got to: Text Editor -> All Languages - > General

SSMS All Languages Show Line Number
Checked Line Numbers check box and click on OK button.

To Show line numbers in only in transact sql editor

Go to: Text Editor -> Transact-Sql - > General

SSMS Transact Sql Editor Show Line Number
Checked Line Numbers check box and click on OK button.

To Show line numbers in only in XML editor

Go to: Text Editor -> XML - > General
 
SSMS XML Editor Show Line Number
Checked Line Numbers check box and click on OK button.


To Show line numbers in only in plain text editor

Go to: Text Editor -> Plain Text - > General

SSMS Plain Text Show Line Number
Checked Line Numbers check box and click on OK button.

Change font style and font weight of query text in Sql server Management Studio (SSMS)

Sometimes we need to change font style and font weight of transact sql queries from default setting in sql server management studio (SSMS) 2014, 2012, 2008 and 2005. We can change it permanently from settings:

How to Change font style and font weight in Sql server management studio (SSMS):

Step 1: Go to Tools -> Options as shown in following diagram:

SSMS Setting Options

Step 2: Go to Environment -> Fonts and Colors

SSMS Font Weight and Font Style Setting Text editor
Step 3:
a. In Show Settings for drop down choose Text editor.
b. In the display items drop down choose Plain Text
c.  In the font drop down choose any font style which you want to set.
d.  Checked the check box bold to set the bold character.  

e.  After setting click on OK button. 

Change background color of query text editor in Sql server Management Studio (SSMS)

Sometimes we need to change background color from white to some other color like black of query text editor in sql server management studio (SSMS) 2014, 2012, 2008 and 2005. We can change it permanently from settings:

How to change background color of query text editor in Sql server management studio (SSMS):

Step 1: Go to Tools -> Options as shown in following diagram:

SSMS Setting Options
Step 2: Go to Environment -> Fonts and Colors

Setting Background Color SSMS
Step 3:

a. In Show Settings for drop down choose Text editor.
b. In the display items drop down choose Plain Text
c.  In the item background drop down choose any color which you want to set.
d.  After setting click on OK button.

After this background color of sql queries text editor will change permanently.  

Changed Background Color SSMS

Zoom font size query text editor Sql Server Management studio

We can easily zoom the font size of text or sql queries in sql server management studio 2014, 2012, 2008 and 2005 (SSMS). There is drop down at bottom of query editor where we can change zoom level as show in following screenshot:


Increase or decrease font size of query text in Sql server Management Studio (SSMS)

Sometimes we need to increase or decrease font size of Transact sql queries for presentation etc purposes in sql server management studio (SSMS) 2014, 2012, 2008 and 2005. We can change it permanently from settings:

How to increase or decrease font size in Sql server management studio (SSMS):

Step 1: Go to Tools -> Options as shown in following diagram:

SSMS Setting Options
Step 2: Go to Environment -> Fonts and Colors

Increase or drcrease Font Size SSMS setting
Step 3:
a. In Show Settings for drop down choose Text editor.
b. In the display items drop down choose Plain Text
c.  In the size drop down choose any size which you want to set.
d.  After setting click on OK button.


After this your font or text size will increase or decrease permanently.