Receive Deadlock detail via email.

I had spent many hours on Google trying to find the best way to send a notification on deadlock occurrences. While it was a fairly straight forward process on how we get notified a deadlock has occurred, I wanted a bit more. I not only wanted to be told when the Deadlock occurred, I wanted to also be emailed the actual Deadlock information.

Every time a deadlock occurs in SQL Server, the detailed info about things like the SPID, the statement that was running, who the victim was, and so on does get logged. But getting the generic alert that SQL Server can create simply will tell you “something” has occurred. It would be the dutiful DBA’s job to log into the server in question and dig into the Error Log to get the Deadlock details.

Since capturing Deadlock info is not turned on by default. We do have to do two things in order to make the scripts I have written work properly.

Requirement number one

The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.

• 3605 = write what we want to the error log.
• 1204 = Capture Deadlock Events.
• 1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Requirement number two

The second requirement is to ensure you have DB Mail setup and working. I will provide no detail on how to accomplish that. It will just be assumed that you have a working DB Mail profile.

Lets get started.

So now we have trace flags set and DB Mail is working we are ready to get into how we send the Deadlock information to an email address when a Deadlock occurs.

Since the structure of the error log changed in SQL 2005, we have two ways of doing this. Each method is basically the same, however, pay attention to where you deploy this script. I have included detailed comments so you can follow along.

For SQL Server 2000

--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = 'DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @message = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @width = 600,
 @attach_results = 'True',
 @no_header = 'True'
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

And for all other version, (2005, 2008, 2008 R2)

--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients='DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @body = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

Next Steps

In order to get those to work every time SQL Server encounters a Deadlock, we have to create a SQL Server Agent Job and a SQL Server Agent Alert. The basic approach is to create a Job that is called by the Alert service.

Let’s first create a SQL Agent Job. The job will have no schedule. Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N’sa’ to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

SQL Server 2000 Job

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = ''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @message = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @width = 600,
 @attach_results = ''True'',
 @no_header = ''True''
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server 2005 and higher Job

Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N’sa’ to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @body = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Final step

Now we have to create a SQL Agent alert to call the job we created. Open SSMS and log into the instance we are capturing Deadlock information for and expand the SQL Server Agent. Right click on the word Alerts and choose “New Alert…”

On the General page it should look like this;

On the Response page it should look like this:

On the Options page it should look like this:

That’s all there is to it. The next time a Deadlock occurs on the instance you are monitoring you will you receive an email. The email you receive will have an attachment that will actually tell you what the deadlock was.

Most tips works for SSMS higher 2008 but some of them only for SSMS 2016 and above.

Great thanks to:

• Kendra Little
• Slava Murygin
• Mike Milligan
• Kenneth Fisher
• William Durkin
• John Morehouse
• Phil Factor
• Klaus Aschenbrenner
• Latish Sehgal
• Arvind Shyamsundar
• SQLMatters
• MSSQLTips
• Anthony Zanevsky, Andrew Zanevsky and Katrin Zanevsky
• Andy Mallon
• Aaron Bertrand

Import and Export Settings

Tools > Options > Environment > Import and Export Settings

You can configure so many settings in SSMS and then export it and use on all your computers. Below link provide detailed instruction and awesome Dark theme configuration: Making SSMS Pretty: My Dark Theme

Also you can create shared team settings file and use it from network location. Detailed information you can find in this article Symbolic Links for Sharing Template Files or “How I Broke Management Studio with Symbolic Links”

SSMS Shortcuts

All 957 shortcuts you can find here

Most useful are:

Keyboard Shortcuts for Favorite Stored Procedures

Tools > Options > Environment > Keyboard > Query Shortcuts

3 Shortcuts can not be changed: Alt + F1, Ctrl + 1 and Ctrl + 2. For another 9 shortcuts my recommendation awesome open source Brent Ozar teams procedures and with some limitations Adam Machanic sp_WhoIsActive:

Also recommended:

• sp_BlitzRS
• sp_DatabaseRestore
• usp_BulkUpload

Script any object with data

Right click on database name > Tasks > Generate Scripts …

Selecting a block of text using the ALT Key

By holding down the ALT key as you select a block of text you can control the width of the selection region as well as the number of rows. Also you can activate multi line mode with Shift + Alt keys and using keyboard arrows to format multi line code.

Script Table and Column Names by Dragging from Object Explorer

