Category: SSMS

Service Broker, SQL, SSMS

Service Broker enabled by default while creating a database through T-SQL in SQL Server
Recently, we decided to disable Service Broker for all databases as part of our deployment. While doing a preliminary check, we observed that when we create a database through T-SQL, by default, the database is enabled with Service Broker.

Please find the below code to replicate the case.

Script:

IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name] = 'testdb')
BEGIN
CREATE DATABASE testdb
Select 'Database created...'
END
GO
select is_broker_enabled,name from sys.databases where name = 'testdb'
GO
Drop database if exists  testdb
Select 'Database dropped...'

screenshot:

We also observed that while creating a database through SSMS interface/wizard, the service broker is disabled by default. Out of curiosity, SQL profiler has been set up, captured the statements and identified that when we create a database through SSMS interface, there is an explicit execution of disabling the Service Broker is happening followed by create database statement as below in the screenshot.

Conclusion:

Always DISABLE service broker explicitly ONLY if you do not really need it to be enabled on your database. Please note, there is no overhead when service broker is enabled but not used. On the flip side, disabling service broker can prevent the applications from using the feature of queuing and usage of components like SqlDependency/QueryNotifications that rely on it. Please never try to turn off service broker on your system databases like msdb as it can cause break in functionalities like database mail etc.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SCRIPT GALLERY, SQL, SSMS

Database Properties – Difference between GUI and sys.master_files in SQL Server
Few days back, One of my colleagues was asking why there is a discrepancy in GUI and system table for Database Properties -> File wizard. Here is an example similar to what he showed me.

GUI Screenshot

SSMS Query Screenshot

My first response was GUI is a user friendly and Query results is a SQL Server Professional friendly. There will be lots of differences like this between GUI and actual table how it saved.But, those are neither discrepancies nor defects, that is how it is by design.

To know this difference, sys.master_files is a system catalog view that represents properties of each file of your database ( data and log). size is always representing in 8 KB pages. Refer the link for more details for other properties.

So, to make him understand, I changed his query a bit to get both looks equal as below. 

Select A.name [Logical Name], A.type_desc [File Type], ISNULL(B.Name, 'Not Applicable') 'Filegroup',
(size*8)/1024 [Size (MB)],
'By ' + Cast((growth*8)/1024 as varchar(max)) + 'MB, ' + 
Case when max_size = -1 then 'Unlimited' Else Cast(Max_size as varchar(max)) End + ' MB' [AutoGrowth/MaxSize],
substring(physical_name,0, Len(Physical_name)-charindex('\',reverse(Physical_name),0)+1) [Path],
Right(physical_name,charindex('\',reverse(Physical_name),0)-1) [File Name]
From sys.master_files A
Left Join sys.filegroups B on A.data_space_id = B.data_space_id
where db_name(database_id) = 'DBATools'

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQL, SSMS

How to enable Side by Side Query window in SQL Server Management Studio


Side by Side Query window is a very useful feature in SSMS. When we are comparing queries or its data, then this feature becomes very handy.

Let us quickly see how do we enable this feature with very simple steps as below:

Select Window Menu in SSMS – > Select “New Horizontal Tab Group” OR “New Vertical Tab Group” as you wish to see the query windows either horizontally or vertically!

You can always revert the change as below:
Select Window Menu in SSMS – > Select “Move to Previous Tab Group”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL, SSMS

Dark theme in Sql Server Management Studio

Many people prefer dark theme for computer displays. It actually enhances the visual ergonomics by reducing eye strain and providing comfort of use at all conditions.Another important benefit, it conserves battery power, there by enabling device to run longer period of time.

Please refer this link for more details.

Now, let us take a look at the fact that dark theme can be applied to SSMS and how do we do and what are implications.

Writing this blog, I am using SSMS 18.2 version, the latest by now. In future, the facts may change if MS decided to make a change.

Do we have Dark theme by default available in SSMS 18.2?

The answer is NO. We do not have an option to change the SSMS into a dark theme by default.However, it provides a way for us to change to “Blue”, “Blue (Extra Contrast)” and “Light” as below.

How do we make SSMS in Dark theme?

There are two ways from my analysis:

1. Custom your SSMS window using Tools -> Options -> Environment -> Fonts and Colors.

The challenge here is to customize each and every items from the list and provide the color combinations. Honestly, this is something very challenging for me to identify the better combinations. There is a blog written by Aaron Betrand on the same, and its really helpful for the basic settings.

2. Change the SSMS settings

There is a file called ssms.pkgundef in the path – “Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE“. This file is where you will place registry entries that are to be removed during setup of your application. If you look at the content of this files, you can see the below code:


// Remove Dark theme
[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

The above code, actually removes the dark options from SSMS while setting up of SSMS. To enable dark theme for SSMS, you can simply comment the code as below and it will give the option of “Dark” in Tool -> Option -> General -> Color Theme as below.

Please be aware, this is a hack or undocumented way of changing SSMS config files. It may or may not work and its not supported by Microsoft. This may be made unavailable or documented because this may not be interoperable with other Windows display settings. So please be use this as an option at your own risk.

SQL, SSMS

How do we recover unsaved query window in SQL Server Management Studio?

How do we recover the unsaved query window in SQL Server Management Studio?

This is a simple tip to recover your unsaved query window in SSMS.

As a SQL server professional, most of us will work closely with SSMS in a daily basis.At times, we forget to save our work in the management studio and for some reason if the query window closes/SSMS crashes, we will be in a position of “lost everything”. You may be questioned on your best practises. Here is a way to recover the SSMS window content which would save your time and effort.

You can verify the below path and get the files:

C:\Users\<>\Documents<>\SQL Server Management Studio\Backup Files

The above path may change depends on the windows version.

How does it happen?

There is a setting in Management Studio that allows the Autorecover option enabled by default. We can modify the default settings as per our need to reduce the risk of loss.

Go to SSMS -> Tools -> Options -> Environment -> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query window file at certain interval specified. You can set this value carefully on how frequent that you need to save your SSMS cahnges.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days.