Save keystrokes by dragging Drag the Columns folder for a table in to auto-type all column names in the table in a single line.

• Warning: this doesn’t include [brackets] around the column names, so if your columns contain spaces or special characters at the beginning, this shortcut isn’t for you
• Dragging the table name over will auto-type the schema and table name, with brackets.

Disable Copy of Empty Text

• Select a block of text to copy;
• Move the cursor the place where you want to paste the code;
• Accidentally press Ctrl+C again instead of Ctrl+V;
• Block of copied text is replaced by an empty block;

This behavior can be disabled in SSMS: go to Tools > Options > Text Editor > All Languages > General > 'Apply Cut or Copy Commands to blank lines when there is no selection' and uncheck the checkbox.

Client Statistics

When you enable that option for your session, SQL Server Management Studio will give you more information about the client side processing of your query.

The Network Statistics shows you the following information:

• Number of Server Roundtrips
• TDS Packets sent from Client
• TDS Packets received from Server
• Bytes sent from Client
• Bytes received from Server

The Time Statistics additionally shows you the following information:

• Client Processing Time
• Total Execution Time
• Wait Time on Server Replies

Configure Object Explorer to Script Compression and Partition Schemes for Indexes

Is this index compressed or partitioned?

By default, you wouldn’t know just by scripting out the index from Object Explorer. If you script out indexes this way to check them into source code, or to tweak the definition slightly, this can lead you to make mistakes.

You can make sure you’re aware when indexes have compression or are partitioned by changing your scripting settings:

• Click Tools – > Options -> SQL Server Object Explorer -> Scripting
• Scroll down in the right pane of options and set both of these to True
  • Script Data Compression Options
  • Script Partition Schemes
• Click OK

Using GO X to Execute a Batch or Statement Multiple Times

The GO command marks the end of a batch of statements that should be sent to SQL Server for processing, and then compiled into a single execution plan. By specifying a number after the ‘GO’ the batch can be run specified number of times. This can be useful if, for instance, you want to create test data by running an insert statement a number of times. Note that this is not a Transact SQL statement and will only work in Management Studio (and also SQLCMD or OSQL). For instance the following SQL can be run in SSMS :

CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME)
GO

INSERT INTO TestData(CreatedDate) SELECT GetDate()
GO 10

This will run the insert statement 10 times and therefore insert 10 rows into the TestData table. In this case this is a simpler alternative than creating a cursor or while loop.

SSMS Template Replacement

One under-used feature of Management Studio is the template replacement feature. SSMS comes with a library of templates, but you can also make your own templates for reusable scripts.

In your saved .sql script, just use the magic incantation to denote the parameters for replacement. The format is simple: <label, datatype, default value>

Then, when you open the .sql script, you hit CTRL + Shift + M, and SSMS will give you a pop-up to enter your replacement values.

Color coding of connections

SQL Server Management Studio has the capability of coloring the bar at the bottom of each query window, with the color dependent on which server is connected. This can be useful in order to provide a visual check of the server that a query is to be run against, for instance to color code production instances as red, development as green and amber as test. This can also be used in conjunction with Registered Servers and CMS (Central Management Server). To add a color bar when connecting to the server click on the Options button in the Connect to Database Engine window and then select the Connection Properties window. Select the check box towards the bottom of the window and use the ‘Select…’ button to choose a color.

SQLCMD mode

Switching on SQLCMD mode enables a number of useful extra scripting style commands in SSMS. In particular you can use it to change to the connection credentials within the query window, so that you can run a query against multiple servers from the same query window. There are more details of how to do this here: Changing the SQL Server connection within an SSMS Query Windows using SQLCMD Mode

Script multiple objects using the Object Explorer Details Windows

Individual database objects, such as a table or stored procedure, can be scripted within SSMS by right clicking on the object within Object Explorer and selecting the appropriate item in the drop down menu. However if you have a lot of objects to script that can quickly become time consuming. Fortunately it’s possible to select multiple objects and script them up all together in a single query window. To do this just open the Object Explorer Details window from the View menu (or press the F7 key). If you want to script up multiple (or all) tables, select the Tables item under the relevant database in Object Explorer. A list of all tables appears in the Object Explorer Details window. Select the tables you want to script (using the Control key if necessary) and then right click and select which script option you want – e.g. to create a table create script for all tables

Registered Servers / Central Management Server

If you have a lot of servers then re-entering the details in Object Explorer every time you start SSMS can be frustrating and time consuming. Fortunately there are two facilities within SSMS that enable these details to be entered just once and “remembered” each time you open up SSMS. These two facilities are Registered Servers and Central Management Servers. These were introduced in different versions of SQL Server and work in different ways, each has its own advantages and disadvantages so you may want to use both.

To add a registered server open the Registered Servers window from the View menu (or click CTRL + ALT + G), the window should appear in the top left corner of SSMS. Right click on the Local Server Groups folder and select ‘New Server Registration…’. Enter the server details and close the window. This new server should then appear under Local Server Groups, you can then right click and open up the server in Object Explorer or open a new query window. The server details are stored locally in an XML file and so will appear next time you open SSMS. If you have a lot of servers then you can also create Server Groups to group together similar servers. One advantage of creating groups (other than being able to logically group similar servers together) is that you can run a query against all servers in the group, by right clicking the group and selecting ‘New Group’.

Central Management Server are similar to Registered Servers but with some differences, the main one being that the server details are stored in a database (the Central Management Server) rather than a local file. A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.

Splitting the Query Window

The query window in SSMS can be split into two so that you can look at two parts of the same query simultaneously. Both parts of the split window can be scrolled independently. This is especially useful if you have a large query and want to compare different areas of the same query. To split the window simply drag the bar to the top right hand side of the window as shown below.

The splitter bar allows you to view one session with two panes. You can scroll in each pane independently. You can also edit in both the top and bottom pane.

Moving columns in the results pane

It may not be immediately obvious but you can switch columns around in the results pane when using the grid view, by dragging the column headers and dropping them next to another column header. This can be useful if you want to rearrange how the results are displayed without amending the query, especially if you have a lot of columns in your result set. This works only for one column.

Generating Charts and Drawings in SQL Server Management Studio

You don’t have to settle for T-SQL’s monochrome text output. These stored procedures let you quickly and easily turn your SELECT queries’ output into colorized charts and even computer-generated art. To turn your own data into a line, column, area, or bar chart using the Chart stored procedure, you need to design a SELECT query that serves as the first parameter in the stored procedure call.

Detailed article and code here: Generating Charts and Drawings in SQL Server Management Studio

Also you can generate amazing chart using awesome R libraries, detailed article: View R Plots from within SQL Server Management Studio

Additional Connection Parameters

One such change SSMS got for free is the connection resiliency logic within the SqlConnection.Open() method. To improve the default experience for clients which connect to Azure SQL Database, the above method will (in the case of initial connection errors / timeouts) now retry 1 time after sleeping for 10 seconds. These numbers are configurable by properties called ConnectRetryCount (default value 1) and ConnectRetryInterval (default value 10 seconds.) The previous versions of the SqlConnection class would not automatically retry in cases of connection failure.

There is a simple workaround for this situation. It is to add the following parameter string into the Additional Connection Parameters tab within the SSMS connection window. The good news is that you only need to do this once, as the property is saved for future sessions for that SQL Server (until of course it is removed by you later.)

ConnectRetryCount=0

Working with tabs headers

You can view SPID in tabs header, quickly open script containing folder or copy script file path.

Hiding tables in SSMS Object Explorer

1. You can actually hide an object from object explorer by assigning a specific extended property:

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

You can then remove the property (and the object will show back up) like so:

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

2. DENY VIEW DEFINITION

DENY VIEW DEFINITION ON Schema.Table TO UserName;

Now UserName won’t be able to see Table in Object Explorer. In Fact, they won’t be able to see the table in sys.tables or INFORMATION_SCHEMA.TABLES

VIEW DEFINITION is the ability to see the definition of the object. In the case of SPs the code, same with Views and in the case of Tables it’s the columns definitions etc.

UnDock Tabs and Windows for Multi Monitor Support

From SSMS 2012 and onwards, you can easily dock/undock the query tabs as well as different object windows inside SSMS to make better use of the screen real estate and multiple monitors you have.

RegEx-Based Finding and Replacing of Text in SSMS

So often, one sees developers doing repetitive coding in SSMS or Visual Studio that would be much quicker and easier by using the built-in Regular-Expression-based Find/Replace functionality. It is understandable, since the syntax is odd and some features are missing, but it is still well-worth knowing about.

More details and examples you can find here RegEx-Based Finding and Replacing of Text in SSMS.

My favorite regex: replace \t on \n,. It useful in many cases when you have column names copied from, for example, Excel and need quickly get sql query.

Changing what SSMS opens on startup

You can customize SSMS startup behavior in Tools -> Options -> Environment -> Startup and hide system objects in Object Explore:

Also you can disable the splash screen – this cuts the time it takes SSMS to load for versions before SSMS 17. Right click your shortcut to SSMS and select properties. Enter the text -nosplash right after the ending quote in the path:

It is useful to create a solution of commonly used SQL scripts to always load at start-up.

1. Display the Solution Explorer by pressing Ctrl+Alt+L or clicking View -> Solution Explorer.
2. Then right click the Solution "Solution1" (0 projects) text and select Add -> New Project.
3. Use the default SQL Server Scripts template and give your solution a clever name.
4. Rename all of your SQL Code Snippets so the extension is .SQL. Drag them into the queries folder within the Solution Explorer.
5. Open Windows explorer and browse to the location of your solution. Copy file location address to your clipboard. Go back to your SSMS shortcut properties and add within double quotes the location and file name of your solution before the “-nosplash”.

This is the complete text within my shortcut properties:

"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe" "C:\Users\taranov\Documents\SQL Server Management Studio\Projects\MySQLServerScripts.ssmssln" -nosplash

Modifying New Query Template

You can modified New Query template for any instance SQL Server:

C:\Program Files (x86)\Microsoft SQL Server\[140|130|120|110]\Tools\Binn\ ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql

For example, you can add begin transaction statement for preventing ups queries:

BEGIN TRANSACTION;
 
 
-- COMMIT   TRANSACTION;
-- ROLLBACK TRANSACTION;

Thanks for this tip Aaron Bertrand: T-SQL Tuesday #92: Lessons Learned the Hard Way

Query Execution Options

More detailed article here: Knowing the Options

The options represent the SET values of the current session. SET options can affect how the query is execute thus having a different execution plan. You can find these options in two places within SSMS under Tools -> Options -> Query Execution -> SQL Server -> Advanced:

As well as Tools -> Options -> Query Execution -> SQL Server -> ANSI:

Using the interface to check what is set can get tiresome. Instead, you can use the system function @@OPTIONS. Each option shown above has a BIT value for all 15 options indicating whether or not it is enabled.

@@OPTIONS takes the binary representation and does a BITWISE operation on it to produce an integer value based on the sum of which BITS are enabled.

Default value for SELECT @@OPTIONS is 5496. Let’s assume for a moment that the only two options that are enabled on my machine are ANSI_PADDING and ANSI_WARNINGS. The values for these two options are 8 and 16, respectively speaking. The sum of the two is 24.

/***************************************************************
  Author: John Morehouse
  Summary: This script display what SET options are enabled for the current session.
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit.
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/
SELECT 'Disable_Def_Cnst_Chk'    AS 'Option', CASE @@options & 1     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'IMPLICIT_TRANSACTIONS'   AS 'Option', CASE @@options & 2     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'CURSOR_CLOSE_ON_COMMIT'  AS 'Option', CASE @@options & 4     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_WARNINGS'           AS 'Option', CASE @@options & 8     WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_PADDING'            AS 'Option', CASE @@options & 16    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULLS'              AS 'Option', CASE @@options & 32    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ARITHABORT'              AS 'Option', CASE @@options & 64    WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ARITHIGNORE'             AS 'Option', CASE @@options & 128   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'QUOTED_IDENTIFIER'       AS 'Option', CASE @@options & 256   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'NOCOUNT'                 AS 'Option', CASE @@options & 512   WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULL_DFLT_ON'       AS 'Option', CASE @@options & 1024  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF'      AS 'Option', CASE @@options & 2048  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL' AS 'Option', CASE @@options & 4096  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'NUMERIC_ROUNDABORT'      AS 'Option', CASE @@options & 8192  WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION ALL
SELECT 'XACT_ABORT'              AS 'Option', CASE @@options & 16384 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled';

SQL Server Diagnostics Extension

Analyze Dumps – Customers using this extension will be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix. The memory dumps are stored in a secured and compliant manner as governed by the Microsoft Privacy Policy.

For example, Joe, a DBA from Contoso, Ltd., finds that SQL Server has generated a memory dump while running a workload, and he would like to debug the issue. Using this feature, John can upload the dump and receive recommended KB articles from Microsoft, which can help him fix the issue.